SQL语句积累

1.       获得某一栏位有重复的记录：

declare @tab table(number1 int,number2 int,content varchar(20))

insert @tab values(2,3,'abc')

insert @tab values(2,4,'bb')

insert @tab values(2,5,'abc')

insert @tab values(3,6,'ccc')

insert @tab values(3,7,'ccc')

insert @tab values(6,3,'dd')

select * from @tab where content in(select content from @tab group by content having count(content)>1)

select distinct * from @tab t where (select count(content) from @tab where t.content=content)>1

2.       按拼音排序

select * from titles  order by type collate Chinese_PRC_CS_AS_KS_WS

3.       @@rowcount 表中记录数量

4.       每个分组中第二大的值

declare @t table (N int ,W bit)

insert into @t select 2005001,0

union all select 2005002,0

union all select 2005003,0

union all select 2005200,1

union all select 2005201,1

union all select 2005202,1

select * from @t where n in (select max(N) as N from @t  where N not in (select max(n) as N from @t group by w  ) group by w)

5.        

create  table a

(编码varchar(20),名称varchar(20),数量int,日期varchar(20))

create  table b

(编码varchar(20),名称varchar(20),数量int,日期varchar(20))

create  table c

(编码varchar(20),名称varchar(20),数量int,日期varchar(20))

 

insert a

select '001','aa',100,'1-1' union all

select '002','bb',20,'1-1'

 

insert b

select '001','aa',100,'1-20' union all

select '002','bb',110,'1-1' union all

select '003','cc',10,'1-4'

 

insert c

select '001','aa',100,'1-1'

 

select * from a

inner join b on a.编码=b.编码

select * from a

left join b on a.编码=b.编码

select * from a

full join b on a.编码=b.编码

select

isnull(a.编码,isnull(b.编码,c.编码)) as 编码

,isnull(a.名称,isnull(b.名称,c.名称)) as 名称

,isnull(a.数量,0) as a数量

,isnull(b.数量,0) as b数量

,isnull(c.数量,0) as c数量

 

from a

full join  b on a.编码=b.编码

full join  c on a.编码=c.编码

 

 

drop table a

drop table b

drop table c

6.        

declare @t table (tid int)

insert into @t select 1

insert into @t select 2

insert into @t select 3

insert into @t select 4

insert into @t select 5

insert into @t select 7

insert into @t select 9

insert into @t select 10

select min(tid) as tid from (select tid+1 as tid from @t where tid+1 not in (select * from @t)) t

 

declare @tid int

select @tid = min(tid) from @t

 

while exists(select 1 from @t where tid = @tid)

begin

set @tid = @tid + 1

end

 

select @tid

-----------------------------------------------

select min(t.a) from (select tid+1 a from @t t where not exists(select 1 from @t where tid = t.tid+1)) t