說明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a 新表名：b) [br]select * into b from a where 1<>1[br][br][br]說明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b)[br]insert into b(a, b, c) select d,e,f from b;[br][br][br]說明：顯示文章、提交人和最后回復(fù)時(shí)間[br]select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b[br][br][br]說明：外連接查詢(表名1：a 表名2：b)[br]select a.a, a.b, a.c, b.c, b.d, b.f from a left out join b on a.a = b.c[br][br][br]說明：日程安排提前五分鐘提醒[br]select * from 日程安排 where datediff('minute',f開始時(shí)間,getdate())>5[br][br][br]說明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息[br]delete from info where not exists ( select * from infobz where info.infid=infobz.infid )[br][br][br]說明：-- [br][br]sql: [br][br]select a.num, a.name, b.upd_date, b.prev_upd_date [br][br]from table1, [br][br](select x.num, x.upd_date, y.upd_date prev_upd_date [br][br]from (select num, upd_date, inbound_qty, stock_onhand [br][br]from table2 [br][br]where to_char(upd_date,'yyyy/mm') = to_char(sysdate, 'yyyy/mm')) x, [br][br](select num, upd_date, stock_onhand [br][br]from table2 [br][br]where to_char(upd_date,'yyyy/mm') = [br][br]to_char(to_date(to_char(sysdate, 'yyyy/mm') || '/01','yyyy/mm/dd') - 1, 'yyyy/mm') ) y, [br][br]where x.num = y.num （+） [br][br]and x.inbound_qty + nvl(y.stock_onhand,0) <> x.stock_onhand ) b [br][br]where a.num = b.num[br][br][br]說明：-- [br]select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名稱='"&strdepartmentname&"' and 專業(yè)名稱='"&strprofessionname&"' order by 性別,生源地,高考總成績[br][br][br]從數(shù)據(jù)庫中去一年的各單位電話費(fèi)統(tǒng)計(jì)(電話費(fèi)定額賀電化肥清單兩個(gè)表來源） [br][br]select a.userper, a.tel, a.standfee, to_char(a.telfeedate, 'yyyy') as telyear, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '01', a.factration)) as jan, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '02', a.factration)) as fri, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '03', a.factration)) as mar, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '04', a.factration)) as apr, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '05', a.factration)) as may, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '06', a.factration)) as jue, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '07', a.factration)) as jul, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '08', a.factration)) as agu, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '09', a.factration)) as sep, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '10', a.factration)) as oct, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '11', a.factration)) as nov, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '12', a.factration)) as dec [br][br]from (select a.userper, a.tel, a.standfee, b.telfeedate, b.factration [br][br]from telfeestand a, telfee b [br][br]where a.tel = b.telfax) a [br][br]group by a.userper, a.tel, a.standfee, to_char(a.telfeedate, 'yyyy')[br][br][br]說明：四表聯(lián)查問題[br]select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... [br][br][br]說明：得到表中最小的未使用的id號(hào)[br][br][br]select (case when exists(select * from handle b where b.handleid = 1) then min(handleid) + 1 else 1 end) as handleid from handle where not handleid in (select a.handleid - 1 from handle a)[br][br][br]一個(gè)sql語句的問題:行列轉(zhuǎn)換[br]select * from v_temp[br]上面的視圖結(jié)果如下:[br]user_name role_name[br]-------------------------[br]系統(tǒng)管理員 管理員 [br]feng 管理員 [br]feng 一般用戶 [br]test 一般用戶 [br]想把結(jié)果變成這樣:[br]user_name role_name[br]---------------------------[br]系統(tǒng)管理員 管理員 [br]feng 管理員,一般用戶 [br]test 一般用戶[br]===================[br]create table a_test(name varchar(20),role2 varchar(20))[br]insert into a_test values('李','管理員')[br]insert into a_test values('張','管理員')[br]insert into a_test values('張','一般用戶')[br]insert into a_test values('常','一般用戶')[br][br]create function join_str(@content varchar(100))[br]returns varchar(2000)[br]as[br]begin[br]declare @str varchar(2000)[br]set @str=''[br]select @str=@str+','+rtrim(role2) from a_test where [name]=@content[br]select @str=right(@str,len(@str)-1)[br]return @str[br]end[br]go[br][br]--調(diào)用：[br]select [name],dbo.join_str([name]) role2 from a_test group by [name][br][br]--select distinct name,dbo.uf_test(name) from a_test[br][br][br]快速比較結(jié)構(gòu)相同的兩表[br]結(jié)構(gòu)相同的兩表，一表有記錄3萬條左右，一表有記錄2萬條左右，我怎樣快速查找兩表的不同記錄？[br]============================[br]給你一個(gè)測試方法，從northwind中的orders表取數(shù)據(jù)。[br]select * into n1 from orders[br]select * into n2 from orders[br][br]select * from n1[br]select * from n2[br][br]--添加主鍵，然后修改n1中若干字段的若干條[br]alter table n1 add constraint pk_n1_id primary key (orderid)[br]alter table n2 add constraint pk_n2_id primary key (orderid)[br][br]select orderid from (select * from n1 union select * from n2) a group by orderid having count(*) > 1[br][br]應(yīng)該可以，而且將不同的記錄的id顯示出來。[br]下面的適用于雙方記錄一樣的情況，[br][br]select * from n1 where orderid in (select orderid from (select * from n1 union select * from n2) a group by orderid having count(*) > 1) [br]至于雙方互不存在的記錄是比較好處理的[br]--刪除n1,n2中若干條記錄[br]delete from n1 where orderid in ('10728','10730')[br]delete from n2 where orderid in ('11000','11001')[br][br]--*************************************************************[br]-- 雙方都有該記錄卻不完全相同[br]select * from n1 where orderid in(select orderid from (select * from n1 union select * from n2) a group by orderid having count(*) > 1)[br]union[br]--n2中存在但在n1中不存的在10728,10730[br]select * from n1 where orderid not in (select orderid from n2)[br]union[br]--n1中存在但在n2中不存的在11000,11001[br]select * from n2 where orderid not in (select orderid from n1)[br][br][br]四種方法取表里n到m條紀(jì)錄：[br][br]1.[br]select top m * into 臨時(shí)表(或表變量) from tablename order by columnname -- 將top m筆插入[br]set rowcount n[br]select * from 表變量 order by columnname desc[br][br][br]2.[br]select top n * from (select top m * from tablename order by columnname) a order by columnname desc[br][br][br]3.如果tablename里沒有其他identity列，那么：[br]select identity(int) id0,* into #temp from tablename[br][br]取n到m條的語句為：[br]select * from #temp where id0 >=n and id0 <= m[br][br]如果你在執(zhí)行select identity(int) id0,* into #temp from tablename這條語句的時(shí)候報(bào)錯(cuò),那是因?yàn)槟愕膁b中間的select into/bulkcopy屬性沒有打開要先執(zhí)行：[br]exec sp_dboption 你的db名字,'select into/bulkcopy',true[br][br][br]4.如果表里有identity屬性，那么簡單：[br]select * from tablename where identitycol between n and m [br][br][br]如何刪除一個(gè)表中重復(fù)的記錄？[br]create table a_dist(id int,name varchar(20))[br][br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br][br]exec up_distinct 'a_dist','id'[br][br]select * from a_dist[br][br]create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))[br]--f_key表示是分組字段﹐即主鍵字段[br]as[br]begin[br]declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer[br]select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'[br]exec(@sql)[br]open cur_rows [br]fetch cur_rows into @id,@max [br]while @@fetch_status=0 [br]begin [br]select @max = @max -1 [br]set rowcount @max [br]select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key[br]if @type=56[br]select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id [br]if @type=167[br]select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' [br]exec(@sql)[br]fetch cur_rows into @id,@max [br]end [br]close cur_rows [br]deallocate cur_rows[br]set rowcount 0[br]end[br][br]select * from systypes[br]select * from syscolumns where id = object_id('a_dist')[br][br][br]查詢數(shù)據(jù)的最大排序問題（只能用一條語句寫） [br]create table hard (qu char (11) ,co char (11) ,je numeric(3, 0)) [br][br]insert into hard values ('a','1',3)[br]insert into hard values ('a','2',4)[br]insert into hard values ('a','4',2)[br]insert into hard values ('a','6',9)[br]insert into hard values ('b','1',4)[br]insert into hard values ('b','2',5)[br]insert into hard values ('b','3',6)[br]insert into hard values ('c','3',4)[br]insert into hard values ('c','6',7)[br]insert into hard values ('c','2',3)[br][br][br]要求查詢出來的結(jié)果如下：[br][br]qu co je [br]----------- ----------- ----- [br]a 6 9[br]a 2 4[br]b 3 6[br]b 2 5[br]c 6 7[br]c 3 4[br][br][br]就是要按qu分組，每組中取je最大的前2位?。br]而且只能用一句sql語句?。。br]select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je) [br][br][br]求刪除重復(fù)記錄的sql語句？ [br]怎樣把具有相同字段的紀(jì)錄刪除，只留下一條。[br]例如，表test里有id,name字段[br]如果有name相同的記錄 只留下一條，其余的刪除。[br]name的內(nèi)容不定，相同的記錄數(shù)不定。[br]有沒有這樣的sql語句？[br]==============================[br]a:一個(gè)完整的解決方案：[br][br]將重復(fù)的記錄記入temp1表:[br]select [標(biāo)志字段id],count(*) into temp1 from [表名][br]group by [標(biāo)志字段id][br]having count(*)>1[br][br]2、將不重復(fù)的記錄記入temp1表:[br]insert temp1 select [標(biāo)志字段id],count(*) from [表名] group by [標(biāo)志字段id] having count(*)=1[br][br]3、作一個(gè)包含所有不重復(fù)記錄的表：[br]select * into temp2 from [表名] where 標(biāo)志字段id in(select 標(biāo)志字段id from temp1)[br][br]4、刪除重復(fù)表:[br]delete [表名][br][br]5、恢復(fù)表：[br]insert [表名] select * from temp2[br][br]6、刪除臨時(shí)表:[br]drop table temp1[br]drop table temp2[br]================================[br]b:[br]create table a_dist(id int,name varchar(20))[br][br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br][br]exec up_distinct 'a_dist','id'[br][br]select * from a_dist[br][br]create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))[br]--f_key表示是分組字段﹐即主鍵字段[br]as[br]begin[br]declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer[br]select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'[br]exec(@sql)[br]open cur_rows [br]fetch cur_rows into @id,@max [br]while @@fetch_status=0 [br]begin [br]select @max = @max -1 [br]set rowcount @max [br]select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key[br]if @type=56[br]select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id [br]if @type=167[br]select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' [br]exec(@sql)[br]fetch cur_rows into @id,@max [br]end [br]close cur_rows [br]deallocate cur_rows[br]set rowcount 0[br]end[br][br]select * from systypes[br]select * from syscolumns where id = object_id('a_dist')[br][br][br]行列轉(zhuǎn)換--普通 [br][br]假設(shè)有張學(xué)生成績表(cj)如下 [br]name subject result [br]張三 語文 80 [br]張三 數(shù)學(xué) 90 [br]張三 物理 85 [br]李四 語文 85 [br]李四 數(shù)學(xué) 92 [br]李四 物理 82 [br][br]想變成 [br]姓名 語文 數(shù)學(xué) 物理 [br]張三 80 90 85 [br]李四 85 92 82 [br][br]declare @sql varchar(4000) [br]set @sql = 'select name' [br]select @sql = @sql + ',sum(case subject when '''+subject+''' then result end) ['+subject+']' [br]from (select distinct subject from cj) as a [br]select @sql = @sql+' from test group by name' [br]exec(@sql) [br][br]行列轉(zhuǎn)換--合并 [br][br]有表a, [br]id pid [br]1 1 [br]1 2 [br]1 3 [br]2 1 [br]2 2 [br]3 1 [br]如何化成表b: [br]id pid [br]1 1,2,3 [br]2 1,2 [br]3 1 [br][br]創(chuàng)建一個(gè)合并的函數(shù) [br]create function fmerg(@id int) [br]returns varchar(8000) [br]as [br]begin [br]declare @str varchar(8000) [br]set @str='' [br]select @str=@str+','+cast(pid as varchar) from 表a where id=@id [br]set @str=right(@str,len(@str)-1) [br]return(@str) [br]end [br]go [br][br]--調(diào)用自定義函數(shù)得到結(jié)果 [br]select distinct id,dbo.fmerg(id) from 表a [br][br][br]如何取得一個(gè)數(shù)據(jù)表的所有列名 [br][br]方法如下：先從systemobject系統(tǒng)表中取得數(shù)據(jù)表的systemid,然后再syscolumn表中取得該數(shù)據(jù)表的所有列名。 [br]sql語句如下： [br]declare @objid int,@objname char(40) [br]set @objname = 'tablename' [br]select @objid = id from sysobjects where id = object_id(@objname) [br]select 'column_name' = name from syscolumns where id = @objid order by colid [br][br]或[br][br]select * from information_schema.columns where table_name ='users'[br][br][br]通過sql語句來更改用戶的密碼 [br][br]修改別人的,需要sysadmin role [br]exec sp_password null, 'newpassword', 'user' [br][br]如果帳號(hào)為sa執(zhí)行exec sp_password null, 'newpassword', sa[br][br][br]怎么判斷出一個(gè)表的哪些字段不允許為空？ [br][br]select column_name from information_schema.columns where is_nullable='no' and table_name=tablename [br][br][br]如何在數(shù)據(jù)庫里找到含有相同字段的表？ [br]a. 查已知列名的情況 [br]select b.name as tablename,a.name as columnname [br]from syscolumns a inner join sysobjects b [br]on a.id=b.id [br]and b.type='u' [br]and a.name='你的字段名字' [br][br][br]未知列名查所有在不同表出現(xiàn)過的列名 [br]select o.name as tablename,s1.name as columnname [br]from syscolumns s1, sysobjects o [br]where s1.id = o.id [br]and o.type = 'u' [br]and exists ( [br]select 1 from syscolumns s2 [br]where s1.name = s2.name [br]and s1.id <> s2.id [br]) [br][br][br]查詢第xxx行數(shù)據(jù) [br][br]假設(shè)id是主鍵： [br]select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id) [br][br]如果使用游標(biāo)也是可以的 [br]fetch absolute [number] from [cursor_name] [br]行數(shù)為絕對(duì)行數(shù) [br][br][br]sql server日期計(jì)算 [br]a. 一個(gè)月的第一天 [br]select dateadd(mm, datediff(mm,0,getdate()), 0) [br]b. 本周的星期一 [br]select dateadd(wk, datediff(wk,0,getdate()), 0) [br]c. 一年的第一天 [br]select dateadd(yy, datediff(yy,0,getdate()), 0) [br]d. 季度的第一天 [br]select dateadd(qq, datediff(qq,0,getdate()), 0) [br]e. 上個(gè)月的最后一天 [br]select dateadd(ms,-3,dateadd(mm, datediff(mm,0,getdate()), 0)) [br]f. 去年的最后一天 [br]select dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate()), 0)) [br]g. 本月的最后一天 [br]select dateadd(ms,-3,dateadd(mm, datediff(m,0,getdate())+1, 0)) [br]h. 本月的第一個(gè)星期一 [br]select dateadd(wk, datediff(wk,0, [br]dateadd(dd,6-datepart(day,getdate()),getdate()) [br]), 0) [br]i. 本年的最后一天 [br]select dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate())+1, 0))。 [br][br][br]獲取表結(jié)構(gòu)[把 'sysobjects' 替換 成 'tablename' 即可] [br][br]select case isnull(i.name, '') [br]when '' then '' [br]else '*' [br]end as ispk, [br]object_name(a.id) as t_name, [br]a.name as c_name, [br]isnull(substring(m.text, 1, 254), '') as pbc_init, [br]t.name as f_datatype, [br]case isnull(typeproperty(t.name, 'scale'), '') [br]when '' then cast(a.prec as varchar) [br]else cast(a.prec as varchar) + ',' + cast(a.scale as varchar) [br]end as f_scale, [br]a.isnullable as f_isnullable [br]from syscolumns as a [br]join systypes as t [br]on (a.xtype = t.xusertype and a.id = object_id('sysobjects') ) [br]left join ( sysindexes as i [br]join syscolumns as a1 [br]on ( i.id = a1.id and a1.id = object_id('sysobjects') and (i.status & 0x800) = 0x800 and a1.colid <= i.keycnt) ) [br]on ( a.id = i.id and a.name = index_col('sysobjects', i.indid, a1.colid) ) [br]left join syscomments as m [br]on ( m.id = a.cdefault and objectproperty(a.cdefault, 'isconstraint') = 1 ) [br]order by a.colid asc[br][br][br]提取數(shù)據(jù)庫內(nèi)所有表的字段詳細(xì)說明的sql語句 [br][br]select [br](case when a.colorder=1 then d.name else '' end) n'表名', [br]a.colorder n'字段序號(hào)', [br]a.name n'字段名', [br](case when columnproperty( a.id,a.name,'isidentity')=1 then '√'else '' [br]end) n'標(biāo)識(shí)', [br](case when (select count(*) [br]from sysobjects [br]where (name in [br](select name [br]from sysindexes [br]where (id = a.id) and (indid in [br](select indid [br]from sysindexkeys [br]where (id = a.id) and (colid in [br](select colid [br]from syscolumns [br]where (id = a.id) and (name = a.name))))))) and [br](xtype = 'pk'))>0 then '√' else '' end) n'主鍵', [br]b.name n'類型', [br]a.length n'占用字節(jié)數(shù)', [br]columnproperty(a.id,a.name,'precision') as n'長度', [br]isnull(columnproperty(a.id,a.name,'scale'),0) as n'小數(shù)位數(shù)'