这辈子写过的比较有意思的几个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 xml path 现实分组后指定列的全部数据
数据表：
For xml path 结果如下：
select * from forxmlpath for XML path('')
分组结果：
select name,min(text) from forxmlpath group by name
分组后除此分组列，其他列要显示就要使用聚合函数，只能显示结果中的一个或数量或合计
我们可以利用for xml path的特性把全部数据都显示在一列中，并指定显示格式
逗号间隔：
select text+',' from forxmlpath for xml path('')
逗号间隔显示分组后非分组列：
select name,(select text+',' from forxmlpath where a.name=name for XML path(''))
from forxmlpath a group by name
去掉结尾的逗号：
使用 left函数截取
select name,LEFT(text,LEN(text)-1)text
from(select name,(select text+',' from forxmlpath where a.name=name for XML path('')) text
from forxmlpath a group by name)t
