SQL 分组查询 ###########

[t101lian]

两个主要字段
金额 创建时间
2000 ‘2018-02-09’
2060 ‘2018-08-09’
863.50 '2018-10-25’
7000 ‘2018-11-06’
2810 ‘2018-12-21’
----------------------------------------
想得到下面的数据格式
时间（距离当前的时间天数） 总金额 占比（%）
1-30(天)
31-60(天)
60-180（天)
大于180(天)

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([金额] decimal(18,8),[创建时间] Date)
Insert #T
select 2000,'2018-02-09' union all
select 2060,'2018-08-09' union all
select 863.50,'2018-10-25' union all
select 7000,'2018-11-06' union all
select 2810,'2018-12-21'
Go
--测试数据结束
SELECT CASE WHEN t.天数 BETWEEN 1 AND 30 THEN '1-30(天) '
WHEN t.天数 BETWEEN 31 AND 60 THEN '31-60(天) '
WHEN t.天数 BETWEEN 61 AND 180 THEN '61-180(天) '
WHEN t.天数 >180 THEN '大于180(天) ' END AS 时间,SUM(金额) AS 总金额,RTRIM(SUM(金额)/(SELECT SUM(金额) FROM #T)*100)+'%' AS 占比 FROM (
Select *,ABS(DATEDIFF(DAY,创建时间,GETDATE())) AS 天数 from #T
)t GROUP BY CASE WHEN t.天数 BETWEEN 1 AND 30 THEN '1-30(天) '
WHEN t.天数 BETWEEN 31 AND 60 THEN '31-60(天) '
WHEN t.天数 BETWEEN 61 AND 180 THEN '61-180(天) '
WHEN t.天数 >180 THEN '大于180(天) ' END

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[money] DECIMAL(10,2)
,[addTime] DATETIME
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'2000',N'2018-02-09')
INSERT INTO dbo.[t] VALUES(N'2060',N'2018-08-09')
INSERT INTO dbo.[t] VALUES(N'863.50',N'2018-10-25')
INSERT INTO dbo.[t] VALUES(N'7000',N'2018-11-06')
INSERT INTO dbo.[t] VALUES(N'2810',N'2018-12-21')

;WITH cte AS(
	SELECT 1 AS startN,30 AS endN,'1-30(天)' AS remark
	UNION ALL
	SELECT 31 AS startN,60 AS endN,'31-60(天)' AS remark
	UNION ALL
	SELECT 60 AS startN,180 AS endN,'60-180（天)' AS remark
	UNION ALL
	SELECT 180 AS startN,999999999 AS endN,'大于180(天)' AS remark
)
SELECT a.remark AS [时间（距离当前的时间天数）]
,SUM(b.[money]) AS [总金额]
,(SUM(b.[money])*100.0)/(SELECT SUM([money]) FROM t) AS [占比（%）]
FROM cte AS a LEFT JOIN t AS b ON DATEDIFF(DAY,b.addtime,GETDATE()) BETWEEN a.startN AND a.endN
GROUP BY a.remark

[早起晚睡]

两个大版主就是牛啊 别人都没看呢 已经结果都出来了