你的位置:首页 > 数据库

[数据库]这辈子写过的比较有意思的几个sql


递归

with myRecursion as(

select * from recursion where id=1

union all select r.* from myRecursion m,recursion r where m.id=r.pid

)

select * from myRecursion

Ps:union all 不去重求并集

 

很多地方都用到了递归,比如asp.net mvc里的模型绑定就是递归绑定的,还比如树状菜单

 

排名

下表是一个销售业绩表,我对销售业绩做一个排名,显示出排名结果

 

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank

FROM ranking  a1, ranking  a2

WHERE a1.Sales < a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)

GROUP BY a1.Name, a1.Sales

ORDER BY a1.Sales DESC, a1.Name DESC;

结果:

 

重点是自己和自己比较,找出a1的Sales小于a2的Sales的数据 或者Name和Sales都相等的数据(a1里的全部数据去对比a2里的每一个数据)

未分组的结果:

SELECT a1.Name, a1.Sales, a2.sales Sales_Rank

FROM ranking  a1, ranking  a2

WHERE a1.Sales < a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)

结果如图,结果一目了然。只要分组count一下就是排名了。

其实还有一个问题就是有并列排名,比如上图中有个并列第3的,第四名就不存在了。

这些都可根据具体的规则用程序去调整,嘻嘻

 

去重

有时我们会遇到一些表里有些重复的数据,如图:

 

第一种,去除全部重复的数据除id以外

declare @t1 table(id int,name nchar(10),Text nchar(10))

insert into @t1(Name,Text)(select distinct Name,Text from mydistinct1)

delete from mydistinct1

insert into mydistinct1(name,text)(select name,text from @t1)

Ps:@t 定义一个虚拟表,向虚拟表里插入用distinct去重的数据,清空原表,再把虚拟表里的数据插入到原表。

第二种,去除指定列重复的数据。

delete from mydistinct where id not in(select MIN(id) from mydistinct group by name)

Ps:sql很简单,分组后取分组里一个id,这里取最小的一个,删除除此之外的id

行转列

 

select 姓名 as 姓名 ,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理

from tb

group by 姓名

结果如图:

 

Ps:一目了然就不多解释了

For 现实分组后指定列的全部数据

数据表:

 

For 结果如下:

select * from for

 

 

分组结果:

select name,min(text) from for

 

分组后除此分组列,其他列要显示就要使用聚合函数,只能显示结果中的一个或数量或合计

我们可以利用for 的特性把全部数据都显示在一列中,并指定显示格式

逗号间隔:

select text+',' from for

 

逗号间隔显示分组后非分组列:

select name,(select text+',' from for

from for

 

去掉结尾的逗号:

使用 left函数截取

select name,LEFT(text,LEN(text)-1)text

from(select name,(select text+',' from for

from for