【80分】SQL题目，解决立结贴

[renyiqiu]

表 paper

rpp_code	rpp_insertdate	city
21010000020056	2015-12-04 14:10:06.000 	北京
21010000020110	2015-12-04 14:11:16.000	上海
99999500022810	2015-12-04 14:11:38.000	北京
51010000022274	2015-12-05 16:22:06.000	杭州
44030000021436	2015-12-05 16:22:16.000	北京
51010000022164	2015-12-05 16:23:14.000	杭州
 21020000020050	2015-12-06 13:32:45.000	上海
21020000020082	2015-12-06 13:36:58.000	北京
21020000020089	2015-12-06 13:38:08.000	北京

这是表结构，
现在要获取不同日期里的数量，而且后一天要累计前一天的数量，比如上面4号3条，5号3条，6号3条，现在我想要这种结构，统计每天的数量，分城市

城市	2015-12-04	2015-12-05	2015-12-06
北京	1	2	4
上海	1	1	2
杭州	0	2	2

[中国风]

累积记录有问题 北京--共5条 4号=2 5号=1 6号=2 6号显示4怎么来的 累积所有：5，累积前一天3

[中国风]

--累积前一天

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([rpp_code] VARCHAR(100),[rpp_insertdate] Datetime,[city] nvarchar(2))
Insert #T
select 21010000020056,'2015-12-04 14:10:06.000',N'北京' union all
select 21010000020110,'2015-12-04 14:11:16.000',N'上海' union all
select 99999500022810,'2015-12-04 14:11:38.000',N'北京' union all
select 51010000022274,'2015-12-05 16:22:06.000',N'杭州' union all
select 44030000021436,'2015-12-05 16:22:16.000',N'北京' union all
select 51010000022164,'2015-12-05 16:23:14.000',N'杭州' union all
select 21020000020050,'2015-12-06 13:32:45.000',N'上海' union all
select 21020000020082,'2015-12-06 13:36:58.000',N'北京' union all
select 21020000020089,'2015-12-06 13:38:08.000',N'北京'
GO
DECLARE @Sql NVARCHAR(max)=''
Select @Sql=@Sql+','+QUOTENAME(CONVERT(VARCHAR(10),[rpp_insertdate],120))+'=SUM(CASE WHEN  DATEDIFF(dd,[rpp_insertdate],'+QUOTENAME(CONVERT(VARCHAR(10),[rpp_insertdate],120),'''')+') IN(1,0) THEN 1 ELSE 0 END)' from #T GROUP by CONVERT(VARCHAR(10),[rpp_insertdate],120)
EXEC('SELECT [city]'+@Sql+' FROM #T GROUP BY [city]')
/*
city	2015-12-04	2015-12-05	2015-12-06
北京	2	1	2
杭州	0	2	0
上海	1	0	1*/

--累积所有

DECLARE @Sql NVARCHAR(max)=''
Select @Sql=@Sql+','+QUOTENAME(CONVERT(VARCHAR(10),[rpp_insertdate],120))+'=SUM(CASE WHEN  CONVERT(VARCHAR(10),[rpp_insertdate],120)<='+QUOTENAME(CONVERT(VARCHAR(10),[rpp_insertdate],120),'''')+' THEN 1 ELSE 0 END)' from #T GROUP by CONVERT(VARCHAR(10),[rpp_insertdate],120)
EXEC('SELECT [city]'+@Sql+' FROM #T GROUP BY [city]')
/*
city	2015-12-04	2015-12-05	2015-12-06
北京	2	3	5
杭州	0	2	2
上海	1	1	2*/

[ctmb520]

路过没看懂,猜的结果是这样

城市	2015-12-04	2015-12-05	2015-12-06
北京	2	3=2+1	5=2+3
上海	1	1	2
杭州	0	2	2

[renyiqiu]

引用 2 楼 roy_88 的回复:

--累积前一天

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([rpp_code] VARCHAR(100),[rpp_insertdate] Datetime,[city] nvarchar(2))
Insert #T
select 21010000020056,'2015-12-04 14:10:06.000',N'北京' union all
select 21010000020110,'2015-12-04 14:11:16.000',N'上海' union all
select 99999500022810,'2015-12-04 14:11:38.000',N'北京' union all
select 51010000022274,'2015-12-05 16:22:06.000',N'杭州' union all
select 44030000021436,'2015-12-05 16:22:16.000',N'北京' union all
select 51010000022164,'2015-12-05 16:23:14.000',N'杭州' union all
select 21020000020050,'2015-12-06 13:32:45.000',N'上海' union all
select 21020000020082,'2015-12-06 13:36:58.000',N'北京' union all
select 21020000020089,'2015-12-06 13:38:08.000',N'北京'
GO
DECLARE @Sql NVARCHAR(max)=''
Select @Sql=@Sql+','+QUOTENAME(CONVERT(VARCHAR(10),[rpp_insertdate],120))+'=SUM(CASE WHEN  DATEDIFF(dd,[rpp_insertdate],'+QUOTENAME(CONVERT(VARCHAR(10),[rpp_insertdate],120),'''')+') IN(1,0) THEN 1 ELSE 0 END)' from #T GROUP by CONVERT(VARCHAR(10),[rpp_insertdate],120)
EXEC('SELECT [city]'+@Sql+' FROM #T GROUP BY [city]')
/*
city	2015-12-04	2015-12-05	2015-12-06
北京	2	1	2
杭州	0	2	0
上海	1	0	1*/

--累积所有

DECLARE @Sql NVARCHAR(max)=''
Select @Sql=@Sql+','+QUOTENAME(CONVERT(VARCHAR(10),[rpp_insertdate],120))+'=SUM(CASE WHEN  CONVERT(VARCHAR(10),[rpp_insertdate],120)<='+QUOTENAME(CONVERT(VARCHAR(10),[rpp_insertdate],120),'''')+' THEN 1 ELSE 0 END)' from #T GROUP by CONVERT(VARCHAR(10),[rpp_insertdate],120)
EXEC('SELECT [city]'+@Sql+' FROM #T GROUP BY [city]')
/*
city	2015-12-04	2015-12-05	2015-12-06
北京	2	3	5
杭州	0	2	2
上海	1	1	2*/

大赞，有用，我会回去琢磨，先结贴