34,590
社区成员
发帖
与我相关
我的任务
分享
alter PROCEDURE [dbo].[GetOwnerChartByUserID]
@UserID int --(这是城主的UserID)
AS
DECLARE @CityID int,@AddedDate DateTime
SELECT @CityID = CityID,@AddedDate=AddedDate FROM #Categories_Owners WHERE UserID=@UserID
SELECT DISTINCT number,ISNULL(count(b.UserID) over(PARTITION BY month(b.AddedDate)),0) AS ownerpeople
FROM MASTER..spt_values a
LEFT JOIN #Accounts_Users b ON a.number=month(b.AddedDate) AND b.CityID = @CityID
WHERE TYPE='P'
AND number BETWEEN month(@AddedDate) AND MONTH(GETDATE())
number ownerpeople
----------- -----------
3 4
4 5
5 6
6 2
7 0
8 0
9 0
10 0
--测试数据
if not object_id(N'Tempdb..#Accounts_Users') is null
drop table #Accounts_Users
Go
CREATE TABLE #Accounts_Users(UserId INT,CityID INT,AddedDate DATETIME)
Insert #Accounts_Users
select 1,1,'2017-01-04 00:25:45.730' union all
select 2,1,'2017-01-11 18:59:49.373' union all
select 3,1,'2017-02-05 10:44:14.263' union all
select 4,1,'2017-02-06 17:10:28.740' union all
select 5,1,'2017-02-07 18:43:20.417' union all
select 6,1,'2017-02-08 15:34:30.533' union all
select 7,1,'2017-02-11 22:48:40.537' union all
select 8,1,'2017-02-27 17:54:24.137' union all
select 9,1,'2017-03-20 10:24:20.117' union all
select 10,1,'2017-03-20 15:01:21.547' union all
select 11,1,'2017-03-23 00:30:58.057' union all
select 12,1,'2017-03-30 21:45:49.563' union all
select 13,1,'2017-04-04 13:22:13.737' union all
select 14,2,'2017-04-09 11:48:24.913' union all
select 15,1,'2017-04-11 18:27:37.720' union all
select 16,1,'2017-04-12 18:14:17.097' union all
select 17,1,'2017-04-13 15:43:53.417' union all
select 18,1,'2017-04-24 12:32:51.613' union all
select 19,1,'2017-05-04 13:15:07.793' union all
select 20,1,'2017-05-07 14:08:14.263' union all
select 21,1,'2017-05-12 14:36:51.260' union all
select 22,1,'2017-05-15 23:33:00.747' union all
select 23,1,'2017-05-16 11:16:07.320' union all
select 24,1,'2017-05-18 13:47:06.903' union all
select 25,1,'2017-06-14 23:39:59.900' union all
select 26,1,'2017-06-15 22:56:29.100'
Go
if not object_id(N'Tempdb..#Categories_Owners') is null
drop table #Categories_Owners
Go
CREATE TABLE #Categories_Owners(OwnerID INT, UserID INT,CityID INT, AddedDate DATETIME)
Insert #Categories_Owners
select 1,30,1,'2017-03-05 10:44:14.263'
Go
--测试数据结束
SELECT MONTH(#Accounts_Users.AddedDate) AS 月份 ,
COUNT(1) AS 增加的人口
FROM #Categories_Owners
JOIN #Accounts_Users ON #Accounts_Users.CityID = #Categories_Owners.CityID
AND #Accounts_Users.AddedDate >= #Categories_Owners.AddedDate
GROUP BY MONTH(#Accounts_Users.AddedDate)