寶貝計劃
2015-10-07 06:43
采纳率: 66.7%
浏览 2.8k

跪求大神给一个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

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • Joke_k 2015-10-08 02:31
    已采纳

    代码如下:

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

    打赏 评论
  • 晓瑶飞雨 2015-10-07 08:32

    一般是取出按照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

    打赏 评论

相关推荐 更多相似问题