关闭

SQL语句积累

903人阅读 评论(0) 收藏 举报

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

0
0

猜你在找
【直播】机器学习&数据挖掘7周实训--韦玮
【套餐】系统集成项目管理工程师顺利通关--徐朋
【直播】3小时掌握Docker最佳实战-徐西宁
【套餐】机器学习系列套餐(算法+实战)--唐宇迪
【直播】计算机视觉原理及实战--屈教授
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之矩阵--黄博士
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之凸优化--马博士
【套餐】Javascript 设计模式实战--曾亮
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:215829次
    • 积分:2683
    • 等级:
    • 排名:第13449名
    • 原创:37篇
    • 转载:64篇
    • 译文:2篇
    • 评论:30条
    文章分类
    最新评论
    Code Source