22,199
社区成员
发帖
与我相关
我的任务
分享
WITH cteb AS (
SELECT DATEADD(DAY, number, CONVERT(datetime, '2021-02-21')) [datetime]
FROM master..spt_values
WHERE type = 'P'
AND number
BETWEEN 0 AND DATEDIFF(DAY, '2021-01-01', '2021-02-21')
)
,CTE1 AS (
SELECT * FROM CTEB A,(SELECT LC_Channel_ID FROM dbo.L_Channel GROUP By LC_Channel_ID) B
)
SELECT CTE1.datetime,
CASE WHEN a.status IS NULL THEN 0 ELSE 1 END+
CASE WHEN b.status IS NULL THEN 0 ELSE 1 END+
CASE WHEN c.status IS NULL THEN 0 ELSE 1 END AS count,
CASE WHEN a.status IS NULL THEN 0 ELSE 1 END AS type0,
CASE WHEN b.status IS NULL THEN 0 ELSE 1 END AS type1,
CASE WHEN c.status IS NULL THEN 0 ELSE 1 END AS type2,
CTE1.LC_Channel_ID
FROM CTE1
LEFT JOIN dbo.LD_Detail a
ON a.LD_Date = CTE1.datetime AND a.status=0 AND A.LD_LCChannel_ID = CTE1.LC_Channel_ID
LEFT JOIN dbo.LD_Detail b
ON b.LD_Date = CTE1.datetime AND b.status=1 AND B.LD_LCChannel_ID = CTE1.LC_Channel_ID
LEFT JOIN dbo.LD_Detail c
ON c.LD_Date = CTE1.datetime AND c.status=2 AND C.LD_LCChannel_ID = CTE1.LC_Channel_ID
order By CTE1.datetime,LC_Channel_ID
CREATE TABLE #A(
ID INT,
[TYPE] INT,
[DATE] DATE,
CID INT
)
INSERT INTO #A VALUES (1,1,'2014-01-01',1)
INSERT INTO #A VALUES (2,2,'2014-01-01',3)
INSERT INTO #A VALUES (3,0,'2014-01-01',2)
INSERT INTO #A VALUES (4,0,'2014-01-03',2)
INSERT INTO #A VALUES (5,2,'2014-01-04',4)
INSERT INTO #A VALUES (6,1,'2014-01-04',3)
INSERT INTO #A VALUES (7,1,'2014-01-06',1)
INSERT INTO #A VALUES (8,2,'2014-01-06',4)
INSERT INTO #A VALUES (9,1,'2014-01-07',4)
;WITH CTE AS(
SELECT A.datetime,B.CID FROM (
SELECT DATEADD(DAY, number, CONVERT(DATE, '2014-01-01')) [datetime]
FROM master..spt_values
WHERE type = 'P'
AND number
BETWEEN 0 AND DATEDIFF(DAY, '2014-01-01', '2014-01-07')) A,(SELECT CID,MAX(DATE) EDATE,MIN(DATE) BDATE FROM #A GROUP BY CID) B
WHERE B.BDATE<=A.datetime AND B.EDATE>A.datetime
)
SELECT * FROM CTE A
LEFT JOIN (
SELECT
A.DATE ,
COUNT(1) COUNTS,
MAX(CASE WHEN [TYPE]=0 THEN 1 ELSE 0 END) [TYPE0] ,
MAX(CASE WHEN [TYPE]=1 THEN 1 ELSE 0 END) [TYPE1] ,
MAX(CASE WHEN [TYPE]=2 THEN 1 ELSE 0 END) [TYPE2] ,
A.CID
FROM #A A
GROUP BY A.DATE,A.CID) B ON A.CID = B.CID AND A.datetime = B.DATE
DROP TABLE #A
这个方法不太好...有一堆isnull0要判断...
CREATE TABLE #A(
ID INT,
[TYPE] INT,
[DATE] DATE
)
INSERT INTO #A VALUES (1,1,'2014-01-01')
INSERT INTO #A VALUES (2,2,'2014-01-01')
INSERT INTO #A VALUES (3,0,'2014-01-01')
INSERT INTO #A VALUES (4,0,'2014-01-03')
INSERT INTO #A VALUES (5,2,'2014-01-04')
INSERT INTO #A VALUES (6,1,'2014-01-04')
INSERT INTO #A VALUES (7,1,'2014-01-06')
INSERT INTO #A VALUES (8,2,'2014-01-06')
INSERT INTO #A VALUES (9,1,'2014-01-07')
SELECT
A.DATE ,
COUNT(1) COUNTS,
MAX(CASE WHEN [TYPE]=0 THEN 1 ELSE 0 END)[TYPE0] ,
MAX(CASE WHEN [TYPE]=1 THEN 1 ELSE 0 END) [TYPE1] ,
MAX(CASE WHEN [TYPE]=2 THEN 1 ELSE 0 END) [TYPE2]
FROM #A A
GROUP BY A.DATE
DROP TABLE #A
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[type] int,[datetime] Date)
Insert #T
select 1,1,'2014-01-01' union all
select 2,2,'2014-01-01' union all
select 3,0,'2014-01-01' union all
select 4,0,'2014-01-03' union all
select 5,2,'2014-01-04' union all
select 6,1,'2014-01-04' union all
select 7,1,'2014-01-06' union all
select 8,2,'2014-01-06' union all
select 9,1,'2014-01-07'
Go
--测试数据结束
;WITH cteb AS (
SELECT DATEADD(DAY, number, CONVERT(DATE, '2014-01-01')) [datetime]
FROM master..spt_values
WHERE type = 'P'
AND number
BETWEEN 0 AND DATEDIFF(DAY, '2014-01-01', '2014-01-07')
)
SELECT cteb.datetime,
CASE WHEN a.type IS NULL THEN 0 ELSE 1 END+
CASE WHEN b.type IS NULL THEN 0 ELSE 1 END+
CASE WHEN c.type IS NULL THEN 0 ELSE 1 END AS count,
CASE WHEN a.type IS NULL THEN 0 ELSE 1 END AS type0,
CASE WHEN b.type IS NULL THEN 0 ELSE 1 END AS type1,
CASE WHEN c.type IS NULL THEN 0 ELSE 1 END AS type2
FROM cteb
LEFT JOIN #T a
ON a.datetime = cteb.datetime AND a.type=0
LEFT JOIN #T b
ON b.datetime = cteb.datetime AND b.type=1
LEFT JOIN #T c
ON c.datetime = cteb.datetime AND c.type=2;
CREATE TABLE #T
(
id INT,
type INT,
ddate DATETIME
)
INSERT INTO #T VALUES( 1 , 1 ,'2014-01-01')
INSERT INTO #T VALUES( 2 , 2 ,'2014-01-01')
INSERT INTO #T VALUES( 3 , 0 ,'2014-01-01')
INSERT INTO #T VALUES( 4 , 0 ,'2014-01-03')
INSERT INTO #T VALUES( 5 , 2 ,'2014-01-04')
INSERT INTO #T VALUES( 6 , 1 ,'2014-01-04')
INSERT INTO #T VALUES( 7 , 1 ,'2014-01-06')
INSERT INTO #T VALUES( 8 , 2 ,'2014-01-06')
INSERT INTO #T VALUES( 9 , 1 ,'2014-01-07')
--把2014-01-07换成getdate()就是今天前7天的数据
SELECT a.ddate,COUNT(1) AS cnt,
SUM(CASE WHEN type=0 THEN 1 ELSE 0 END) AS type0,
SUM(CASE WHEN type=1 THEN 1 ELSE 0 END) AS type1,
SUM(CASE WHEN type=2 THEN 1 ELSE 0 END) AS type2
FROM
(
SELECT DATEADD(DAY,-number,'2014-01-07' ) AS ddate FROM master.dbo.spt_values WHERE type='P' AND number BETWEEN 0 AND 6
) a
LEFT JOIN #T b ON a.ddate=b.ddate
GROUP BY a.ddate
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[type] int,[datetime] Date,CID INT)
Insert #T
select 1,1,'2014-01-01',1 union all
select 2,2,'2014-01-01',1 union all
select 3,0,'2014-01-01',2 union all
select 4,0,'2014-01-03',2 union all
select 5,2,'2014-01-04',3 union all
select 6,1,'2014-01-04',3 union all
select 7,1,'2014-01-06',4 union all
select 8,2,'2014-01-06',4 union all
select 9,1,'2014-01-07',4
Go
CREATE TABLE #B(
CID INT
)
INSERT INTO #B VALUES(1),(2),(3),(4),(5)
--测试数据结束
;WITH cteb AS (
SELECT DATEADD(DAY, number, CONVERT(DATE, '2014-01-01')) [datetime]
FROM master..spt_values
WHERE type = 'P'
AND number
BETWEEN 0 AND DATEDIFF(DAY, '2014-01-01', '2014-01-07')
)
,CTE1 AS (
SELECT * FROM CTEB A,(SELECT CID FROM #B GROUP BY CID) B
)
SELECT CTE1.datetime,
CASE WHEN a.type IS NULL THEN 0 ELSE 1 END+
CASE WHEN b.type IS NULL THEN 0 ELSE 1 END+
CASE WHEN c.type IS NULL THEN 0 ELSE 1 END AS count,
CASE WHEN a.type IS NULL THEN 0 ELSE 1 END AS type0,
CASE WHEN b.type IS NULL THEN 0 ELSE 1 END AS type1,
CASE WHEN c.type IS NULL THEN 0 ELSE 1 END AS type2
FROM CTE1
LEFT JOIN #T a
ON a.datetime = CTE1.datetime AND a.type=0 AND A.CID = CTE1.CID
LEFT JOIN #T b
ON b.datetime = CTE1.datetime AND b.type=1 AND B.CID = CTE1.CID
LEFT JOIN #T c
ON c.datetime = CTE1.datetime AND c.type=2 AND C.CID = CTE1.CID
DROP TABLE #T
我的方法要判断一堆isnull0太麻烦了,你的新需求在二月十六的代码上微调应该就可以了[/quote]
非常抱歉,大神,您看应该如何微调呢?小弟才疏学浅,实在没能理解,以及,您看当前日期的前7天应该怎样写呢[/quote]
我已经调整过了呀,你运行一下试试。这不是你想要的的结果吗?
39行后面要加一列,CTE1.CID
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[type] int,[datetime] Date,CID INT)
Insert #T
select 1,1,'2014-01-01',1 union all
select 2,2,'2014-01-01',1 union all
select 3,0,'2014-01-01',2 union all
select 4,0,'2014-01-03',2 union all
select 5,2,'2014-01-04',3 union all
select 6,1,'2014-01-04',3 union all
select 7,1,'2014-01-06',4 union all
select 8,2,'2014-01-06',4 union all
select 9,1,'2014-01-07',4
Go
CREATE TABLE #B(
CID INT
)
INSERT INTO #B VALUES(1),(2),(3),(4),(5)
--测试数据结束
;WITH cteb AS (
SELECT DATEADD(DAY, number, CONVERT(DATE, '2014-01-01')) [datetime]
FROM master..spt_values
WHERE type = 'P'
AND number
BETWEEN 0 AND DATEDIFF(DAY, '2014-01-01', '2014-01-07')
)
,CTE1 AS (
SELECT * FROM CTEB A,(SELECT CID FROM #B GROUP BY CID) B
)
SELECT CTE1.datetime,
CASE WHEN a.type IS NULL THEN 0 ELSE 1 END+
CASE WHEN b.type IS NULL THEN 0 ELSE 1 END+
CASE WHEN c.type IS NULL THEN 0 ELSE 1 END AS count,
CASE WHEN a.type IS NULL THEN 0 ELSE 1 END AS type0,
CASE WHEN b.type IS NULL THEN 0 ELSE 1 END AS type1,
CASE WHEN c.type IS NULL THEN 0 ELSE 1 END AS type2
FROM CTE1
LEFT JOIN #T a
ON a.datetime = CTE1.datetime AND a.type=0 AND A.CID = CTE1.CID
LEFT JOIN #T b
ON b.datetime = CTE1.datetime AND b.type=1 AND B.CID = CTE1.CID
LEFT JOIN #T c
ON c.datetime = CTE1.datetime AND c.type=2 AND C.CID = CTE1.CID
DROP TABLE #T
我的方法要判断一堆isnull0太麻烦了,你的新需求在二月十六的代码上微调应该就可以了[/quote]
非常抱歉,大神,您看应该如何微调呢?小弟才疏学浅,实在没能理解,以及,您看当前日期的前7天应该怎样写呢