精妙的sql语句.htm

介绍SQL的书

<P>从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） </P>
<P>SQL: </P>
<P>SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’) AS 
telyear, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘01′, a.factration)) AS JAN, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘02′, a.factration)) AS FRI, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘03′, a.factration)) AS MAR, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘04′, a.factration)) AS APR, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘05′, a.factration)) AS MAY, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘06′, a.factration)) AS JUE, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘07′, a.factration)) AS JUL, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘08′, a.factration)) AS AGU, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘09′, a.factration)) AS SEP, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘10′, a.factration)) AS OCT, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘11′, a.factration)) AS NOV, </P>
<P>SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘12′, a.factration)) AS DEC </P>
<P>FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration </P>
<P>FROM TELFEESTAND a, TELFEE b </P>
<P>WHERE a.tel = b.telfax) a </P>
<P>GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’) </P>
<P>8.说明：四表联查问题： </P>
<P>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 ….. </P>
<P>9.说明：得到表中最小的未使用的ID号 </P>
<P>SQL: </P>
<P>SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN 
MIN(HandleID) + 1 ELSE 1 END) as HandleID </P>
<P>FROM Handle </P>
<P>WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)</P>
<P>9.SQL语句技巧<BR>9.1、一个SQL语句的问题:行列转换<BR>select * from 
v_temp<BR>上面的视图结果如下:<BR>user_name role_name<BR>————————-<BR>系统管理员 管理员<BR>feng 
管理员<BR>feng 一般用户<BR>test 一般用户<BR>想把结果变成这样:<BR>user_name 
role_name<BR>—————————<BR>系统管理员 管理员<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(’常’,'一般用戶’)</P>
<P>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</P>
<P>–调用：<BR>select [name],dbo.join_str([name]) role2 from a_test group by 
[name]</P>
<P>–select distinct name,dbo.uf_test(name) from a_test</P>
<P>9.2、求助！快速比较结构相同的两表<BR>结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？<BR>============================<BR>给你一个测试方法，从northwind中的orders表取数据。<BR>select 
* into n1 from orders<BR>select * into n2 from orders</P>
<P>select * from n1<BR>select * from n2</P>
<P>–添加主键，然后修改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)</P>
<P>select OrderID from (select * from n1<BR>union<BR>select * from n2) a group 
by OrderID having count(*) &gt; 1</P>
<P>应该可以，而且将不同的记录的ID显示出来。<BR>下面的适用于双方记录一样的情况，</P>
<P>select * from n1 where orderid in<BR>(<BR>select OrderID from (select * from 
n1<BR>union<BR>select * from n2) a group by OrderID having count(*) &gt; 
1<BR>)<BR>至于双方互不存在的记录是比较好处理的<BR>–删除n1,n2中若干条记录<BR>delete from n1 where orderID 
in (’10728′,’10730′)<BR>delete from n2 where orderID in (’11000′,’11001′)</P>
<P>–*************************************************************<BR>– 
双方都有该记录却不完全相同<BR>select * from n1 where orderid in<BR>(<BR>select OrderID from 
(select * from n1<BR>union<BR>select * from n2) a group by OrderID having 
count(*) &gt; 1<BR>)<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)</P>
<P>9.3、四种方法取表里n到m条纪录：</P>
<P>1.<BR>select top m * into 临时表(或表变量) from tablename order by columnname — 将top 
m笔插入<BR>set rowcount n<BR>select * from 表变量 order by columnname desc</P>
<P>2.<BR>select top n * from<BR>(select top m * from tablename order by 
columnname) a<BR>order by columnname desc</P>
<P>3.如果tablename里没有其他identity列，那么：<BR>select identity(int) id0,* into #temp from 
tablename</P>
<P>取n到m条的语句为：<BR>select * from #temp where id0 &gt;=n and id0 
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</P>
<P>select * from systypes<BR>select * from syscolumns where id = 
object_id(’a_dist’)</P>
<P>9.4.查询数据的最大排序问题（只能用一条语句写）<BR>CREATE TABLE hard (qu char (11) ,co char (11) 
,je numeric(3, 0)) </P>
<P>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)</P>
<P>要求查询出来的结果如下：</P>
<P>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</P>
<P>就是要按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>9.5.求删除重复记录的sql语句？<BR>怎样把具有相同字段的纪录删除，只留下一条。<BR>例如，表test里有id,name字段<BR>如果有name相同的记录 
只留下一条，其余的删除。<BR>name的内容不定，相同的记录数不定。<BR>有没有这样的sql语句？<BR>==============================<BR>A:一个完整的解决方案：</P>
<P>将重复的记录记入temp1表:<BR>select [标志字段id],count(*) into temp1 from [表名]<BR>group by 
[标志字段id]<BR>having count(*)&gt;1</P>
<P>2、将不重复的记录记入temp1表:<BR>insert temp1<BR>select [标志字段id],count(*) from 
[表名]<BR>group by [标志字段id]<BR>having count(*)=1</P>
<P>3、作一个包含所有不重复记录的表：<BR>select * into temp2 from [表名]<BR>where 标志字段id in(select 
标志字段id from temp1)</P>
<P>4、删除重复表:<BR>delete [表名]</P>
<P>5、恢复表：<BR>insert [表名]<BR>select * from temp2</P>
<P>6、删除临时表:<BR>drop table temp1<BR>drop table 
temp2<BR>================================<BR>B:<BR>create table a_dist(id 
int,name varchar(20))</P>
<P>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’)</P>
<P>exec up_distinct ‘a_dist’,'id’</P>
<P>select * from a_dist</P>
<P>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(*) &gt; 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</P>
<P>select * from systypes<BR>select * from syscolumns where id = 
object_id(’a_dist’)</P>
<P>10.1. 行列转换–普通 </P>
<P>假设有张学生成绩表(CJ)如下<BR>Name Subject Result<BR>张三 语文 80<BR>张三 数学 90<BR>张三 物理 
85<BR>李四 语文 85<BR>李四 数学 92<BR>李四 物理 82 </P>
<P>想变成<BR>姓名 语文 数学 物理<BR>张三 80 90 85<BR>李四 85 92 82 </P>
<P>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) </P>
<P>10.2. 行列转换–合并 </P>
<P>有表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 </P>
<P>创建一个合并的函数<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 </P>
<P>–调用自定义函数得到结果<BR>select distinct id,dbo.fmerg(id) from 表A </P>
<P>10.3. 如何取得一个数据表的所有列名 </P>
<P>方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。<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 </P>
<P>是不是太简单了？ 呵呵 不过经常用阿. </P>
<P>10.4. 通过SQL语句来更改用户的密码 </P>
<P>修改别人的,需要sysadmin role<BR>EXEC sp_password NULL, ‘newpassword’, ‘User’ </P>
<P>如果帐号为SA执行EXEC sp_password NULL, ‘newpassword’, sa </P>
<P>10.5. 怎么判断出一个表的哪些字段不允许为空？ </P>
<P>select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=’NO’ and 
TABLE_NAME=tablename </P>
<P>10.6. 如何在数据库里找到含有相同字段的表？<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=’你的字段名字’ </P>
<P>b. 未知列名查所有在不同表出现过的列名<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>) </P>
<P>10.7. 查询第xxx行数据 </P>
<P>假设id是主键：<BR>select *<BR>from (select top xxx * from yourtable) aa<BR>where 
not exists(select 1 from (select top xxx-1 * from yourtable) bb where 
aa.id=bb.id) </P>
<P>如果使用游标也是可以的<BR>fetch absolute [number] from [cursor_name]<BR>行数为绝对行数 </P>
<P>10.8. SQL Server日期计算<BR>a. 一个月的第一天<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. 上个月的最后一天<BR>SELECT dateadd(ms,-3,DATEADD(mm, 
DATEDIFF(mm,0,getdate()), 0))<BR>f. 去年的最后一天<BR>SELECT dateadd(ms,-3,DATEADD(yy,