现有两张表：
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 * from (
select Udept,Lstype...... from Dept left join Leave --明细
union all
select Udept,Lstype,sum()...... from Dept left join Leave group by Udept,Lstype --小计
union all
select null Udept,null Lstype,sum()...... from Dept left join Leave --总计
) aaa
order by ...... --排序
------解决思路----------------------
;with Dept(ID,DName,ParentDept) as
(
select 1, '总经办', 0 union all
select 2, '行政部', 1 union all
select 3, '企划部', 1
),
leave(ID,UserName,Udept,Uposition,Lstart,Lend,Lstatus,Lstype) as
(
select 1, '张三', 2, '科员', '2015-06-09', '2015-06-10', '2', 1 union all
select 2, '李四', 3, '职员', '2015-06-22', '2015-06-25', '3', 2 union all
select 3, '王五', 2, '职员', '2015-05-19', '2015-05-22', '3', 2
),
t1 as
(
select
a.ID, a.UserName, a.Udept, a.Uposition,a.Lstart,a.Lend,a.Lstatus,a.Lstype, b.ID as sort
from leave as a
left join Dept as b
on a.Udept = b.id
),
t2 as
(
select Udept, count(udept) as Lstype, (Udept+1) as sort from leave group by Udept
),
t3 as
(
select count(id) as Lstype, (max(Udept)+2) as sort from leave
)
----------------------------------------------------------------------------------
select
row_number() over(order by t.sort, t.id) as id,
t.UserName,isnull(t1.Dname, '') as Udept,
t.Uposition,t.Lstart,t.Lend,t.Lstatus,t.Lstype
from
(
select ID,UserName,Udept,Uposition,Lstart,Lend,Lstatus,Lstype, sort from t1
union all
select 0, '小计', Udept, '','','','', Lstype, sort from t2
union all
select 0, '总计', -1, '','','','', Lstype, sort from t3
) as t
left join Dept as t1
on t.Udept = t1.id
----------------------------------------------------------------------------------
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-06-22 2015-06-25 3 2
5 小计 企划部 1
6 总计 3