27,579
社区成员
发帖
与我相关
我的任务
分享请教sql语句
表1--department部门表：
departid(部门代码，varchar(30)) departname(部门名称，varchar(20))
T001 ----- 一级部门 SHBT
T001001 --- 二级部门 GMA
T001001001 --- 三级部门 DTA
T001001001001-- 四级部门 ECH5
T001002 GMO
T001002001 DTC
T001002001001 ECH6
T002 WXBT
T002001 GMK
T002001001 DTB
T002001001001 ECH7
.........................
整个部门结构数据用图示是这样：SHBT---GMA---DTA---ECH5
SHBT--GMO--DTC---ECH6
WXBT--GMK--DTB---ECH7
.................
表2：employee人员表：
departid(部门代码varchar(30)) empid（工号varchar(10)） empname(姓名varchar(20)) empcard（卡号 varchar(10)）
T001001001001 HR001 张三 8379546018
T001002001001 HR021 李四 8468135891
T002001001001 TK135 王五 1234567890
........
表3：employee_leave离职人员表：
(部门代码varchar(30)) （工号varchar(10)） (姓名varchar(20)) （卡号 varchar(10)） date 离职时间
departid empid empname empcard checkdatetime
T002001001001 TK168 赵六 1788399620 2013-12-02 09:30:10
........
表4：toll_card_detail消费记录表
varchar(30) varchar(30) varchar(30) int date(消费时间) money（消费金额）varchar(5) (设备号) 窗口号varchar(5)
departid empid empname empcard checkdatetime eatmoney machineid window_id
T001001001001 HR001 张三 8379546018 2013-12-01 08:33:25 5.00 001 003
T001001001001 HR001 张三 8379546018 2013-12-01 11:27:25 11.00 003 004
T001001001001 HR001 张三 8379546018 2013-12-01 18:42:15 9.00 003 004
T002001001001 TK135 王五 1234567890 2013-12-01 08:10:31 4.00 002 001
T002001001001 TK135 王五 1234567890 2013-12-01 12:10:31 8.00 003 004
T001002001001 HR021 李四 8468135891 2013-12-01 22:55:28 6.00 001 003
T002001001001 TK168 赵六 1788399620 2013-12-01 07:40:21 4.00 003 004
T002001001001 TK168 赵六 1788399620 2013-12-01 11:37:21 8.00 002 001
T001001001001 HR001 张三 8379546018 2013-12-01 15:45:15 6.00 003 004
........
表5：window窗口表 表6：machine设备表 表7：shift消费时段表
window_id window_name machineid window_id shiftid shiftname starttime endtime
001 自选窗口1 001 003 01 早餐 07:00 09:00
002 早点窗口 002 001 02 午餐 11：00 13：00
003 面点窗口 003 004 03 晚餐 16：00 19：00
004 小卖部 04 夜宵 21：00 23：00
要求输出以下数据：
1）消费汇总表：（按四级部门统计），需包含离职人员
一级部门 二级部门 三级部门 四级部门 早餐次 早餐额 午餐次 午餐额 晚餐次 晚餐额 夜宵次 夜宵额 其他餐次 其他餐额
SHBT GMA DTA ECH5 1 5.00 1 11.00 1 9.00 0 0.00 1 6.00
WXBT GMK DTB ECH7 2 8.00 2 16.00 0 0.00 0 0.00 0 0.00
SHBT GMO DTC ECH6 0 0.00 0 0.00 0 0.00 1 6.00 0 0.00
................
(2)1）消费汇总表：（按一级部门统计）
一级部门 早餐次 早餐额 午餐次 午餐额 晚餐次 晚餐额 夜宵次 夜宵额 其他餐次 其他餐额
SHBT 1 5.00 1 11.00 1 9.00 1 6.00 1 6.00
WXBT 2 8.00 2 16.00 0 0.00 0 0.00 0 0.00
.................
（3）消费汇总表：按窗口统计
一级部门 二级部门 三级部门 四级部门 窗口号 消费次数 消费金额合计
SHBT GMA DTA ECH5 003 1 5.00
SHBT GMO DTC ECH6 003 1 6.00
SHBT GMA DTA ECH5 004 3 26.00
WXBT GMK DTB ECH7 001 2 12.00
WXBT GMK DTB ECH7 004 2 12.00
................
敬请各路高手不吝赐教，方便的话可以加下我的QQ：656213051，以便沟通更方便及时，跪谢！
departid(部门代码，varchar(30)) departname(部门名称，varchar(20))
T001 ----- 一级部门 SHBT
T001001 --- 二级部门 GMA
T001001001 --- 三级部门 DTA
T001001001001-- 四级部门 ECH5
T001002 GMO
T001002001 DTC
T001002001001 ECH6
T002 WXBT
T002001 GMK
T002001001 DTB
T002001001001 ECH7
.........................
整个部门结构数据用图示是这样：SHBT---GMA---DTA---ECH5
SHBT--GMO--DTC---ECH6
WXBT--GMK--DTB---ECH7
.................
表2：employee人员表：
departid(部门代码varchar(30)) empid（工号varchar(10)） empname(姓名varchar(20)) empcard（卡号 varchar(10)）
T001001001001 HR001 张三 8379546018
T001002001001 HR021 李四 8468135891
T002001001001 TK135 王五 1234567890
........
表3：employee_leave离职人员表：
(部门代码varchar(30)) （工号varchar(10)） (姓名varchar(20)) （卡号 varchar(10)） date 离职时间
departid empid empname empcard checkdatetime
T002001001001 TK168 赵六 1788399620 2013-12-02 09:30:10
........
表4：toll_card_detail消费记录表
varchar(30) varchar(30) varchar(30) int date(消费时间) money（消费金额）varchar(5) (设备号) 窗口号varchar(5)
departid empid empname empcard checkdatetime eatmoney machineid window_id
T001001001001 HR001 张三 8379546018 2013-12-01 08:33:25 5.00 001 003
T001001001001 HR001 张三 8379546018 2013-12-01 11:27:25 11.00 003 004
T001001001001 HR001 张三 8379546018 2013-12-01 18:42:15 9.00 003 004
T002001001001 TK135 王五 1234567890 2013-12-01 08:10:31 4.00 002 001
T002001001001 TK135 王五 1234567890 2013-12-01 12:10:31 8.00 003 004
T001002001001 HR021 李四 8468135891 2013-12-01 22:55:28 6.00 001 003
T002001001001 TK168 赵六 1788399620 2013-12-01 07:40:21 4.00 003 004
T002001001001 TK168 赵六 1788399620 2013-12-01 11:37:21 8.00 002 001
T001001001001 HR001 张三 8379546018 2013-12-01 15:45:15 6.00 003 004
........
表5：window窗口表 表6：machine设备表 表7：shift消费时段表
window_id window_name machineid window_id shiftid shiftname starttime endtime
001 自选窗口1 001 003 01 早餐 07:00 09:00
002 早点窗口 002 001 02 午餐 11：00 13：00
003 面点窗口 003 004 03 晚餐 16：00 19：00
004 小卖部 04 夜宵 21：00 23：00
要求输出以下数据：
1）消费汇总表：（按四级部门统计），需包含离职人员
一级部门 二级部门 三级部门 四级部门 早餐次 早餐额 午餐次 午餐额 晚餐次 晚餐额 夜宵次 夜宵额 其他餐次 其他餐额
SHBT GMA DTA ECH5 1 5.00 1 11.00 1 9.00 0 0.00 1 6.00
WXBT GMK DTB ECH7 2 8.00 2 16.00 0 0.00 0 0.00 0 0.00
SHBT GMO DTC ECH6 0 0.00 0 0.00 0 0.00 1 6.00 0 0.00
................
(2)1）消费汇总表：（按一级部门统计）
一级部门 早餐次 早餐额 午餐次 午餐额 晚餐次 晚餐额 夜宵次 夜宵额 其他餐次 其他餐额
SHBT 1 5.00 1 11.00 1 9.00 1 6.00 1 6.00
WXBT 2 8.00 2 16.00 0 0.00 0 0.00 0 0.00
.................
（3）消费汇总表：按窗口统计
一级部门 二级部门 三级部门 四级部门 窗口号 消费次数 消费金额合计
SHBT GMA DTA ECH5 003 1 5.00
SHBT GMO DTC ECH6 003 1 6.00
SHBT GMA DTA ECH5 004 3 26.00
WXBT GMK DTB ECH7 001 2 12.00
WXBT GMK DTB ECH7 004 2 12.00
................
敬请各路高手不吝赐教，方便的话可以加下我的QQ：656213051，以便沟通更方便及时，跪谢！
...全文
请发表友善的回复…
发表回复
yinxionglu2009 2014-04-15
- 打赏
- 举报
正是因为有了各位大大的奉献，CSDN才会欣欣向荣，向各位辛苦回帖的大大深表敬意
yinxionglu2009 2014-04-15
- 打赏
- 举报
先不管3721，感谢楼上各位大大的帮助，尤其chwnrthd，太强悍了，验证后一定第一时间给分，谢谢
昵称90天可改一次 2014-04-15
- 打赏
- 举报
1其他 修正
create table department(departid varchar(30),departname varchar(20))
insert department
select 'T001','SHBT' union all
select 'T001001','GMA' union all
select 'T001001001','DTA' union all
select 'T001001001001','ECH5' union all
select 'T001002','GMO' union all
select 'T001002001','DTC' union all
select 'T001002001001','ECH6' union all
select 'T002','WXBT' union all
select 'T002001','GMK' union all
select 'T002001001','DTB' union all
select 'T002001001001','ECH7'
create table employee(departid varchar(30),empid varchar(10),empname varchar(20),empcard varchar(10))
insert employee
select 'T001001001001','HR001','张三','8379546018' union all
select 'T001002001001','HR021','李四','8468135891' union all
select 'T002001001001','TK135','王五','1234567890'
create table employee_leave(departid varchar(30),empid varchar(10),empname varchar(20),empcard varchar(10),checkdatetime datetime)
insert employee_leave
select 'T002001001001','TK168','赵六','1788399620','2013-12-02 09:30:10'
create table toll_card_detail(departid varchar(30),empid varchar(30),empname varchar(30),
empcard varchar(10),checkdatetime datetime,eatmoney money,
machineid varchar(5),window_id varchar(5))
insert toll_card_detail
select 'T001001001001','HR001','张三','8379546018','2013-12-01 08:33:25',5.00,'001','003' union all
select 'T001001001001','HR001','张三','8379546018','2013-12-01 11:27:25',11.00,'003','004' union all
select 'T001001001001','HR001','张三','8379546018','2013-12-01 18:42:15',9.00,'003','004' union all
select 'T002001001001','TK135','王五','1234567890','2013-12-01 08:10:31',4.00,'002','001' union all
select 'T002001001001','TK135','王五','1234567890','2013-12-01 12:10:31',8.00,'003','004' union all
select 'T001002001001','HR021','李四','8468135891','2013-12-01 22:55:28',6.00,'001','003' union all
select 'T002001001001','TK168','赵六','1788399620','2013-12-01 07:40:21',4.00,'003','004' union all
select 'T002001001001','TK168','赵六','1788399620','2013-12-01 11:37:21',8.00,'002','001' union all
select 'T001001001001','HR001','张三','8379546018','2013-12-01 15:45:15',6.00,'003','004'
create table window(window_id varchar(5),window_name varchar(10))
insert window
select '001','自选窗口' union all
select '002','早点窗口' union all
select '003','面点窗口' union all
select '004','小卖部'
create table machine(machineid varchar(5),window_id varchar(5))
insert machine
select '001','003' union all
select '002','001' union all
select '003','004'
create table shift(shiftid varchar(5),shiftname varchar(10),starttime datetime,endtime datetime)
insert shift
select '01','早餐','07:00','09:00' union all
select '02','午餐','11:00','13:00' union all
select '03','晚餐','16:00','19:00' union all
select '04','夜宵','21:00','23:00'
--1
select 一级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 4)),
二级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 7)),
三级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 10)),
四级部门=(select departname from department where departid=a.departid),
早餐次=SUM(case when s.shiftname='早餐' then 1 else 0 end),
早餐额=SUM(case when s.shiftname='早餐' then tcd.eatmoney else 0 end),
午餐次=SUM(case when s.shiftname='午餐' then 1 else 0 end),
午餐额=SUM(case when s.shiftname='午餐' then tcd.eatmoney else 0 end),
晚餐次=SUM(case when s.shiftname='晚餐' then 1 else 0 end),
晚餐额=SUM(case when s.shiftname='晚餐' then tcd.eatmoney else 0 end),
夜宵次=SUM(case when s.shiftname='夜宵' then 1 else 0 end),
夜宵额=SUM(case when s.shiftname='夜宵' then tcd.eatmoney else 0 end),
其他次=SUM(case when s.shiftname is null then 1 else 0 end),
其他额=SUM(case when s.shiftname is null then tcd.eatmoney else 0 end)
from (select departid,empid,empname,empcard
from employee
union all
select departid,empid,empname,empcard
from employee_leave
) a
inner join toll_card_detail tcd
on a.empcard=tcd.empcard
left join shift s
on CONVERT(varchar(100), tcd.checkdatetime, 108) between s.starttime and s.endtime
inner join window w
on tcd.window_id=w.window_id
inner join machine m
on m.machineid=tcd.machineid
group by a.departid
/*
SHBT GMA DTA ECH5 1 5.00 1 11.00 1 9.00 0 0.00 1 6.00
SHBT GMO DTC ECH6 0 0.00 0 0.00 0 0.00 1 6.00 0 0.00
WXBT GMK DTB ECH7 2 8.00 2 16.00 0 0.00 0 0.00 0 0.00
*/
--2
select 一级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 4)),
早餐次=SUM(case when s.shiftname='早餐' then 1 else 0 end),
早餐额=SUM(case when s.shiftname='早餐' then tcd.eatmoney else 0 end),
午餐次=SUM(case when s.shiftname='午餐' then 1 else 0 end),
午餐额=SUM(case when s.shiftname='午餐' then tcd.eatmoney else 0 end),
晚餐次=SUM(case when s.shiftname='晚餐' then 1 else 0 end),
晚餐额=SUM(case when s.shiftname='晚餐' then tcd.eatmoney else 0 end),
夜宵次=SUM(case when s.shiftname='夜宵' then 1 else 0 end),
夜宵额=SUM(case when s.shiftname='夜宵' then tcd.eatmoney else 0 end)
from (select departid,empid,empname,empcard
from employee
union all
select departid,empid,empname,empcard
from employee_leave
) a
inner join toll_card_detail tcd
on a.empcard=tcd.empcard
left join shift s
on CONVERT(varchar(100), tcd.checkdatetime, 108) between s.starttime and s.endtime
inner join window w
on tcd.window_id=w.window_id
inner join machine m
on m.machineid=tcd.machineid
group by SUBSTRING(a.departid, 1, 4)
/*
SHBT 1 5.00 1 11.00 1 9.00 1 6.00
WXBT 2 8.00 2 16.00 0 0.00 0 0.00
*/
--3
select 一级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 4)),
二级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 7)),
三级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 10)),
四级部门=(select departname from department where departid=a.departid),
窗口号=w.window_id,
消费次数=count(w.window_id),
消费金额合计=SUM(tcd.eatmoney)
from (select departid,empid,empname,empcard
from employee
union all
select departid,empid,empname,empcard
from employee_leave
) a
inner join toll_card_detail tcd
on a.empcard=tcd.empcard
left join shift s
on CONVERT(varchar(100), tcd.checkdatetime, 108) between s.starttime and s.endtime
inner join window w
on tcd.window_id=w.window_id
inner join machine m
on m.machineid=tcd.machineid
group by a.departid,
w.window_id
/*
SHBT GMA DTA ECH5 003 1 5.00
SHBT GMA DTA ECH5 004 3 26.00
SHBT GMO DTC ECH6 003 1 6.00
WXBT GMK DTB ECH7 001 2 12.00
WXBT GMK DTB ECH7 004 2 12.00
*/
昵称90天可改一次 2014-04-15
- 打赏
- 举报
create table department(departid varchar(30),departname varchar(20))
insert department
select 'T001','SHBT' union all
select 'T001001','GMA' union all
select 'T001001001','DTA' union all
select 'T001001001001','ECH5' union all
select 'T001002','GMO' union all
select 'T001002001','DTC' union all
select 'T001002001001','ECH6' union all
select 'T002','WXBT' union all
select 'T002001','GMK' union all
select 'T002001001','DTB' union all
select 'T002001001001','ECH7'
create table employee(departid varchar(30),empid varchar(10),empname varchar(20),empcard varchar(10))
insert employee
select 'T001001001001','HR001','张三','8379546018' union all
select 'T001002001001','HR021','李四','8468135891' union all
select 'T002001001001','TK135','王五','1234567890'
create table employee_leave(departid varchar(30),empid varchar(10),empname varchar(20),empcard varchar(10),checkdatetime datetime)
insert employee_leave
select 'T002001001001','TK168','赵六','1788399620','2013-12-02 09:30:10'
create table toll_card_detail(departid varchar(30),empid varchar(30),empname varchar(30),
empcard varchar(10),checkdatetime datetime,eatmoney money,
machineid varchar(5),window_id varchar(5))
insert toll_card_detail
select 'T001001001001','HR001','张三','8379546018','2013-12-01 08:33:25',5.00,'001','003' union all
select 'T001001001001','HR001','张三','8379546018','2013-12-01 11:27:25',11.00,'003','004' union all
select 'T001001001001','HR001','张三','8379546018','2013-12-01 18:42:15',9.00,'003','004' union all
select 'T002001001001','TK135','王五','1234567890','2013-12-01 08:10:31',4.00,'002','001' union all
select 'T002001001001','TK135','王五','1234567890','2013-12-01 12:10:31',8.00,'003','004' union all
select 'T001002001001','HR021','李四','8468135891','2013-12-01 22:55:28',6.00,'001','003' union all
select 'T002001001001','TK168','赵六','1788399620','2013-12-01 07:40:21',4.00,'003','004' union all
select 'T002001001001','TK168','赵六','1788399620','2013-12-01 11:37:21',8.00,'002','001' union all
select 'T001001001001','HR001','张三','8379546018','2013-12-01 15:45:15',6.00,'003','004'
create table window(window_id varchar(5),window_name varchar(10))
insert window
select '001','自选窗口' union all
select '002','早点窗口' union all
select '003','面点窗口' union all
select '004','小卖部'
create table machine(machineid varchar(5),window_id varchar(5))
insert machine
select '001','003' union all
select '002','001' union all
select '003','004'
create table shift(shiftid varchar(5),shiftname varchar(10),starttime datetime,endtime datetime)
insert shift
select '01','早餐','07:00','09:00' union all
select '02','午餐','11:00','13:00' union all
select '03','晚餐','16:00','19:00' union all
select '04','夜宵','21:00','23:00'
--1 其他怎么来的没明白 自己添加
select 一级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 4)),
二级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 7)),
三级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 10)),
四级部门=(select departname from department where departid=a.departid),
早餐次=SUM(case when s.shiftname='早餐' then 1 else 0 end),
早餐额=SUM(case when s.shiftname='早餐' then tcd.eatmoney else 0 end),
午餐次=SUM(case when s.shiftname='午餐' then 1 else 0 end),
午餐额=SUM(case when s.shiftname='午餐' then tcd.eatmoney else 0 end),
晚餐次=SUM(case when s.shiftname='晚餐' then 1 else 0 end),
晚餐额=SUM(case when s.shiftname='晚餐' then tcd.eatmoney else 0 end),
夜宵次=SUM(case when s.shiftname='夜宵' then 1 else 0 end),
夜宵额=SUM(case when s.shiftname='夜宵' then tcd.eatmoney else 0 end)
from (select departid,empid,empname,empcard
from employee
union all
select departid,empid,empname,empcard
from employee_leave
) a
inner join toll_card_detail tcd
on a.empcard=tcd.empcard
inner join shift s
on CONVERT(varchar(100), tcd.checkdatetime, 108) between s.starttime and s.endtime
inner join window w
on tcd.window_id=w.window_id
inner join machine m
on m.machineid=tcd.machineid
group by a.departid
/*
SHBT GMA DTA ECH5 1 5.00 1 11.00 1 9.00 0 0.00
SHBT GMO DTC ECH6 0 0.00 0 0.00 0 0.00 1 6.00
WXBT GMK DTB ECH7 2 8.00 2 16.00 0 0.00 0 0.00
*/
--2
select 一级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 4)),
早餐次=SUM(case when s.shiftname='早餐' then 1 else 0 end),
早餐额=SUM(case when s.shiftname='早餐' then tcd.eatmoney else 0 end),
午餐次=SUM(case when s.shiftname='午餐' then 1 else 0 end),
午餐额=SUM(case when s.shiftname='午餐' then tcd.eatmoney else 0 end),
晚餐次=SUM(case when s.shiftname='晚餐' then 1 else 0 end),
晚餐额=SUM(case when s.shiftname='晚餐' then tcd.eatmoney else 0 end),
夜宵次=SUM(case when s.shiftname='夜宵' then 1 else 0 end),
夜宵额=SUM(case when s.shiftname='夜宵' then tcd.eatmoney else 0 end)
from (select departid,empid,empname,empcard
from employee
union all
select departid,empid,empname,empcard
from employee_leave
) a
inner join toll_card_detail tcd
on a.empcard=tcd.empcard
inner join shift s
on CONVERT(varchar(100), tcd.checkdatetime, 108) between s.starttime and s.endtime
inner join window w
on tcd.window_id=w.window_id
inner join machine m
on m.machineid=tcd.machineid
group by SUBSTRING(a.departid, 1, 4)
/*
SHBT 1 5.00 1 11.00 1 9.00 1 6.00
WXBT 2 8.00 2 16.00 0 0.00 0 0.00
*/
--3
select 一级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 4)),
二级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 7)),
三级部门=(select departname from department where departid=SUBSTRING(a.departid, 1, 10)),
四级部门=(select departname from department where departid=a.departid),
窗口号=w.window_id,
消费次数=count(w.window_id),
消费金额合计=SUM(tcd.eatmoney)
from (select departid,empid,empname,empcard
from employee
union all
select departid,empid,empname,empcard
from employee_leave
) a
inner join toll_card_detail tcd
on a.empcard=tcd.empcard
inner join shift s
on CONVERT(varchar(100), tcd.checkdatetime, 108) between s.starttime and s.endtime
inner join window w
on tcd.window_id=w.window_id
inner join machine m
on m.machineid=tcd.machineid
group by a.departid,
w.window_id
/*
SHBT GMA DTA ECH5 003 1 5.00
SHBT GMA DTA ECH5 004 2 20.00
SHBT GMO DTC ECH6 003 1 6.00
WXBT GMK DTB ECH7 001 2 12.00
WXBT GMK DTB ECH7 004 2 12.00
*/
xdashewan 2014-04-15
- 打赏
- 举报
WITH cte AS
(
SELECT TCD.departid, TCD.window_id, COUNT(0) AS CNT, SUM(TCD.eatmoney) AS money
FROM toll_card_detail TCD
GROUP BY TCD.departid, TCD.window_id)
SELECT d1.departname,
d2.departname,
d3.departname,
D.departname,
w.window_name,
ISNULL(cte1.CNT, '0') AS shift1,
ISNULL(cte1.money, '0') AS money1,
ISNULL(cte2.CNT, '0') AS shift2,
ISNULL(cte2.money, '0') AS money2,
ISNULL(cte3.CNT, '0') AS shift3,
ISNULL(cte3.money, '0') AS money3,
ISNULL(cte4.CNT, '0') AS shift4,
ISNULL(cte4.money, '0') AS money4,
ISNULL(cte5.CNT, '0') AS shift5,
ISNULL(cte5.money, '0') AS money5
FROM department D
LEFT JOIN cte cte1 ON D.departid = cte1.departid
LEFT JOIN cte cte2 ON D.departid = cte2.departid
LEFT JOIN cte cte3 ON D.departid = cte3.departid
LEFT JOIN cte cte4 ON D.departid = cte4.departid
LEFT JOIN cte cte5 ON D.departid = cte5.departid
LEFT JOIN department d1 ON SUBSRING(D.departid, 1, 3) = d1.departid
LEFT JOIN department d2 ON SUBSRING(D.departid, 4, 3) = d2.departid
LEFT JOIN department d3 ON SUBSRING(D.departid, 7, 3) = d3.departid
LEFT JOIN window w ON w.window_id = cte.window_id
WHERE LEN(D.departid) = 12
xdashewan 2014-04-15
- 打赏
- 举报
WITH cte AS
(
SELECT SUBSTRING(TCD.departid, 1, 3) AS departid, S.shiftid, COUNT(0) AS CNT, SUM(TCD.eatmoney) AS money
FROM toll_card_detail TCD
LEFT JOIN shift S ON TCD.checkdatetime BETWEEN S.starttime AND S.endtime
GROUP BY SUBSTRING(TCD.departid, 1, 3), S.shiftid
)
SELECT D.departname,
ISNULL(cte1.CNT, '0') AS shift1,
ISNULL(cte1.money, '0') AS money1,
ISNULL(cte2.CNT, '0') AS shift2,
ISNULL(cte2.money, '0') AS money2,
ISNULL(cte3.CNT, '0') AS shift3,
ISNULL(cte3.money, '0') AS money3,
ISNULL(cte4.CNT, '0') AS shift4,
ISNULL(cte4.money, '0') AS money4,
ISNULL(cte5.CNT, '0') AS shift5,
ISNULL(cte5.money, '0') AS money5
FROM department D
LEFT JOIN cte cte1 ON D.departid = cte1.departid
LEFT JOIN cte cte2 ON D.departid = cte2.departid
LEFT JOIN cte cte3 ON D.departid = cte3.departid
LEFT JOIN cte cte4 ON D.departid = cte4.departid
LEFT JOIN cte cte5 ON D.departid = cte5.departid
WHERE LEN(D.departid) = 3
AND cte1.shiftid = '01'
AND cte2.shiftid = '02'
AND cte3.shiftid = '03'
AND cte4.shiftid = '04'
AND cte5.shiftid IS NULL
xdashewan 2014-04-15
- 打赏
- 举报
WITH cte AS
(
SELECT TCD.departid, S.shiftid, COUNT(0) AS CNT, SUM(TCD.eatmoney) AS money
FROM toll_card_detail TCD
LEFT JOIN shift S ON TCD.checkdatetime BETWEEN S.starttime AND S.endtime
GROUP BY TCD.departid, S.shiftid
)
SELECT d1.departname,
d2.departname,
d3.departname,
D.departname,
ISNULL(cte1.CNT, '0') AS shift1,
ISNULL(cte1.money, '0') AS money1,
ISNULL(cte2.CNT, '0') AS shift2,
ISNULL(cte2.money, '0') AS money2,
ISNULL(cte3.CNT, '0') AS shift3,
ISNULL(cte3.money, '0') AS money3,
ISNULL(cte4.CNT, '0') AS shift4,
ISNULL(cte4.money, '0') AS money4,
ISNULL(cte5.CNT, '0') AS shift5,
ISNULL(cte5.money, '0') AS money5
FROM department D
LEFT JOIN cte cte1 ON D.departid = cte1.departid
LEFT JOIN cte cte2 ON D.departid = cte2.departid
LEFT JOIN cte cte3 ON D.departid = cte3.departid
LEFT JOIN cte cte4 ON D.departid = cte4.departid
LEFT JOIN cte cte5 ON D.departid = cte5.departid
LEFT JOIN department d1 ON SUBSRING(D.departid, 1, 3) = d1.departid
LEFT JOIN department d2 ON SUBSRING(D.departid, 4, 3) = d2.departid
LEFT JOIN department d3 ON SUBSRING(D.departid, 7, 3) = d3.departid
WHERE LEN(D.departid) = 12
AND cte1.shiftid = '01'
AND cte2.shiftid = '02'
AND cte3.shiftid = '03'
AND cte4.shiftid = '04'
AND cte5.shiftid IS NULL
昵称90天可改一次 2014-04-15
- 打赏
- 举报
真是辛苦你了发了一堆 还不如发建表、插入数据语句 不是不会而是闲建表插入数据麻烦
