跪求大神给一个sql分类汇总语句

Question

现有两张表：
Dept：
ID DName ParentDept
1 总经办 0
2 行政部 1
3 企划部 1
Leave:
ID UserName Udept Uposition Lstart Lend Lstatus Lstype 1 张三 2 科员 2015-06-09 2015-06-10 2 1
2 李四 3 职员 2015-06-22 2015-06-25 3 2
3 王五 2 职员 2015-05-19 2015-05-22 3 2
要求先按照Udept、Lstype 进行分类汇总
sql语句分类汇总后
ID UserName Udept Uposition Lstart Lend Lstatus Lstype
1 张三 行政部 科员 2015-06-09 2015-06-10 2 1
2 王五 行政部 职员 2015-05-19 2015-05-22 3 2
3 小计 行政部 2
4 王五 企划部 职员 2015-05-19 2015-05-22 3 2
5 小计 企划部 1
6 总计 3

Answer 1

代码如下：

select ROW_NUMBER() OVER(order by Udept desc) as ID,

UserName = CASE

  WHEN GROUPING(Udept)=0 and GROUPING(UserName) = 1 THEN '小计'

  WHEN GROUPING(UserName) = 1 and GROUPING(Udept)=1 THEN '总计'

  ELSE UserName END,

Udept = CASE

  WHEN GROUPING(UserName) = 1 and GROUPING(Udept) = 1 THEN CONVERT(varchar(10),COUNT(*))

  WHEN GROUPING(UserName) = 1 THEN (select DName FROM Dept WHERE ID = Udept)

  ELSE (select DName FROM Dept WHERE ID = Udept) END,

Uposition = CASE

  WHEN GROUPING(UserName) = 1 and GROUPING(Udept) = 1 THEN ''

  WHEN GROUPING(UserName) = 1 THEN CONVERT(varchar(10),COUNT(*))

  ELSE MAX(Uposition) END,

Lstart = CASE

  WHEN GROUPING(UserName) = 1 and GROUPING(Udept) = 1 THEN ''

  WHEN GROUPING(UserName) = 1 THEN ''

  ELSE MAX(Lstart) END,

Lend = CASE

  WHEN GROUPING(UserName) = 1 and GROUPING(Udept) = 1 THEN ''

  WHEN GROUPING(UserName) = 1 THEN ''

  ELSE MAX(Lend) END,

Lstatus = CASE

  WHEN GROUPING(UserName) = 1 and GROUPING(Udept) = 1 THEN 0

  WHEN GROUPING(UserName) = 1 THEN 0

  ELSE MAX(Lstatus) END,

Lstype = CASE

  WHEN GROUPING(UserName) = 1 and GROUPING(Udept) = 1 THEN 0

  WHEN GROUPING(UserName) = 1 THEN 0

  ELSE MAX(Lstype) END

from Leave

Group by Udept,UserName

WITH ROLLUP

效果如下：

在小计和总计后面的 Lstatus，Lstype 字段显示为0，是因为 Lstatus，Lstype 的字段类型为int,只要改为varchar即可