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
...全文
370 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
二月十六 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
  • 打赏
  • 举报
 回复
刚好这几天被汇总折磨的要死要活的，这里是个很好的解决思路启发地
听雨停了 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')
SQL查询语句-汇总分析
求某列数据删除重复值后的行数select count(distince 姓名) from student。列名 (not) in ( )：查询结果是括号里的内容返回值1，排在后面；否则返回值0，排在前面。错误1：在分组条件having中使用了查询结果select里的别名。错误3：在创建表时把数值数据设置成字符串数值。错误2：在行条件where后使用了汇总函数。
sql查询成绩最高分_SQL之汇总分析
最近学习了SQL的汇总分析，内容涵盖了汇总函数，分组，对分组结果指定条件，用sql解决业务问题，对查询结果排序，如何看懂报错信息，我把这些知识点整理如下：一：章节练习题1、问题：查询课程编号为“0002”的总成绩解答：select sum(成绩) as 总成绩 from score where 课程号='0002'; 问题：查询选了课程的学生人数解答：select count(distin...
sql月度汇总_sql月份汇总查询语句
sql月份汇总查询语句select '铅笔' as [月份],sum(case month(日期) when 1 then 铅笔 else 0 end) as [1月],sum(case month(日期) when 2 then 铅笔 else 0 end) as [2月],sum(case month(日期) when 3 then 铅笔 else 0 end) as [3月],sum(cas...
[数据库】sql 查询语句 汇总
本文记录学习SQL查询语句的汇总 使用时，是关键字，表示将要执行一个查询，表示“所有列”，表示将要从哪个表查询例如，要指定条件“分数在80分或以上的学生”，写成条件就是。其中，关键字后面的就是条件。是列名，该列存储了学生的成绩，因此，就筛选出了指定条件的记录： 3.投影查询 例如，从表中...
SQL中的查询语句汇总
【代码】SQL中的查询语句汇总。
疑难问题

22,300

社区成员

121,734

社区内容

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

加载中

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

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

+ 用AI写文章