sql查询汇总问题

Caoxp_papa 2017-11-02 10:24:59
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
)
查询结果需要这样,先是每个小组成员信息汇总,然后小组1是每个组成员(张三、李四)的汇总,总计是每个小组的汇总,如何实现?

姓名 当日金额 本周金额 本月金额
张三 100 300 3000
李四 100 200 1000
小组1 200 500 4000
王五 50 150 800
刘六 500 700 1700
小组2 550 850 2500
总计 750 1350 6500
...全文
305 11 点赞 打赏 收藏 举报
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
二月十六 2017-11-02
--测试数据
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


  • 打赏
  • 举报
回复
听雨停了 2017-11-02
给点测试数据
  • 打赏
  • 举报
回复
RINK_1 2017-11-02

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)
  • 打赏
  • 举报
回复
刚好这几天被汇总折磨的要死要活的,这里是个很好的解决思路启发地
  • 打赏
  • 举报
回复
听雨停了 2017-11-02
引用 8 楼 Caoxp_papa 的回复:
非常感谢,输出顺序可以调整一下吗?每个小组的成员,然后小组汇总,第二个小组成员,第二个小组汇总..... 姓名 当日金额 本周金额 本月金额 张三 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 '姓名',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
  • 打赏
  • 举报
回复
Caoxp_papa 2017-11-02
引用 7 楼 qq_37170555 的回复:
[quote=引用 6 楼 Caoxp_papa 的回复:] [quote=引用 5 楼 sinat_28984567 的回复:] [quote=引用 3 楼 Caoxp_papa 的回复:] 测试数据: 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')
基于这个数据的结果是什么?[/quote] 结果是: 姓名 当日金额 本周金额 本月金额 张三 100 300 3000 李四 100 200 1000 小组1 200 500 4000 王五 50 150 800 刘六 500 700 1700 小组2 550 850 2500 总计 750 1350 6500[/quote]

--测试数据
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
  • 打赏
  • 举报
回复
听雨停了 2017-11-02
引用 6 楼 Caoxp_papa 的回复:
[quote=引用 5 楼 sinat_28984567 的回复:]
[quote=引用 3 楼 Caoxp_papa 的回复:]
测试数据:
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')

基于这个数据的结果是什么?[/quote]

结果是:
姓名 当日金额 本周金额 本月金额
张三 100 300 3000
李四 100 200 1000
小组1 200 500 4000
王五 50 150 800
刘六 500 700 1700
小组2 550 850 2500
总计 750 1350 6500[/quote]

--测试数据
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

  • 打赏
  • 举报
回复
Caoxp_papa 2017-11-02
引用 5 楼 sinat_28984567 的回复:
[quote=引用 3 楼 Caoxp_papa 的回复:] 测试数据: 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')
基于这个数据的结果是什么?[/quote] 结果是: 姓名 当日金额 本周金额 本月金额 张三 100 300 3000 李四 100 200 1000 小组1 200 500 4000 王五 50 150 800 刘六 500 700 1700 小组2 550 850 2500 总计 750 1350 6500
  • 打赏
  • 举报
回复
二月十六 2017-11-02
引用 3 楼 Caoxp_papa 的回复:
测试数据: 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')
基于这个数据的结果是什么?
  • 打赏
  • 举报
回复
Caoxp_papa 2017-11-02
引用 2 楼 sinat_28984567 的回复:
--测试数据
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
不好意思,应该是我没描述清除,order_money这个表是记录的每一个订单的金额信息,没有汇总信息。上边是测试数据。
  • 打赏
  • 举报
回复
Caoxp_papa 2017-11-02
测试数据: 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')
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
加入

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2017-11-02 10:24
社区公告
暂无公告