22,206
社区成员
发帖
与我相关
我的任务
分享
DECLARE @StartDate DATETIME = '2016-01-01',
@EndDate DATETIME = '2016-12-31'
DECLARE @Sql NVARCHAR(max)='',
@DT DATETIME
SELECT @DT = @StartDate
WHILE @DT <= @EndDate
BEGIN
SELECT @Sql = @Sql + ',SUM(Case WHEN LoginDate = ''' + CONVERT(VARCHAR(7),@DT,120) + ''' Then 1 else 0 end) AS [' + RTRIM(YEAR(@DT)) + '年' + RTRIM(MONTH(@DT)) + '月]' + CHAR(10)
,@DT = DATEADD(mm,1,@DT)
END
--PRINT @Sql
SELECT @Sql = '
;WITH CTE (DisplayName,MinTime,MaxTime) AS
(
SELECT ''1次'',0,2
UNION ALL
SELECT ''2~10次'',2,10
UNION ALL
SELECT ''10次以上'',10,10000
),CTE2 AS(
SELECT a.LoginDate,b.DisplayName,a.Qty AS Qty
FROM CTE b
LEFT JOIN (SELECT CONVERT(VARCHAR(7),LoginDate,120) AS LoginDate,COUNT(DISTINCT CONVERT(VARCHAR(10),LoginDate,120)) AS Qty
FROM #TB
WHERE LoginDate >= '''+CONVERT(VARCHAR(10),@StartDate,120)+'''
AND LoginDate < '''+CONVERT(VARCHAR(10),@EndDate,120)+'''
GROUP BY CONVERT(VARCHAR(7),LoginDate,120),UserID) a ON a.Qty >= b.MinTime AND a.Qty < b.MaxTime)
--INNER JOIN CTE b ON a.Qty >= b.MinTime AND a.Qty < b.MaxTime)
SELECT DisplayName,' +
STUFF(@Sql,1,1,'')
+ '
FROM CTE2
Group by DisplayName
'
--PRINT @Sql
EXEC (@Sql)
DECLARE @StartDate DATETIME = '2016-02-01',
@EndDate DATETIME = '2016-02-29'
DECLARE @Sql NVARCHAR(max)='',
@DT DATETIME
SELECT @DT = @StartDate
WHILE @DT <= @EndDate
BEGIN
SELECT @Sql = @Sql + ',SUM(Case WHEN LoginDate = ''' + CONVERT(VARCHAR(10),@DT,120) + ''' Then Qty else 0 end) AS [' + RTRIM(YEAR(@DT)) + '年' + RTRIM(MONTH(@DT)) + '月' + RTRIM(DAY(@DT)) + ']' + CHAR(10)
,@DT = DATEADD(dd,1,@DT)
END
--PRINT @Sql
SELECT @Sql = '
;WITH CTE (DisplayName,MinTime,MaxTime) AS
(
SELECT ''1次'',0,2
UNION ALL
SELECT ''2~10次'',2,10
UNION ALL
SELECT ''10次以上'',10,10000
),CTE2 AS(
SELECT a.LoginDate,b.DisplayName,a.Qty AS Qty
FROM (SELECT CONVERT(VARCHAR(10),LoginDate,120) AS LoginDate,COUNT(DISTINCT UserID) AS Qty
FROM #TB
WHERE LoginDate >= '''+CONVERT(VARCHAR(10),@StartDate,120)+'''
AND LoginDate < '''+CONVERT(VARCHAR(10),@EndDate,120)+'''
GROUP BY CONVERT(VARCHAR(10),LoginDate,120) ) a
INNER JOIN CTE b ON a.Qty >= b.MinTime AND a.Qty < b.MaxTime)
SELECT DisplayName,' +
STUFF(@Sql,1,1,'')
+ '
FROM CTE2
Group by DisplayName
'
EXEC (@Sql)
CREATE TABLE #TB (UserID INT,LoginDate DATETIME)
INSERT INTO #TB
( UserID, LoginDate )
VALUES (1,'2016-01-24'),
(1,'2016-02-24'),
(1,'2016-02-25'),
(1,'2016-02-26'),
(2,'2016-02-24'),
(3,'2016-02-24'),
(3,'2016-02-25'),
(3,'2016-02-26'),
(4,'2016-02-01'),
(4,'2016-02-24'),
(4,'2016-02-25')
;WITH CTE (DisplayName,MinTime,MaxTime) AS
(
SELECT '1次',0,2
UNION ALL
SELECT '2~10次',2,10
UNION ALL
SELECT '10次以上',10,10000
),CTE2 AS(
SELECT a.LoginDate,b.DisplayName,a.Qty AS Qty
FROM (SELECT CONVERT(VARCHAR(10),LoginDate,120) AS LoginDate,COUNT(DISTINCT UserID) AS Qty
FROM #TB
WHERE LoginDate >= '2016-02-01'
AND LoginDate < '2016-02-29'
GROUP BY CONVERT(VARCHAR(10),LoginDate,120) ) a
INNER JOIN CTE b ON a.Qty >= b.MinTime AND a.Qty < b.MaxTime)
SELECT DisplayName,SUM(Case WHEN LoginDate = '2016-02-01' Then Qty else 0 end) AS [2016年2月1]
,SUM(Case WHEN LoginDate = '2016-02-02' Then Qty else 0 end) AS [2016年2月2]
,SUM(Case WHEN LoginDate = '2016-02-03' Then Qty else 0 end) AS [2016年2月3]
,SUM(Case WHEN LoginDate = '2016-02-04' Then Qty else 0 end) AS [2016年2月4]
,SUM(Case WHEN LoginDate = '2016-02-05' Then Qty else 0 end) AS [2016年2月5]
,SUM(Case WHEN LoginDate = '2016-02-06' Then Qty else 0 end) AS [2016年2月6]
,SUM(Case WHEN LoginDate = '2016-02-07' Then Qty else 0 end) AS [2016年2月7]
,SUM(Case WHEN LoginDate = '2016-02-08' Then Qty else 0 end) AS [2016年2月8]
,SUM(Case WHEN LoginDate = '2016-02-09' Then Qty else 0 end) AS [2016年2月9]
,SUM(Case WHEN LoginDate = '2016-02-10' Then Qty else 0 end) AS [2016年2月10]
,SUM(Case WHEN LoginDate = '2016-02-11' Then Qty else 0 end) AS [2016年2月11]
,SUM(Case WHEN LoginDate = '2016-02-12' Then Qty else 0 end) AS [2016年2月12]
,SUM(Case WHEN LoginDate = '2016-02-13' Then Qty else 0 end) AS [2016年2月13]
,SUM(Case WHEN LoginDate = '2016-02-14' Then Qty else 0 end) AS [2016年2月14]
,SUM(Case WHEN LoginDate = '2016-02-15' Then Qty else 0 end) AS [2016年2月15]
,SUM(Case WHEN LoginDate = '2016-02-16' Then Qty else 0 end) AS [2016年2月16]
,SUM(Case WHEN LoginDate = '2016-02-17' Then Qty else 0 end) AS [2016年2月17]
,SUM(Case WHEN LoginDate = '2016-02-18' Then Qty else 0 end) AS [2016年2月18]
,SUM(Case WHEN LoginDate = '2016-02-19' Then Qty else 0 end) AS [2016年2月19]
,SUM(Case WHEN LoginDate = '2016-02-20' Then Qty else 0 end) AS [2016年2月20]
,SUM(Case WHEN LoginDate = '2016-02-21' Then Qty else 0 end) AS [2016年2月21]
,SUM(Case WHEN LoginDate = '2016-02-22' Then Qty else 0 end) AS [2016年2月22]
,SUM(Case WHEN LoginDate = '2016-02-23' Then Qty else 0 end) AS [2016年2月23]
,SUM(Case WHEN LoginDate = '2016-02-24' Then Qty else 0 end) AS [2016年2月24]
,SUM(Case WHEN LoginDate = '2016-02-25' Then Qty else 0 end) AS [2016年2月25]
,SUM(Case WHEN LoginDate = '2016-02-26' Then Qty else 0 end) AS [2016年2月26]
,SUM(Case WHEN LoginDate = '2016-02-27' Then Qty else 0 end) AS [2016年2月27]
,SUM(Case WHEN LoginDate = '2016-02-28' Then Qty else 0 end) AS [2016年2月28]
,SUM(Case WHEN LoginDate = '2016-02-29' Then Qty else 0 end) AS [2016年2月29]
FROM CTE2
Group by DisplayName
with cte
(UserID , LoginDate ) as (
select 1 ,'2016-01-24 00:00:00.000' union
select 1 ,'2016-02-24 00:00:00.000' union
select 1 ,'2016-02-25 00:00:00.000' union
select 1 ,'2016-02-26 00:00:00.000' union
select 2 ,'2016-02-24 00:00:00.000' union
select 3 ,'2016-02-24 00:00:00.000' union
select 3 ,'2016-02-25 00:00:00.000' union
select 3 ,'2016-02-26 00:00:00.000')
,cte1(id,dly,cs) as (select UserID,CONVERT(char(7),LoginDate,112),COUNT(*)
from cte group by UserID,CONVERT(char(7),LoginDate,112) )
select dly,case when cs=1 then '该月登录1次用户数' when cs between 2 and 10
then '登录2-10次用户数' else '登录10次以上用户数' end
,isnull(COUNT(id),0) from cte1 group by dly,cs
初步这样,行转列不怎么会,我研究一下。已经满足你的要求了