精妙的SQL语句

• 1.说明：复制表(只复制结构,源表名：a 新表名：b)    
• SQL: select * into b from a where 11    
•   
• 2.说明：拷贝表(拷贝数据,源表名：a 目标表名：b)    
•   
• SQL: insert into b(a, b, c) select d,e,f from a;    
•   
• 3.说明：显示文章、提交人和最后回复时间    
•   
• SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b    
•   
• 4.说明：外连接查询(表名1：a 表名2：b)    
•   
• SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUTER JOIN b ON a.a = b.c    
•   
• 5.说明：日程安排提前五分钟提醒    
•   
• SQL: select * from 日程安排 where datediff(’minute’,f开始时间,getdate())>5    
•   
• 6.说明：两张关联表，删除主表中已经在副表中没有的信息    
•   
• SQL:    
•   
• delete from info where not exists ( select * from infobz where info.infid=infobz.infid )    
•   
• 说明：–    
•   
• SQL:    
•   
• SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE    
•   
• FROM TABLE1,    
•   
• (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE    
•   
• FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND    
•   
• FROM TABLE2    
•   
• WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) = TO_CHAR(SYSDATE, ‘YYYY/MM’)) X,    
•   
• (SELECT NUM, UPD_DATE, STOCK_ONHAND    
•   
• FROM TABLE2    
•   
• WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) =    
•   
• TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, ‘YYYY/MM’) || ‘/01′,’YYYY/MM/DD’) – 1, ‘YYYY/MM’) ) Y,    
•   
• WHERE X.NUM = Y.NUM （+）    
•   
• AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) X.STOCK_ONHAND ) B    
•   
• WHERE A.NUM = B.NUM    
•   
• 说明：–    
•   
• SQL:    
•   
• select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称=’”&strdepartmentname&”‘ and 专业名称=’”&strprofessionname&”‘ order by 性别,生源地,高考总成绩    
•   
• 7.说明：    
•   
• 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）    
•   
• SQL:    
•   
• SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’) AS telyear,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘01′, a.factration)) AS JAN,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘02′, a.factration)) AS FRI,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘03′, a.factration)) AS MAR,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘04′, a.factration)) AS APR,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘05′, a.factration)) AS MAY,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘06′, a.factration)) AS JUE,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘07′, a.factration)) AS JUL,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘08′, a.factration)) AS AGU,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘09′, a.factration)) AS SEP,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘10′, a.factration)) AS OCT,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘11′, a.factration)) AS NOV,    
•   
• SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘12′, a.factration)) AS DEC    
•   
• FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration    
•   
• FROM TELFEESTAND a, TELFEE b    
•   
• WHERE a.tel = b.telfax) a    
•   
• GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’)    
•   
• 8.说明：四表联查问题：    
•   
• SQL: 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 …..    
•   
• 9.说明：得到表中最小的未使用的ID号    
•   
• SQL:    
•   
• 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)    
•   
• 9.SQL语句技巧    
• 9.1、一个SQL语句的问题:行列转换    
• select * from v_temp    
• 上面的视图结果如下:    
• user_name role_name    
• ————————-    
• 系统管理员 管理员    
• feng 管理员    
• feng 一般用户    
• test 一般用户    
• 想把结果变成这样:    
• user_name role_name    
• —————————    
• 系统管理员 管理员    
• feng 管理员,一般用户    
• test 一般用户    
• ===================    
• create table a_test(name varchar(20),role2 varchar(20))    
• insert into a_test values(’李’,'管理員’)    
• insert into a_test values(’張’,'管理員’)    
• insert into a_test values(’張’,'一般用戶’)    
• insert into a_test values(’常’,'一般用戶’)    
•   
• create function join_str(@content varchar(100))    
• returns varchar(2000)    
• as    
• begin    
• declare @str varchar(2000)    
• set @str=”    
• select @str=@str+’,'+rtrim(role2) from a_test where [name]=@content    
• select @str=right(@str,len(@str)-1)    
• return @str    
• end    
• go    
•   
• –调用：    
• select [name],dbo.join_str([name]) role2 from a_test group by [name]    
•   
• –select distinct name,dbo.uf_test(name) from a_test    
•   
• 9.2、求助！快速比较结构相同的两表    
• 结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？    
• ============================    
• 给你一个测试方法，从northwind中的orders表取数据。    
• select * into n1 from orders    
• select * into n2 from orders    
•   
• select * from n1    
• select * from n2    
•   
• –添加主键，然后修改n1中若干字段的若干条    
• alter table n1 add constraint pk_n1_id primary key (OrderID)    
• alter table n2 add constraint pk_n2_id primary key (OrderID)    
•   
• select OrderID from (select * from n1    
• union    
• select * from n2) a group by OrderID having count(*) > 1    
•   
• 应该可以，而且将不同的记录的ID显示出来。    
• 下面的适用于双方记录一样的情况，    
•   
• select * from n1 where orderid in    
• (    
• select OrderID from (select * from n1    
• union    
• select * from n2) a group by OrderID having count(*) > 1    
• )    
• 至于双方互不存在的记录是比较好处理的    
• –删除n1,n2中若干条记录    
• delete from n1 where orderID in (’10728′,’10730′)    
• delete from n2 where orderID in (’11000′,’11001′)    
•   
• –*************************************************************    
• – 双方都有该记录却不完全相同    
• select * from n1 where orderid in    
• (    
• select OrderID from (select * from n1    
• union    
• select * from n2) a group by OrderID having count(*) > 1    
• )    
• union    
• –n2中存在但在n1中不存的在10728,10730    
• select * from n1 where OrderID not in (select OrderID from n2)    
• union    
• –n1中存在但在n2中不存的在11000,11001    
• select * from n2 where OrderID not in (select OrderID from n1)    
•   
• 9.3、四种方法取表里n到m条纪录：    
•   
• 1.    
• select top m * into 临时表(或表变量) from tablename order by columnname — 将top m笔插入    
• set rowcount n    
• select * from 表变量 order by columnname desc    
•   
• 2.    
• select top n * from    
• (select top m * from tablename order by columnname) a    
• order by columnname desc    
•   
• 3.如果tablename里没有其他identity列，那么：    
• select identity(int) id0,* into #temp from tablename    
•   
• 取n到m条的语句为：    
• select * from #temp where id0 >=n and id0 1′    
• exec(@sql)    
• open cur_rows    
• fetch cur_rows into @id,@max    
• while @@fetch_status=0    
• begin    
• select @max = @max -1    
• set rowcount @max    
• select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key    
• if @type=56    
• select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+ @id    
• if @type=167    
• select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+””+ @id +””    
• exec(@sql)    
• fetch cur_rows into @id,@max    
• end    
• close cur_rows    
• deallocate cur_rows    
• set rowcount 0    
• end    
•   
• select * from systypes    
• select * from syscolumns where id = object_id(’a_dist’)    
•   
• 9.4.查询数据的最大排序问题（只能用一条语句写）    
• CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))    
•   
• insert into hard values (’A',’1′,3)    
• insert into hard values (’A',’2′,4)    
• insert into hard values (’A',’4′,2)    
• insert into hard values (’A',’6′,9)    
• insert into hard values (’B',’1′,4)    
• insert into hard values (’B',’2′,5)    
• insert into hard values (’B',’3′,6)    
• insert into hard values (’C',’3′,4)    
• insert into hard values (’C',’6′,7)    
• insert into hard values (’C',’2′,3)    
•   
• 要求查询出来的结果如下：    
•   
• qu co je    
• ———– ———– —–    
• A 6 9    
• A 2 4    
• B 3 6    
• B 2 5    
• C 6 7    
• C 3 4    
•   
• 就是要按qu分组，每组中取je最大的前2位！！    
• 而且只能用一句sql语句！！！    
• select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)    
• 9.5.求删除重复记录的sql语句？    
• 怎样把具有相同字段的纪录删除，只留下一条。    
• 例如，表test里有id,name字段    
• 如果有name相同的记录 只留下一条，其余的删除。    
• name的内容不定，相同的记录数不定。    
• 有没有这样的sql语句？    
• ==============================    
• A:一个完整的解决方案：    
•   
• 将重复的记录记入temp1表:    
• select [标志字段id],count(*) into temp1 from [表名]    
• group by [标志字段id]    
• having count(*)>1    
•   
• 2、将不重复的记录记入temp1表:    
• insert temp1    
• select [标志字段id],count(*) from [表名]    
• group by [标志字段id]    
• having count(*)=1    
•   
• 3、作一个包含所有不重复记录的表：    
• select * into temp2 from [表名]    
• where 标志字段id in(select 标志字段id from temp1)    
•   
• 4、删除重复表:    
• delete [表名]    
•   
• 5、恢复表：    
• insert [表名]    
• select * from temp2    
•   
• 6、删除临时表:    
• drop table temp1    
• drop table temp2    
• ================================    
• B:    
• create table a_dist(id int,name varchar(20))    
•   
• insert into a_dist values(1,’abc’)    
• insert into a_dist values(1,’abc’)    
• insert into a_dist values(1,’abc’)    
• insert into a_dist values(1,’abc’)    
•   
• exec up_distinct ‘a_dist’,'id’    
•   
• select * from a_dist    
•   
• create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))    
• –f_key表示是分組字段﹐即主鍵字段    
• as    
• begin    
• declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer    
• select @sql = ‘declare cur_rows cursor for select ‘+@f_key+’ ,count(*) from ‘ +@t_name +’ group by ‘ +@f_key +’ having count(*) > 1′    
• exec(@sql)    
• open cur_rows    
• fetch cur_rows into @id,@max    
• while @@fetch_status=0    
• begin    
• select @max = @max -1    
• set rowcount @max    
• select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key    
• if @type=56    
• select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+ @id    
• if @type=167    
• select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+””+ @id +””    
• exec(@sql)    
• fetch cur_rows into @id,@max    
• end    
• close cur_rows    
• deallocate cur_rows    
• set rowcount 0    
• end    
•   
• select * from systypes    
• select * from syscolumns where id = object_id(’a_dist’)    
•   
• 10.1. 行列转换–普通    
•   
• 假设有张学生成绩表(CJ)如下    
• Name Subject Result    
• 张三 语文 80    
• 张三 数学 90    
• 张三 物理 85    
• 李四 语文 85    
• 李四 数学 92    
• 李四 物理 82    
•   
• 想变成    
• 姓名 语文 数学 物理    
• 张三 80 90 85    
• 李四 85 92 82    
•   
• declare @sql varchar(4000)    
• set @sql = ’select Name’    
• select @sql = @sql + ‘,sum(case Subject when ”’+Subject+”’ then Result end) ['+Subject+']‘    
• from (select distinct Subject from CJ) as a    
• select @sql = @sql+’ from test group by name’    
• exec(@sql)    
•   
• 10.2. 行列转换–合并    
•   
• 有表A,    
• id pid    
• 1 1    
• 1 2    
• 1 3    
• 2 1    
• 2 2    
• 3 1    
• 如何化成表B:    
• id pid    
• 1 1,2,3    
• 2 1,2    
• 3 1    
•   
• 创建一个合并的函数    
• create function fmerg(@id int)    
• returns varchar(8000)    
• as    
• begin    
• declare @str varchar(8000)    
• set @str=”    
• select @str=@str+’,'+cast(pid as varchar) from 表A where id=@id    
• set @str=right(@str,len(@str)-1)    
• return(@str)    
• End    
• go    
•   
• –调用自定义函数得到结果    
• select distinct id,dbo.fmerg(id) from 表A    
•   
• 10.3. 如何取得一个数据表的所有列名    
•   
• 方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。    
• SQL语句如下：    
• declare @objid int,@objname char(40)    
• set @objname = ‘tablename’    
• select @objid = id from sysobjects where id = object_id(@objname)    
• select ‘Column_name’ = name from syscolumns where id = @objid order by colid    
•   
• 是不是太简单了？ 呵呵 不过经常用阿.    
•   
• 10.4. 通过SQL语句来更改用户的密码    
•   
• 修改别人的,需要sysadmin role    
• EXEC sp_password NULL, ‘newpassword’, ‘User’    
•   
• 如果帐号为SA执行EXEC sp_password NULL, ‘newpassword’, sa    
•   
• 10.5. 怎么判断出一个表的哪些字段不允许为空？    
•   
• select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=’NO’ and TABLE_NAME=tablename    
•   
• 10.6. 如何在数据库里找到含有相同字段的表？    
• a. 查已知列名的情况    
• SELECT b.name as TableName,a.name as columnname    
• From syscolumns a INNER JOIN sysobjects b    
• ON a.id=b.id    
• AND b.type=’U’    
• AND a.name=’你的字段名字’    
•   
• b. 未知列名查所有在不同表出现过的列名    
• Select o.name As tablename,s1.name As columnname    
• From syscolumns s1, sysobjects o    
• Where s1.id = o.id    
• And o.type = ‘U’    
• And Exists (    
• Select 1 From syscolumns s2    
• Where s1.name = s2.name    
• And s1.id s2.id    
• )    
•   
• 10.7. 查询第xxx行数据    
•   
• 假设id是主键：    
• 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)    
•   
• 如果使用游标也是可以的    
• fetch absolute [number] from [cursor_name]    
• 行数为绝对行数    
•   
• 10.8. SQL Server日期计算    
• a. 一个月的第一天    
• SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)    
• b. 本周的星期一    
• SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)    
• c. 一年的第一天    
• SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)    
• d. 季度的第一天    
• SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)    
• e. 上个月的最后一天    
• SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))    
• f. 去年的最后一天    
• SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))    
• g. 本月的最后一天    
• SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))    
• h. 本月的第一个星期一    
• select DATEADD(wk, DATEDIFF(wk,0,    
• dateadd(dd,6-datepart(day,getdate()),getdate())    
• ), 0)    
• i. 本年的最后一天    
• SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。    
•   
• 11.1.获取表结构[把 'sysobjects' 替换 成 'tablename' 即可]    
•   
• SELECT CASE IsNull(I.name, ”)    
• When ” Then ”    
• Else ‘*’    
• End as IsPK,    
• Object_Name(A.id) as t_name,    
• A.name as c_name,    
• IsNull(SubString(M.text, 1, 254), ”) as pbc_init,    
• T.name as F_DataType,    
• CASE IsNull(TYPEPROPERTY(T.name, ‘Scale’), ”)    
• WHEN ” Then Cast(A.prec as varchar)    
• ELSE Cast(A.prec as varchar) + ‘,’ + Cast(A.scale as varchar)    
• END as F_Scale,    
• A.isnullable as F_isNullAble    
• FROM Syscolumns as A    
• JOIN Systypes as T    
• ON (A.xType = T.xUserType AND A.Id = Object_id(’sysobjects’) )    
• LEFT JOIN ( SysIndexes as I    
• JOIN Syscolumns as A1    
• ON ( I.id = A1.id and A1.id = object_id(’sysobjects’) and (I.status & 0×800) = 0×800 AND A1.colid 0 then ‘√’ else ” end) N’主键’,    
• b.name N’类型’,    
• a.length N’占用字节数’,    
• COLUMNPROPERTY(a.id,a.name,’PRECISION’) as N’长度’,    
• isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0) as N’小数位数’,    
• (case when a.isnullable=1 then ‘√’else ” end) N’允许空’,    
• isnull(e.text,”) N’默认值’,    
• isnull(g.[value],”) AS N’字段说明’    
• FROM syscolumns a    
• left join systypes b    
• on a.xtype=b.xusertype    
• inner join sysobjects d    
• on a.id=d.id and d.xtype=’U’ and d.name’dtproperties’    
• left join syscomments e    
• on a.cdefault=e.id    
• left join sysproperties g    
• on a.id=g.id AND a.colid = g.smallid    
• order by object_name(a.id),a.colorder    
•   
• 11.3.快速获取表test的记录总数[对大容量表非常有效]    
•   
• 快速获取表test的记录总数:    
• select rows from sysindexes where id = object_id(‘test’) and indid in (0,1)    
•   
• update 2 set KHXH=(ID+1)\2 2行递增编号    
• update [23] set id1 = ‘No.’+right(’00000000′+id,6) where id not like ‘No%’ //递增    
• update [23] set id1= ‘No.’+right(’00000000′+replace(id1,’No.’,”),6) //补位递增    
• delete from [1] where (id%2)=1    
• 奇数    
•   
• 替换表名字段    
• update [1] set domurl = replace(domurl,’/upload/Imgswf/’,'/upload/Photo/’) where domurl like ‘%/upload/Imgswf/%’    
•   
• 截位    
• SELECT LEFT(表名, 5)