请教大家一下

qq_26006573 2017-11-17 03:36:22
if OBJECT_ID(N'tempdb..#Xfs') is not null
drop table #Xfs
go
create table #Xfs --消费表
(
xf_jr int,--消费金额
xf_bm varchar(30),--部门
xf_user varchar(30),--消费人员
xf_rq datetime,--消费日期
)
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-16 20:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('200','李四','2017-11-15 21:00:00','人事部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:00:00','生产部')
GO

insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:10:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:11:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 13:10:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 23:10:00','生产部')
GO

declare @start_date datetime,
@end_date datetime,
@start_date1 datetime,
@end_date1 datetime,
@start_date2 datetime,
@end_date2 datetime

set @start_date='2017-11-15 20:00:00'
set @end_date='2017-11-16 22:00:00'

set @start_date1='2017-11-15 12:00:00'
set @end_date1='2017-11-16 14:00:00'

set @start_date2='2017-11-15 23:00:00'
set @end_date2='2017-11-16 23:55:00'

;with cte
as
(

select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date, 111) and CONVERT(varchar(100), @end_date, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date, 108) and CONVERT(varchar(100), @end_date, 108))
)
,
cte1
as
(
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date1, 111) and CONVERT(varchar(100), @end_date1, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date1, 108) and CONVERT(varchar(100), @end_date1, 108))
)
,
cte2
as
(
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date2, 111) and CONVERT(varchar(100), @end_date2, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date2, 108) and CONVERT(varchar(100), @end_date2, 108))
)
select c1.xf_user,c1.xf_bm,
'早餐' as zc,
sum(case when c1.rn=1 then c1.xf_jr else 0 end) as amount_once,
sum(case when c1.rn=1 then 1 else 0 end) as count_once,
sum(case when c1.rn>1 then c1.xf_jr else 0 end) as amount_oncemore,
sum(case when c1.rn>1 then 1 else 0 end) as count_oncemore,
'中餐' as zc,
sum(case when c2.rn=1 then c2.xf_jr else 0 end) as amount_once,
sum(case when c2.rn=1 then 1 else 0 end) as count_once,
sum(case when c2.rn>1 then c2.xf_jr else 0 end) as amount_oncemore,
sum(case when c2.rn>1 then 1 else 0 end) as count_oncemore,
'晚餐' as zc,
sum(case when c3.rn=1 then c3.xf_jr else 0 end) as amount_once,
sum(case when c3.rn=1 then 1 else 0 end) as count_once,
sum(case when c3.rn>1 then c3.xf_jr else 0 end) as amount_oncemore,
sum(case when c3.rn>1 then 1 else 0 end) as count_oncemore
from


group by c1.xf_user,c1.xf_bm


请教下 form 哪里怎么写，才能 一次吧 cte， cte1 cte2 里的内容一起显示出来。我分3个查询是可以，我想合在一起显示
...全文
146 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
RINK_1 2017-11-17
  • 打赏
  • 举报
 回复 


select coalesce(A.xf_user,B.xf_user,C.xf_user) AS xf_user,
coalesce(A.xf_bm,B.xf_bm,C.xf_bm) AS xf_bm,
A.zc,A.amount_once,A.count_once,A.amount_oncemore,A.count_oncemore,
B.zc,B.amount_once,B.count_once,B.amount_oncemore,B.count_oncemore,
C.zc,C.amount_once,C.count_once,C.amount_oncemore,C.count_oncemore
from
(select c1.xf_user,c1.xf_bm,
'早餐' as zc,
sum(case when c1.rn=1 then c1.xf_jr else 0 end) as amount_once,
sum(case when c1.rn=1 then 1 else 0 end) as count_once,
sum(case when c1.rn>1 then c1.xf_jr else 0 end) as amount_oncemore,
sum(case when c1.rn>1 then 1 else 0 end) as count_oncemore
from cte1 c1
group by c1.xf_user,c1.xf_bm) as A
full join 
(select c1.xf_user,c1.xf_bm,
'中餐' as zc,
sum(case when c2.rn=1 then c2.xf_jr else 0 end) as amount_once,
sum(case when c2.rn=1 then 1 else 0 end) as count_once,
sum(case when c2.rn>1 then c2.xf_jr else 0 end) as amount_oncemore,
sum(case when c2.rn>1 then 1 else 0 end) as count_oncemore
from cte_2 c1
group by c1.xf_user,c1.xf_bm ) as B ON A.xf_user=B.xf_user AND A.xf_bm=B.xf_bm
FULL JOIN
(select c1.xf_user,c1.xf_bm,
'晚餐' as zc,
sum(case when c3.rn=1 then c3.xf_jr else 0 end) as amount_once,
sum(case when c3.rn=1 then 1 else 0 end) as count_once,
sum(case when c3.rn>1 then c3.xf_jr else 0 end) as amount_oncemore,
sum(case when c3.rn>1 then 1 else 0 end) as count_oncemore
from cte_3 c1
group by c1.xf_user,c1.xf_bm ) as C ON ISNULL(A.xf_user,B.xf_user)=C.xf_user AND ISNULL(A.xf_bm,B.xf_bm)=C.xf_bm
二月十六 2017-11-17
  • 打赏
  • 举报
 回复 
if OBJECT_ID(N'tempdb..#Xfs') is not null
drop table #Xfs
go
create table #Xfs --消费表
(
   xf_jr int,--消费金额
   xf_bm  varchar(30),--部门
   xf_user varchar(30),--消费人员
   xf_rq datetime,--消费日期
)
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-16 20:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('200','李四','2017-11-15 21:00:00','人事部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-16 13:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:10:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:11:00','生产部')
GO
 insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 13:10:00','生产部')
GO
  insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 23:10:00','生产部')
GO
  
declare @start_date datetime, 
        @end_date datetime,
        @start_date1 datetime, 
        @end_date1 datetime,
        @start_date2 datetime, 
        @end_date2 datetime
   
set @start_date='2017-11-15 20:00:00'
set @end_date='2017-11-16 22:00:00'
   
set @start_date1='2017-11-15 12:00:00'
set @end_date1='2017-11-16 14:00:00'
  
set @start_date2='2017-11-15 23:00:00'
set @end_date2='2017-11-16 23:55:00'
  
;with cte
as
(
   
select *,
ROW_NUMBER() over (partition by xf_user order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date, 111)  and  CONVERT(varchar(100), @end_date, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date, 108)  and  CONVERT(varchar(100), @end_date, 108))
)
,
cte1
as 
(
select *,
ROW_NUMBER() over (partition by xf_user order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date1, 111)  and  CONVERT(varchar(100), @end_date1, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date1, 108)  and  CONVERT(varchar(100), @end_date1, 108))
)
,
cte2
as 
(
select *,
ROW_NUMBER() over (partition by xf_user order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date2, 111)  and  CONVERT(varchar(100), @end_date2, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date2, 108)  and  CONVERT(varchar(100), @end_date2, 108))
)
SELECT c1.xf_user,c1.xf_bm,
'早餐' AS zc,
SUM(CASE WHEN c1.rn=1 THEN c1.xf_jr ELSE 0 END) AS amount_once,
SUM(CASE WHEN c1.rn=1 THEN 1 ELSE 0 END) AS count_once,
SUM(CASE WHEN c1.rn>1 THEN c1.xf_jr ELSE 0 END) AS amount_oncemore,
SUM(CASE WHEN c1.rn>1 THEN 1 ELSE 0 END) AS count_oncemore,
'中餐' AS zc,
SUM(CASE WHEN c2.rn=1 THEN c2.xf_jr ELSE 0 END) AS amount_once,
SUM(CASE WHEN c2.rn=1 THEN 1 ELSE 0 END) AS count_once,
SUM(CASE WHEN c2.rn>1 THEN c2.xf_jr ELSE 0 END) AS amount_oncemore,
SUM(CASE WHEN c2.rn>1 THEN 1 ELSE 0 END) AS count_oncemore,
'晚餐' AS zc,
SUM(CASE WHEN c3.rn=1 THEN c3.xf_jr ELSE 0 END) AS amount_once,
SUM(CASE WHEN c3.rn=1 THEN 1 ELSE 0 END) AS count_once,
SUM(CASE WHEN c3.rn>1 THEN c3.xf_jr ELSE 0 END) AS amount_oncemore,
SUM(CASE WHEN c3.rn>1 THEN 1 ELSE 0 END) AS count_oncemore
FROM 
cte c1 FULL JOIN cte1 c2 ON c2.xf_user = c1.xf_user AND c2.rn = c1.rn  FULL JOIN cte2 c3 ON c3.xf_user = c1.xf_user AND c3.rn = c1.rn 
  
group by c1.xf_user,c1.xf_bm
二月十六 2017-11-17
  • 打赏
  • 举报
 回复
没太明白你的意思，partition by加日期是算什么?最后有没有显示日期 
if OBJECT_ID(N'tempdb..#Xfs') is not null
drop table #Xfs
go
create table #Xfs --消费表
(
   xf_jr int,--消费金额
   xf_bm  varchar(30),--部门
   xf_user varchar(30),--消费人员
   xf_rq datetime,--消费日期
)
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-16 20:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('200','李四','2017-11-15 21:00:00','人事部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-16 13:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:10:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:11:00','生产部')
GO
 insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 13:10:00','生产部')
GO
  insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 23:10:00','生产部')
GO
  
declare @start_date datetime, 
        @end_date datetime,
        @start_date1 datetime, 
        @end_date1 datetime,
        @start_date2 datetime, 
        @end_date2 datetime
   
set @start_date='2017-11-15 20:00:00'
set @end_date='2017-11-16 22:00:00'
   
set @start_date1='2017-11-15 12:00:00'
set @end_date1='2017-11-16 14:00:00'
  
set @start_date2='2017-11-15 23:00:00'
set @end_date2='2017-11-16 23:55:00'
  
;with cte
as
(
   
select *,
ROW_NUMBER() over (partition by xf_user order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date, 111)  and  CONVERT(varchar(100), @end_date, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date, 108)  and  CONVERT(varchar(100), @end_date, 108))
)
,
cte1
as 
(
select *,
ROW_NUMBER() over (partition by xf_user order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date1, 111)  and  CONVERT(varchar(100), @end_date1, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date1, 108)  and  CONVERT(varchar(100), @end_date1, 108))
)
,
cte2
as 
(
select *,
ROW_NUMBER() over (partition by xf_user order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date2, 111)  and  CONVERT(varchar(100), @end_date2, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date2, 108)  and  CONVERT(varchar(100), @end_date2, 108))
)
SELECT c1.xf_user,c1.xf_bm,
'早餐' AS zc,
SUM(CASE WHEN c1.rn=1 THEN c1.xf_jr ELSE 0 END) AS amount_once,
SUM(CASE WHEN c1.rn=1 THEN 1 ELSE 0 END) AS count_once,
SUM(CASE WHEN c1.rn>1 THEN c1.xf_jr ELSE 0 END) AS amount_oncemore,
SUM(CASE WHEN c1.rn>1 THEN 1 ELSE 0 END) AS count_oncemore,
'中餐' AS zc,
SUM(CASE WHEN c2.rn=1 THEN c2.xf_jr ELSE 0 END) AS amount_once,
SUM(CASE WHEN c2.rn=1 THEN 1 ELSE 0 END) AS count_once,
SUM(CASE WHEN c2.rn>1 THEN c2.xf_jr ELSE 0 END) AS amount_oncemore,
SUM(CASE WHEN c2.rn>1 THEN 1 ELSE 0 END) AS count_oncemore,
'晚餐' AS zc,
SUM(CASE WHEN c3.rn=1 THEN c3.xf_jr ELSE 0 END) AS amount_once,
SUM(CASE WHEN c3.rn=1 THEN 1 ELSE 0 END) AS count_once,
SUM(CASE WHEN c3.rn>1 THEN c3.xf_jr ELSE 0 END) AS amount_oncemore,
SUM(CASE WHEN c3.rn>1 THEN 1 ELSE 0 END) AS count_oncemore
FROM 
cte c1 FULL JOIN cte1 c2 ON c2.xf_user = c1.xf_user AND c2.rn = c1.rn  FULL JOIN cte2 c3 ON c3.xf_user = c1.xf_user AND c3.rn = c1.rn 
  
group by c1.xf_user,c1.xf_bm
qq_26006573 2017-11-17
  • 打赏
  • 举报
 回复
引用 3 楼 sinat_28984567 的回复:
这样？ 
if OBJECT_ID(N'tempdb..#Xfs') is not null
drop table #Xfs
go
create table #Xfs --消费表
(
   xf_jr int,--消费金额
   xf_bm  varchar(30),--部门
   xf_user varchar(30),--消费人员
   xf_rq datetime,--消费日期
)
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-16 20:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('200','李四','2017-11-15 21:00:00','人事部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:00:00','生产部')
GO
  
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:10:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:11:00','生产部')
GO
 insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 13:10:00','生产部')
GO
  insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 23:10:00','生产部')
GO
 
declare @start_date datetime, 
        @end_date datetime,
        @start_date1 datetime, 
        @end_date1 datetime,
        @start_date2 datetime, 
        @end_date2 datetime
  
set @start_date='2017-11-15 20:00:00'
set @end_date='2017-11-16 22:00:00'
  
set @start_date1='2017-11-15 12:00:00'
set @end_date1='2017-11-16 14:00:00'
 
set @start_date2='2017-11-15 23:00:00'
set @end_date2='2017-11-16 23:55:00'
 
;with cte
as
(
  
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date, 111)  and  CONVERT(varchar(100), @end_date, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date, 108)  and  CONVERT(varchar(100), @end_date, 108))
)
,
cte1
as 
(
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date1, 111)  and  CONVERT(varchar(100), @end_date1, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date1, 108)  and  CONVERT(varchar(100), @end_date1, 108))
)
,
cte2
as 
(
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date2, 111)  and  CONVERT(varchar(100), @end_date2, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date2, 108)  and  CONVERT(varchar(100), @end_date2, 108))
)
select c1.xf_user,c1.xf_bm,
'早餐' as zc,
sum(case when c1.rn=1 then c1.xf_jr else 0 end) as amount_once,
sum(case when c1.rn=1 then 1 else 0 end) as count_once,
sum(case when c1.rn>1 then c1.xf_jr else 0 end) as amount_oncemore,
sum(case when c1.rn>1 then 1 else 0 end) as count_oncemore,
'中餐' as zc,
sum(case when c2.rn=1 then c2.xf_jr else 0 end) as amount_once,
sum(case when c2.rn=1 then 1 else 0 end) as count_once,
sum(case when c2.rn>1 then c2.xf_jr else 0 end) as amount_oncemore,
sum(case when c2.rn>1 then 1 else 0 end) as count_oncemore,
'晚餐' as zc,
sum(case when c3.rn=1 then c3.xf_jr else 0 end) as amount_once,
sum(case when c3.rn=1 then 1 else 0 end) as count_once,
sum(case when c3.rn>1 then c3.xf_jr else 0 end) as amount_oncemore,
sum(case when c3.rn>1 then 1 else 0 end) as count_oncemore
from 
cte c1 FULL JOIN cte1 c2 ON c2.xf_user = c1.xf_user FULL JOIN cte2 c3 ON c3.xf_user = c1.xf_user
 
group by c1.xf_user,c1.xf_bm
这样我又增加了一笔数据 insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-16 13:00:00','生产部') GO 结果变成张三中午 消费400了，应该是100，算出来的不对了。。。
二月十六 2017-11-17
  • 打赏
  • 举报
 回复
这样？ 
if OBJECT_ID(N'tempdb..#Xfs') is not null
drop table #Xfs
go
create table #Xfs --消费表
(
   xf_jr int,--消费金额
   xf_bm  varchar(30),--部门
   xf_user varchar(30),--消费人员
   xf_rq datetime,--消费日期
)
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-16 20:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('200','李四','2017-11-15 21:00:00','人事部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:00:00','生产部')
GO
  
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:10:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:11:00','生产部')
GO
 insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 13:10:00','生产部')
GO
  insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 23:10:00','生产部')
GO
 
declare @start_date datetime, 
        @end_date datetime,
        @start_date1 datetime, 
        @end_date1 datetime,
        @start_date2 datetime, 
        @end_date2 datetime
  
set @start_date='2017-11-15 20:00:00'
set @end_date='2017-11-16 22:00:00'
  
set @start_date1='2017-11-15 12:00:00'
set @end_date1='2017-11-16 14:00:00'
 
set @start_date2='2017-11-15 23:00:00'
set @end_date2='2017-11-16 23:55:00'
 
;with cte
as
(
  
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date, 111)  and  CONVERT(varchar(100), @end_date, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date, 108)  and  CONVERT(varchar(100), @end_date, 108))
)
,
cte1
as 
(
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date1, 111)  and  CONVERT(varchar(100), @end_date1, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date1, 108)  and  CONVERT(varchar(100), @end_date1, 108))
)
,
cte2
as 
(
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date2, 111)  and  CONVERT(varchar(100), @end_date2, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date2, 108)  and  CONVERT(varchar(100), @end_date2, 108))
)
select c1.xf_user,c1.xf_bm,
'早餐' as zc,
sum(case when c1.rn=1 then c1.xf_jr else 0 end) as amount_once,
sum(case when c1.rn=1 then 1 else 0 end) as count_once,
sum(case when c1.rn>1 then c1.xf_jr else 0 end) as amount_oncemore,
sum(case when c1.rn>1 then 1 else 0 end) as count_oncemore,
'中餐' as zc,
sum(case when c2.rn=1 then c2.xf_jr else 0 end) as amount_once,
sum(case when c2.rn=1 then 1 else 0 end) as count_once,
sum(case when c2.rn>1 then c2.xf_jr else 0 end) as amount_oncemore,
sum(case when c2.rn>1 then 1 else 0 end) as count_oncemore,
'晚餐' as zc,
sum(case when c3.rn=1 then c3.xf_jr else 0 end) as amount_once,
sum(case when c3.rn=1 then 1 else 0 end) as count_once,
sum(case when c3.rn>1 then c3.xf_jr else 0 end) as amount_oncemore,
sum(case when c3.rn>1 then 1 else 0 end) as count_oncemore
from 
cte c1 FULL JOIN cte1 c2 ON c2.xf_user = c1.xf_user FULL JOIN cte2 c3 ON c3.xf_user = c1.xf_user
 
group by c1.xf_user,c1.xf_bm
qq_26006573 2017-11-17
  • 打赏
  • 举报
 回复
引用 1 楼 sinat_28984567 的回复:
if OBJECT_ID(N'tempdb..#Xfs') is not null
drop table #Xfs
go
create table #Xfs --消费表
(
   xf_jr int,--消费金额
   xf_bm  varchar(30),--部门
   xf_user varchar(30),--消费人员
   xf_rq datetime,--消费日期
)
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-16 20:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('200','李四','2017-11-15 21:00:00','人事部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:00:00','生产部')
GO
 
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:10:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:11:00','生产部')
GO
 insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 13:10:00','生产部')
GO
  insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 23:10:00','生产部')
GO

declare @start_date datetime, 
        @end_date datetime,
        @start_date1 datetime, 
        @end_date1 datetime,
        @start_date2 datetime, 
        @end_date2 datetime
 
set @start_date='2017-11-15 20:00:00'
set @end_date='2017-11-16 22:00:00'
 
set @start_date1='2017-11-15 12:00:00'
set @end_date1='2017-11-16 14:00:00'

set @start_date2='2017-11-15 23:00:00'
set @end_date2='2017-11-16 23:55:00'

;with cte
as
(
 
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date, 111)  and  CONVERT(varchar(100), @end_date, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date, 108)  and  CONVERT(varchar(100), @end_date, 108))
)
,
cte1
as 
(
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date1, 111)  and  CONVERT(varchar(100), @end_date1, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date1, 108)  and  CONVERT(varchar(100), @end_date1, 108))
)
,
cte2
as 
(
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date2, 111)  and  CONVERT(varchar(100), @end_date2, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date2, 108)  and  CONVERT(varchar(100), @end_date2, 108))
)
select c1.xf_user,c1.xf_bm,
'早餐' as zc,
sum(case when c1.rn=1 then c1.xf_jr else 0 end) as amount_once,
sum(case when c1.rn=1 then 1 else 0 end) as count_once,
sum(case when c1.rn>1 then c1.xf_jr else 0 end) as amount_oncemore,
sum(case when c1.rn>1 then 1 else 0 end) as count_oncemore,
'中餐' as zc,
sum(case when c2.rn=1 then c2.xf_jr else 0 end) as amount_once,
sum(case when c2.rn=1 then 1 else 0 end) as count_once,
sum(case when c2.rn>1 then c2.xf_jr else 0 end) as amount_oncemore,
sum(case when c2.rn>1 then 1 else 0 end) as count_oncemore,
'晚餐' as zc,
sum(case when c3.rn=1 then c3.xf_jr else 0 end) as amount_once,
sum(case when c3.rn=1 then 1 else 0 end) as count_once,
sum(case when c3.rn>1 then c3.xf_jr else 0 end) as amount_oncemore,
sum(case when c3.rn>1 then 1 else 0 end) as count_oncemore
from 
cte c1 JOIN cte1 c2 ON c2.xf_user = c1.xf_user JOIN cte2 c3 ON c3.xf_user = c1.xf_user

group by c1.xf_user,c1.xf_bm
可是张三这个人不见了，只显示了李四1个人，我要看每个人的，早，中 晚，谢谢大版
二月十六 2017-11-17
  • 打赏
  • 举报
 回复 
if OBJECT_ID(N'tempdb..#Xfs') is not null
drop table #Xfs
go
create table #Xfs --消费表
(
   xf_jr int,--消费金额
   xf_bm  varchar(30),--部门
   xf_user varchar(30),--消费人员
   xf_rq datetime,--消费日期
)
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-16 20:00:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('200','李四','2017-11-15 21:00:00','人事部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:00:00','生产部')
GO
 
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:10:00','生产部')
GO
insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','张三','2017-11-15 21:11:00','生产部')
GO
 insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 13:10:00','生产部')
GO
  insert into #Xfs(xf_jr,xf_user,xf_rq,xf_bm) values('100','李四','2017-11-15 23:10:00','生产部')
GO

declare @start_date datetime, 
        @end_date datetime,
        @start_date1 datetime, 
        @end_date1 datetime,
        @start_date2 datetime, 
        @end_date2 datetime
 
set @start_date='2017-11-15 20:00:00'
set @end_date='2017-11-16 22:00:00'
 
set @start_date1='2017-11-15 12:00:00'
set @end_date1='2017-11-16 14:00:00'

set @start_date2='2017-11-15 23:00:00'
set @end_date2='2017-11-16 23:55:00'

;with cte
as
(
 
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date, 111)  and  CONVERT(varchar(100), @end_date, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date, 108)  and  CONVERT(varchar(100), @end_date, 108))
)
,
cte1
as 
(
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date1, 111)  and  CONVERT(varchar(100), @end_date1, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date1, 108)  and  CONVERT(varchar(100), @end_date1, 108))
)
,
cte2
as 
(
select *,
ROW_NUMBER() over (partition by xf_user,convert(varchar(10),xf_rq,23) order by xf_rq) as rn
from #Xfs
where  (CONVERT(varchar(100), xf_rq, 111) between CONVERT(varchar(100), @start_date2, 111)  and  CONVERT(varchar(100), @end_date2, 111))
and (CONVERT(varchar(100), xf_rq, 108) between CONVERT(varchar(100), @start_date2, 108)  and  CONVERT(varchar(100), @end_date2, 108))
)
select c1.xf_user,c1.xf_bm,
'早餐' as zc,
sum(case when c1.rn=1 then c1.xf_jr else 0 end) as amount_once,
sum(case when c1.rn=1 then 1 else 0 end) as count_once,
sum(case when c1.rn>1 then c1.xf_jr else 0 end) as amount_oncemore,
sum(case when c1.rn>1 then 1 else 0 end) as count_oncemore,
'中餐' as zc,
sum(case when c2.rn=1 then c2.xf_jr else 0 end) as amount_once,
sum(case when c2.rn=1 then 1 else 0 end) as count_once,
sum(case when c2.rn>1 then c2.xf_jr else 0 end) as amount_oncemore,
sum(case when c2.rn>1 then 1 else 0 end) as count_oncemore,
'晚餐' as zc,
sum(case when c3.rn=1 then c3.xf_jr else 0 end) as amount_once,
sum(case when c3.rn=1 then 1 else 0 end) as count_once,
sum(case when c3.rn>1 then c3.xf_jr else 0 end) as amount_oncemore,
sum(case when c3.rn>1 then 1 else 0 end) as count_oncemore
from 
cte c1 JOIN cte1 c2 ON c2.xf_user = c1.xf_user JOIN cte2 c3 ON c3.xf_user = c1.xf_user

group by c1.xf_user,c1.xf_bm
关于脉振高频注入的一些问题
各位大佬们，关于脉振高频注入的一些问题，想请教大家一下： 在基于id=0的矢量控制中，SVPWM能够同时合成多个旋转矢量吗？SVPWM一般是依据α-β轴下的电压（假设使用七段法）来计算目标输出矢量相邻的基础矢量的作用...
向大家请教一个问题
以下问题来自我们的真实需求，并加上在一个氛围尚可的 QQ 群讨论的补充，我们没有得出比较好的答案，特向大家请教。
请教一下:visual c++中的画笔和画刷有什么区别呢??
我想请教大家一下:visual c++中的画笔和画刷有什么区别呢??谢谢啊！！
谁知道people是怎么回事？
今天去面试一家公司，他们需要作peoplesoft的...不解，但是没好意思问，因为他问我peoplesoft听说过没，我说听说过，所以现在比较郁闷，再过两天可能要见他们的技术再面试 ，小弟想请教大家一下。有没有谁熟悉peoplesof
学习区块链从哪一方面开始
学习区块链前端技术应该从哪一方面入手，React和区块链有什么关系！想请教大家一下学习的方向。
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章