[转载]精妙SQL语句介绍----(3)

-- 双方都有该记录却不完全相同 ^ 92eNf6f  
select * from n1 where orderid in(select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1) "Z# +szGAM  
union q rb:O5*,l  
--n2中存在但在n1中不存的在10728,10730 p91f1M>N  
select * from n1 where OrderID not in (select OrderID from n2) UfuFij9  
union "\p{=w),  
--n1中存在但在n2中不存的在11000,11001 ~.O&{f0&@  
select * from n2 where OrderID not in (select OrderID from n1) u08eK!V  
四种方法取表里n到m条纪录： <$(H{!-;  
1. #lZM*X /  
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入 _wXp*fl H#  
set rowcount n 04=0-^y}v  
select * from 表变量 order by columnname desc 4C!M6x$.  
2. 8 P.!-}_R  
select top n * from (select top m * from tablename order by columnname) a order by columnname desc &c|^0rdc  
3.如果tablename里没有其他identity列，那么： mB{IF))  
select identity(int) id0,* into #temp from tablename < l9OR  
取n到m条的语句为： J&QlTo\FIm  
select * from #temp where id0 >=n and id0 <= m g=opr``  
如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行： >1/ r.4X'|  
exec sp_dboption 你的DB名字,'select into/bulkcopy',true aP/f(z sh  
4.如果表里有identity属性，那么简单： `ID2VZ2 
select * from tablename where identitycol between n and m }* vgK#rF  
如何删除一个表中重复的记录？ U^O0H[  
create table a_dist(id int,name varchar(20)) {wD`0yb5  
insert into a_dist values(1,'abc') XhOr`- Ae  
insert into a_dist values(1,'abc') z@O\Mxt&  
insert into a_dist values(1,'abc') \NefID7wr  
insert into a_dist values(1,'abc') * ;vr~(  
exec up_distinct 'a_dist','id' @n*n 9s/  
select * from a_dist + ^4RcIfM  
create procedure up_distinct(@t_name varchar(30),@f_key varchar(30)) hgL_#3o  
--f_key表示是分组字段﹐即主键字段 "
N  
as nK ;?&IH|\  
begin qec*,vW5x,  
declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer bOb 
select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1' I~ S4y+  
exec(@sql)
}7T0w4xeJ  
open cur_rows Z0bWQ^X  
fetch cur_rows into @id,@max SyVI34(k  
while @@fetch_status=0 t<r_m\B  
begin ^t^ +q:  
select @max = @max -1 +m68:cf  
set rowcount @max [:wR?T!4  
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key Hb r3I
c^  
if @type=56 :vi1a|k  
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id vO&X&C%h  
if @type=167
)]/2 ?C:e  
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' M&.@og0  
exec(@sql) :d t^  
fetch cur_rows into @id,@max o~Um 
end ^!ova%  
close cur_rows ve-?D[  
deallocate cur_rows  lcQJhC?  
set rowcount 0 C-S8=n  
end l[$DjW"Mo  
select * from systypes d{'d?;f  
select * from syscolumns where id = object_id('a_dist') zGZcv/[  
查询数据的最大排序问题（只能用一条语句写） "XCG( H6  
CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0)) no7Kn:  
insert into hard values ('A','1',3) ?=K=[  
insert into hard values ('A','2',4) h"$ *14 6  
insert into hard values ('A','4',2) 4CG4U;  
insert into hard values ('A','6',9) `#+>4`>  
insert into hard values ('B','1',4) pq8U5S5Du  
insert into hard values ('B','2',5) WqB5yJF  
insert into hard values ('B','3',6) X:=:t  
insert into hard values ('C','3',4)  D4{nB 5  
insert into hard values ('C','6',7) HFAC .G  
insert into hard values ('C','2',3) '+}tVkgC  
要求查询出来的结果如下： =l
:)  
qu co je x$}R3%'3n  
----------- ----------- ----- +=.TM`dW  
A 6 9 LM=%Q\{  
A 2 4 )k7)   
B 3 6 3P)u#:u   
B 2 5 q
|jB ."  
C 6 7 ,q2VIbTVUg  
C 3 4 4B_[idu|-f  
就是要按qu分组，每组中取je最大的前2位！！ 53SWi:S-C  
而且只能用一句sql语句！！！ #aZ[   
select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je) Cd`m2!{R