双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

sql查询汇总问题

2017-11-02 10:24:59 446 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Caoxp_papa 墨衍会员 · AI 创作全网分发关注 + 加入社区

ABB AC500 系列 PLC 与上位机iFix 的通讯配置在软件中，有 3 种方式可以进入 IP config tool 的配置界面 双击 IP_settings，点击 IP config tool，即可进入 IP config tool 界面点击菜单栏的 Tool→IP config如果单独安装了 ABB IP Configration，则可以通过开始→程序→ABB→ABB IPConfigration 启动 IP 配置工具启动 IP config tool 后，即可进入 IP 配置界面。阅读详情

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

...全文

【STC32G12K128开发板】——从零到一：Keil C251开发环境全攻略本文详细介绍了为STC32G12K128开发板搭建Keil C251开发环境的完整流程。从环境准备、软件安装、驱动配置到创建第一个工程并下载程序，提供了避坑指南和实用技巧，帮助开发者高效入门这款基于251内核的国产32位单片机开发。阅读详情

Caoxp_papa

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

二月十六 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')

✦双11狂欢大促✦

sql查询汇总问题

相关推荐

双11狂欢大促