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

现有两张表：
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

代码如下：

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即可

一般是取出按照Udept排序的数据后再在代码中循环拼接，或者sql中每条数据带count，直接取出来就是你说的这个格式的没有遇到过。
SELECT
L.ID AS ID,
L.UserName AS UserName,
D.DName AS Udept,
L.Uposition AS Uposition,
L.Lstart AS Lstart,
L.Lend AS Lend,
L.Lstatus AS Lstatus,
L.Lstype AS Lstype,
(SELECT COUNT(*) FROM leave WHERE Udept = L.Udept) AS DeptCount,
(SELECT COUNT(*) FROM leave) AS SumCount
FROM leave L
LEFT JOIN dept D ON L.Udept = D.ID
ORDER BY L.Udept