22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] nvarchar(22),[当日金额] int,[本周金额] int,[本月金额] INT,[分组] int)
Insert #T
select N'张三',100,300,3000,1 union all
select N'李四',100,200,1000,1 union all
select N'王五',50,150,800,2 union all
select N'刘六',500,700,1700,2
Go
--测试数据结束
SELECT 姓名,当日金额,本周金额,本月金额
FROM ( SELECT 姓名 ,
SUM(当日金额) AS 当日金额 ,
SUM(本周金额) AS 本周金额 ,
SUM(本月金额) AS 本月金额 ,
分组
FROM #T
GROUP BY 姓名 ,
分组
UNION ALL
SELECT RTRIM(分组) +'组',
SUM(当日金额) AS 当日金额 ,
SUM(本周金额) AS 本周金额 ,
SUM(本月金额) AS 本月金额 ,
分组
FROM #T
GROUP BY RTRIM(分组) +'组' ,
分组
UNION ALL
SELECT '总计' ,
SUM(当日金额) AS 当日金额 ,
SUM(本周金额) AS 本周金额 ,
SUM(本月金额) AS 本月金额 ,
1000
FROM #T
) t
ORDER BY t.分组 ,
姓名 DESC
select case when GROUPING(user_id)=1 and GROUPING([group])=0 then '小组'+CAST([GROUP] as varchar)
when GROUPING(user_id)=1 and GROUPING([group])=1 then '合计'
else MAX(user_name)
end as name,
sum(case when DATEDIFF(DAY,add_time,GETDATE())=0 then money else 0 end) as total_daily,
sum(case when DATEDIFF(week,add_time,GETDATE())=0 then money else 0 end) as total_weekly,
sum(case when DATEDIFF(month,add_time,GETDATE())=0 then money else 0 end) as total_monthly
from #order_money
group by user_id,[GROUP] with cube
having not (GROUPing(user_id)=0 and GROUPING([GROUP])=1)
order by GROUPING([GROUP]),[group],GROUPING(user_id)
--测试数据
IF OBJECT_ID('tempdb..#order_money') IS NOT NULL
DROP TABLE #order_money
create table #order_money
(
id int identity(1,1) primary key not null,
user_id int,
user_name nvarchar(20),
[GROUP] int,
type varchar(10),
money float,
add_time datetime
)
insert into #order_money values(1,'张三',1,'类型1',30,'2017-11-2')
insert into #order_money values(1,'张三',1,'类型1',70,'2017-11-2')
insert into #order_money values(1,'张三',1,'类型1',100,'2017-11-3')
insert into #order_money values(1,'张三',1,'类型1',100,'2017-11-3')
insert into #order_money values(1,'张三',1,'类型1',700,'2017-11-9')
insert into #order_money values(1,'张三',1,'类型1',2000,'2017-11-26')
insert into #order_money values(2,'李四',1,'类型1',60,'2017-11-2')
insert into #order_money values(2,'李四',1,'类型1',30,'2017-11-2')
insert into #order_money values(2,'李四',1,'类型1',10,'2017-11-2')
insert into #order_money values(2,'李四',1,'类型1',50,'2017-11-3')
insert into #order_money values(2,'李四',1,'类型1',50,'2017-11-3')
insert into #order_money values(2,'李四',1,'类型1',300,'2017-11-25')
insert into #order_money values(2,'李四',1,'类型1',500,'2017-11-23')
insert into #order_money values(3,'王五',2,'类型1',50,'2017-11-2')
insert into #order_money values(3,'王五',2,'类型1',100,'2017-11-3')
insert into #order_money values(3,'王五',2,'类型1',150,'2017-11-27')
insert into #order_money values(3,'王五',2,'类型1',500,'2017-11-29')
insert into #order_money values(4,'刘六',2,'类型1',200,'2017-11-2')
insert into #order_money values(4,'刘六',2,'类型1',300,'2017-11-2')
insert into #order_money values(4,'刘六',2,'类型1',200,'2017-11-3')
insert into #order_money values(4,'刘六',2,'类型1',1000,'2017-11-29')
--测试数据结束
;WITH cte AS (
SELECT DISTINCT USER_NAME,
[GROUP],
SUM([MONEY]) OVER(PARTITION BY USER_NAME, [GROUP]) AS 本月金额
FROM #order_money
),
cte2 AS (
SELECT DISTINCT USER_NAME,
[GROUP],
SUM([MONEY]) OVER(PARTITION BY USER_NAME, [GROUP]) AS 本周金额
FROM #order_money
WHERE add_time BETWEEN CONVERT(
VARCHAR(100),
DATEADD(DAY, -(DATEPART(weekday, GETDATE()) -1), GETDATE()),
23
) AND CONVERT(
VARCHAR(100),
DATEADD(DAY, -(DATEPART(weekday, GETDATE()) -7), GETDATE()),
23
)
),
cte3 AS (
SELECT DISTINCT USER_NAME,
[GROUP],
SUM([MONEY]) OVER(PARTITION BY USER_NAME, [GROUP]) AS 当日金额
FROM #order_money
WHERE convert(varchar(100),add_time,23)=convert(varchar(100),GETDATE(),23)
),
cte4 AS(
SELECT '小组'+cast(a.[GROUP] AS VARCHAR(20)) as '姓名',a.[GROUP],SUM(a.本月金额) as 本月金额,SUM(b.本周金额) as 本周金额,SUM(c.当日金额) as 当日金额 FROM cte a
INNER JOIN cte2 b ON a.USER_NAME=b.USER_NAME AND a.[group]=b.[group]
INNER JOIN cte3 c ON a.USER_NAME=c.USER_NAME AND a.[group]=c.[group]
GROUP BY a.[GROUP]
),
cte5 AS (
SELECT '总计' as user_name,999 as [group],SUM(本月金额) as 本月金额,sum(本周金额) as 本周金额,sum(当日金额) as 当日金额 FROM cte4
),
cte6 AS (
SELECT a.user_name AS '姓名',a.[group],a.本月金额,b.本周金额,c.当日金额 FROM cte a
INNER JOIN cte2 b ON a.USER_NAME=b.USER_NAME AND a.[group]=b.[group]
INNER JOIN cte3 c ON a.USER_NAME=c.USER_NAME AND a.[group]=c.[group]
),
cte7 AS (
SELECT * FROM cte6
UNION ALL
SELECT * FROM cte5
UNION ALL
SELECT * FROM cte4
)
SELECT 姓名,当日金额,本周金额,本月金额
from cte7
ORDER BY [GROUP],本月金额
姓名 当日金额 本周金额 本月金额
------------------------ ---------------------- ---------------------- ----------------------
李四 100 200 1000
张三 100 300 3000
小组1 200 500 4000
王五 50 150 800
刘六 500 700 1700
小组2 550 850 2500
总计 750 1350 6500
--测试数据
IF OBJECT_ID('tempdb..#order_money') IS NOT NULL
DROP TABLE #order_money
create table #order_money
(
id int identity(1,1) primary key not null,
user_id int,
user_name nvarchar(20),
[GROUP] int,
type varchar(10),
money float,
add_time datetime
)
insert into #order_money values(1,'张三',1,'类型1',30,'2017-11-2')
insert into #order_money values(1,'张三',1,'类型1',70,'2017-11-2')
insert into #order_money values(1,'张三',1,'类型1',100,'2017-11-3')
insert into #order_money values(1,'张三',1,'类型1',100,'2017-11-3')
insert into #order_money values(1,'张三',1,'类型1',700,'2017-11-9')
insert into #order_money values(1,'张三',1,'类型1',2000,'2017-11-26')
insert into #order_money values(2,'李四',1,'类型1',60,'2017-11-2')
insert into #order_money values(2,'李四',1,'类型1',30,'2017-11-2')
insert into #order_money values(2,'李四',1,'类型1',10,'2017-11-2')
insert into #order_money values(2,'李四',1,'类型1',50,'2017-11-3')
insert into #order_money values(2,'李四',1,'类型1',50,'2017-11-3')
insert into #order_money values(2,'李四',1,'类型1',300,'2017-11-25')
insert into #order_money values(2,'李四',1,'类型1',500,'2017-11-23')
insert into #order_money values(3,'王五',2,'类型1',50,'2017-11-2')
insert into #order_money values(3,'王五',2,'类型1',100,'2017-11-3')
insert into #order_money values(3,'王五',2,'类型1',150,'2017-11-27')
insert into #order_money values(3,'王五',2,'类型1',500,'2017-11-29')
insert into #order_money values(4,'刘六',2,'类型1',200,'2017-11-2')
insert into #order_money values(4,'刘六',2,'类型1',300,'2017-11-2')
insert into #order_money values(4,'刘六',2,'类型1',200,'2017-11-3')
insert into #order_money values(4,'刘六',2,'类型1',1000,'2017-11-29')
--测试数据结束
;WITH cte AS (
SELECT DISTINCT USER_NAME,
[GROUP],
SUM([MONEY]) OVER(PARTITION BY USER_NAME, [GROUP]) AS 本月金额
FROM #order_money
),
cte2 AS (
SELECT DISTINCT USER_NAME,
[GROUP],
SUM([MONEY]) OVER(PARTITION BY USER_NAME, [GROUP]) AS 本周金额
FROM #order_money
WHERE add_time BETWEEN CONVERT(
VARCHAR(100),
DATEADD(DAY, -(DATEPART(weekday, GETDATE()) -1), GETDATE()),
23
) AND CONVERT(
VARCHAR(100),
DATEADD(DAY, -(DATEPART(weekday, GETDATE()) -7), GETDATE()),
23
)
),
cte3 AS (
SELECT DISTINCT USER_NAME,
[GROUP],
SUM([MONEY]) OVER(PARTITION BY USER_NAME, [GROUP]) AS 当日金额
FROM #order_money
WHERE convert(varchar(100),add_time,23)=convert(varchar(100),GETDATE(),23)
),
cte4 AS(
SELECT '小组'+cast(a.[GROUP] AS VARCHAR(20)) as '姓名',SUM(a.本月金额) as 本月金额,SUM(b.本周金额) as 本周金额,SUM(c.当日金额) as 当日金额 FROM cte a
INNER JOIN cte2 b ON a.USER_NAME=b.USER_NAME AND a.[group]=b.[group]
INNER JOIN cte3 c ON a.USER_NAME=c.USER_NAME AND a.[group]=c.[group]
GROUP BY a.[GROUP]
),
cte5 AS (
SELECT '总计' as user_name,SUM(本月金额) as 本月金额,sum(本周金额) as 本周金额,sum(当日金额) as 当日金额 FROM cte4
),
cte6 AS (
SELECT a.user_name AS '姓名',a.本月金额,b.本周金额,c.当日金额 FROM cte a
INNER JOIN cte2 b ON a.USER_NAME=b.USER_NAME AND a.[group]=b.[group]
INNER JOIN cte3 c ON a.USER_NAME=c.USER_NAME AND a.[group]=c.[group]
)
SELECT * FROM cte6
UNION ALL
SELECT * FROM cte5
UNION ALL
SELECT * FROM cte4
[/quote]
非常感谢,输出顺序可以调整一下吗?每个小组的成员,然后小组汇总,第二个小组成员,第二个小组汇总.....
姓名 当日金额 本周金额 本月金额
张三 100 300 3000
李四 100 200 1000
小组1 200 500 4000
王五 50 150 800
刘六 500 700 1700
小组2 550 850 2500
总计 750 1350 6500
--测试数据
IF OBJECT_ID('tempdb..#order_money') IS NOT NULL
DROP TABLE #order_money
create table #order_money
(
id int identity(1,1) primary key not null,
user_id int,
user_name nvarchar(20),
[GROUP] int,
type varchar(10),
money float,
add_time datetime
)
insert into #order_money values(1,'张三',1,'类型1',30,'2017-11-2')
insert into #order_money values(1,'张三',1,'类型1',70,'2017-11-2')
insert into #order_money values(1,'张三',1,'类型1',100,'2017-11-3')
insert into #order_money values(1,'张三',1,'类型1',100,'2017-11-3')
insert into #order_money values(1,'张三',1,'类型1',700,'2017-11-9')
insert into #order_money values(1,'张三',1,'类型1',2000,'2017-11-26')
insert into #order_money values(2,'李四',1,'类型1',60,'2017-11-2')
insert into #order_money values(2,'李四',1,'类型1',30,'2017-11-2')
insert into #order_money values(2,'李四',1,'类型1',10,'2017-11-2')
insert into #order_money values(2,'李四',1,'类型1',50,'2017-11-3')
insert into #order_money values(2,'李四',1,'类型1',50,'2017-11-3')
insert into #order_money values(2,'李四',1,'类型1',300,'2017-11-25')
insert into #order_money values(2,'李四',1,'类型1',500,'2017-11-23')
insert into #order_money values(3,'王五',2,'类型1',50,'2017-11-2')
insert into #order_money values(3,'王五',2,'类型1',100,'2017-11-3')
insert into #order_money values(3,'王五',2,'类型1',150,'2017-11-27')
insert into #order_money values(3,'王五',2,'类型1',500,'2017-11-29')
insert into #order_money values(4,'刘六',2,'类型1',200,'2017-11-2')
insert into #order_money values(4,'刘六',2,'类型1',300,'2017-11-2')
insert into #order_money values(4,'刘六',2,'类型1',200,'2017-11-3')
insert into #order_money values(4,'刘六',2,'类型1',1000,'2017-11-29')
--测试数据结束
;WITH cte AS (
SELECT DISTINCT USER_NAME,
[GROUP],
SUM([MONEY]) OVER(PARTITION BY USER_NAME, [GROUP]) AS 本月金额
FROM #order_money
),
cte2 AS (
SELECT DISTINCT USER_NAME,
[GROUP],
SUM([MONEY]) OVER(PARTITION BY USER_NAME, [GROUP]) AS 本周金额
FROM #order_money
WHERE add_time BETWEEN CONVERT(
VARCHAR(100),
DATEADD(DAY, -(DATEPART(weekday, GETDATE()) -1), GETDATE()),
23
) AND CONVERT(
VARCHAR(100),
DATEADD(DAY, -(DATEPART(weekday, GETDATE()) -7), GETDATE()),
23
)
),
cte3 AS (
SELECT DISTINCT USER_NAME,
[GROUP],
SUM([MONEY]) OVER(PARTITION BY USER_NAME, [GROUP]) AS 当日金额
FROM #order_money
WHERE convert(varchar(100),add_time,23)=convert(varchar(100),GETDATE(),23)
),
cte4 AS(
SELECT '小组'+cast(a.[GROUP] AS VARCHAR(20)) as '姓名',SUM(a.本月金额) as 本月金额,SUM(b.本周金额) as 本周金额,SUM(c.当日金额) as 当日金额 FROM cte a
INNER JOIN cte2 b ON a.USER_NAME=b.USER_NAME AND a.[group]=b.[group]
INNER JOIN cte3 c ON a.USER_NAME=c.USER_NAME AND a.[group]=c.[group]
GROUP BY a.[GROUP]
),
cte5 AS (
SELECT '总计' as user_name,SUM(本月金额) as 本月金额,sum(本周金额) as 本周金额,sum(当日金额) as 当日金额 FROM cte4
),
cte6 AS (
SELECT a.user_name AS '姓名',a.本月金额,b.本周金额,c.当日金额 FROM cte a
INNER JOIN cte2 b ON a.USER_NAME=b.USER_NAME AND a.[group]=b.[group]
INNER JOIN cte3 c ON a.USER_NAME=c.USER_NAME AND a.[group]=c.[group]
)
SELECT * FROM cte6
UNION ALL
SELECT * FROM cte5
UNION ALL
SELECT * FROM cte4