求大神指教!!SQL表中获取从今天起前5天的统计数据,如果表里没有这一天的记录数据就为0!

liuchenru 2021-02-22 07:15:15
id type datetime
1 1 2014-01-01
2 2 2014-01-01
3 0 2014-01-01
4 0 2014-01-03
5 2 2014-01-04
6 1 2014-01-04
7 1 2014-01-06
8 2 2014-01-06
9 1 2014-01-07

现要得到在2014-01-01 到 2014-01-07的如下记录 且需要根据type的值进行分别统计

date count type0 type1 type2
2014-1-1 3 1 1 1
2014-1-2 0 0 0 0
2014-1-3 1 1 0 0
2014-1-4 2 0 1 1
2014-1-5 0 0 0 0
2014-1-6 2 0 1 1
2014-1-7 1 0 1 0


SQL表中获取时间段里每一天的数据,如果表里没有这一天的记录数据就为0
不知道那位大神能帮我解决这个问题,小弟不胜感激。
...全文
1322 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
luj_1768 2021-02-27
  • 打赏
  • 举报
回复
题目的解释应该是:把一个数值域(type)依据取值区间(type0, type1, type2)进行筛选统计。 第一步:数据预处理。 for each row in records{ switch(type){ case type0: type0=true; type1=false; type2=false; break; case type1: type0=false; type1=true; type2=false; break; case type2: type0=false; type1=false; type2=true; break; default: } } 第二步:依据真假值进行筛选统计。 这么做的好处是更加通用,更加规范,更加快速。
m0_53553242 2021-02-27
  • 打赏
  • 举报
回复
学习了谢谢大神
锟斤拷锟斤拷 2021-02-26
  • 打赏
  • 举报
回复
行数和列名都没问题,只是结果都为0的话 会不会是join的时候出了问题呢 ON a.LD_Date = CTE1.datetime AND a.status=0 AND A.LD_LCChannel_ID = CTE1.LC_Channel_ID 检查一下这个join条件是否满足呢?比如我看你把第二行的date类型换成datetime类型了,会不会在你的dbo.LD_Detail表里是datetime类型呢? LD_Date会不会是类似2021-02-26 10:57:16.913这样的有时分秒的数据呢?
liuchenru 2021-02-25
  • 打赏
  • 举报
回复
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
liuchenru 2021-02-25
  • 打赏
  • 举报
回复
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
liuchenru 2021-02-25
  • 打赏
  • 举报
回复
添麻烦了,大神,有点事情请教一下,您的代码粘贴运行后,得出的结果都为0呢,这是为什么呢?
liuchenru 2021-02-23
  • 打赏
  • 举报
回复
谢谢大神,主要想在表B内没有表A的cid时,也可以实现0数量条数生成 例如: 表A: cid name 1001 小明 1002 小华 1003 小芳 1004 小李 表B: id type datetime cid 1 1 2014-01-01 1001 2 2 2014-01-01 1002 3 0 2014-01-01 1001 4 0 2014-01-03 1002 5 2 2014-01-04 1003 6 1 2014-01-04 1001 7 1 2014-01-06 1002 8 2 2014-01-06 1002 9 1 2014-01-07 1001 date count type0 type1 type2 cid 2014-1-1 3 1 1 1 1001 2014-1-2 0 0 0 0 1001 2014-1-3 1 1 0 0 1001 2014-1-4 2 0 1 1 1001 2014-1-5 0 0 0 0 1001 2014-1-6 2 0 1 1 1001 2014-1-7 1 0 1 0 1001 2014-1-1 3 1 1 1 1002 2014-1-2 0 0 0 0 1002 2014-1-3 1 1 0 0 1002 2014-1-4 2 0 1 1 1002 2014-1-5 0 0 0 0 1002 2014-1-6 2 0 1 1 1002 2014-1-7 1 0 1 0 1002 2014-1-1 3 1 1 1 1003 2014-1-2 0 0 0 0 1003 2014-1-3 1 1 0 0 1003 2014-1-4 2 0 1 1 1003 2014-1-5 0 0 0 0 1003 2014-1-6 2 0 1 1 1003 2014-1-7 1 0 1 0 1003 2014-1-1 0 0 0 0 1004 2014-1-2 0 0 0 0 1004 2014-1-3 0 0 0 0 1004 2014-1-4 0 0 0 0 1004 2014-1-5 0 0 0 0 1004 2014-1-6 0 0 0 0 1004 2014-1-7 0 0 0 0 1004
liuchenru 2021-02-23
  • 打赏
  • 举报
回复
求大神们指教一下,非常感谢!
锟斤拷锟斤拷 2021-02-23
  • 打赏
  • 举报
回复
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要判断...
锟斤拷锟斤拷 2021-02-23
  • 打赏
  • 举报
回复
group by的时候,把cid也写进去好像就行了
寻道模式 2021-02-23
  • 打赏
  • 举报
回复
早上忙,刚才看到楼上的解答,感觉是对的,没有问题。case when就是干这个的
liuchenru 2021-02-23
  • 打赏
  • 举报
回复
引用 7 楼 雨夹雪 的回复:
SELECT number FROM master.dbo.spt_values WHERE type='P' AND number BETWEEN 0 AND 6 这个是系统表,这样就查询到0到6的数字而已
懂了懂了,谢谢您,您看可以帮忙看一下刚刚加的那个表吗?抱歉给您添麻烦了
liuchenru 2021-02-23
  • 打赏
  • 举报
回复
非常感谢各位大神的回复,另有一点小问题请教各位大神一下,可否再帮忙看一看如何修改 现在有另一张表 表A: cid name 1001 小明 1002 小华 1003 小芳 表B: id type datetime cid 1 1 2014-01-01 1001 2 2 2014-01-01 1002 3 0 2014-01-01 1001 4 0 2014-01-03 1002 5 2 2014-01-04 1003 6 1 2014-01-04 1001 7 1 2014-01-06 1002 8 2 2014-01-06 1002 9 1 2014-01-07 1001 最后想查询每个cid7日内的数据 date count type0 type1 type2 cid 2014-1-1 3 1 1 1 1001 2014-1-2 0 0 0 0 1001 2014-1-3 1 1 0 0 1001 2014-1-4 2 0 1 1 1001 2014-1-5 0 0 0 0 1001 2014-1-6 2 0 1 1 1001 2014-1-7 1 0 1 0 1001 2014-1-1 3 1 1 1 1002 2014-1-2 0 0 0 0 1002 2014-1-3 1 1 0 0 1002 2014-1-4 2 0 1 1 1002 2014-1-5 0 0 0 0 1002 2014-1-6 2 0 1 1 1002 2014-1-7 1 0 1 0 1002 2014-1-1 3 1 1 1 1003 2014-1-2 0 0 0 0 1003 2014-1-3 1 1 0 0 1003 2014-1-4 2 0 1 1 1003 2014-1-5 0 0 0 0 1003 2014-1-6 2 0 1 1 1003 2014-1-7 1 0 1 0 1003
ダ雨夹雪リ 2021-02-23
  • 打赏
  • 举报
回复
SELECT number FROM master.dbo.spt_values WHERE type='P' AND number BETWEEN 0 AND 6 这个是系统表,这样就查询到0到6的数字而已
liuchenru 2021-02-23
  • 打赏
  • 举报
回复
引用 3 楼 雨夹雪 的回复:


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

您好,抱歉麻烦您问一下,P是什么意思呢?
锟斤拷锟斤拷 2021-02-23
  • 打赏
  • 举报
回复

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
二月十六 2021-02-23
  • 打赏
  • 举报
回复
--测试数据
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;
ダ雨夹雪リ 2021-02-23
  • 打赏
  • 举报
回复


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

锟斤拷锟斤拷 2021-02-23
  • 打赏
  • 举报
回复
引用 15 楼 liuchenru 的回复:
[quote=引用 14 楼 锟斤拷锟斤拷 的回复:][quote=引用 13 楼 liuchenru 的回复:]谢谢大神,主要想在表B内没有表A的cid时,也可以实现0数量条数生成 例如: 表A: cid name 1001 小明 1002 小华 1003 小芳 1004 小李 表B: id type datetime cid 1 1 2014-01-01 1001 2 2 2014-01-01 1002 3 0 2014-01-01 1001 4 0 2014-01-03 1002 5 2 2014-01-04 1003 6 1 2014-01-04 1001 7 1 2014-01-06 1002 8 2 2014-01-06 1002 9 1 2014-01-07 1001 date count type0 type1 type2 cid 2014-1-1 3 1 1 1 1001 2014-1-2 0 0 0 0 1001 2014-1-3 1 1 0 0 1001 2014-1-4 2 0 1 1 1001 2014-1-5 0 0 0 0 1001 2014-1-6 2 0 1 1 1001 2014-1-7 1 0 1 0 1001 2014-1-1 3 1 1 1 1002 2014-1-2 0 0 0 0 1002 2014-1-3 1 1 0 0 1002 2014-1-4 2 0 1 1 1002 2014-1-5 0 0 0 0 1002 2014-1-6 2 0 1 1 1002 2014-1-7 1 0 1 0 1002 2014-1-1 3 1 1 1 1003 2014-1-2 0 0 0 0 1003 2014-1-3 1 1 0 0 1003 2014-1-4 2 0 1 1 1003 2014-1-5 0 0 0 0 1003 2014-1-6 2 0 1 1 1003 2014-1-7 1 0 1 0 1003 2014-1-1 0 0 0 0 1004 2014-1-2 0 0 0 0 1004 2014-1-3 0 0 0 0 1004 2014-1-4 0 0 0 0 1004 2014-1-5 0 0 0 0 1004 2014-1-6 0 0 0 0 1004 2014-1-7 0 0 0 0 1004
哎?我以为还需要根据表B里每个cid的开始时间和结束时间来取区间呢,原来是固定的前7天吗?

--测试数据
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
liuchenru 2021-02-23
  • 打赏
  • 举报
回复
引用 14 楼 锟斤拷锟斤拷 的回复:
[quote=引用 13 楼 liuchenru 的回复:]谢谢大神,主要想在表B内没有表A的cid时,也可以实现0数量条数生成 例如: 表A: cid name 1001 小明 1002 小华 1003 小芳 1004 小李 表B: id type datetime cid 1 1 2014-01-01 1001 2 2 2014-01-01 1002 3 0 2014-01-01 1001 4 0 2014-01-03 1002 5 2 2014-01-04 1003 6 1 2014-01-04 1001 7 1 2014-01-06 1002 8 2 2014-01-06 1002 9 1 2014-01-07 1001 date count type0 type1 type2 cid 2014-1-1 3 1 1 1 1001 2014-1-2 0 0 0 0 1001 2014-1-3 1 1 0 0 1001 2014-1-4 2 0 1 1 1001 2014-1-5 0 0 0 0 1001 2014-1-6 2 0 1 1 1001 2014-1-7 1 0 1 0 1001 2014-1-1 3 1 1 1 1002 2014-1-2 0 0 0 0 1002 2014-1-3 1 1 0 0 1002 2014-1-4 2 0 1 1 1002 2014-1-5 0 0 0 0 1002 2014-1-6 2 0 1 1 1002 2014-1-7 1 0 1 0 1002 2014-1-1 3 1 1 1 1003 2014-1-2 0 0 0 0 1003 2014-1-3 1 1 0 0 1003 2014-1-4 2 0 1 1 1003 2014-1-5 0 0 0 0 1003 2014-1-6 2 0 1 1 1003 2014-1-7 1 0 1 0 1003 2014-1-1 0 0 0 0 1004 2014-1-2 0 0 0 0 1004 2014-1-3 0 0 0 0 1004 2014-1-4 0 0 0 0 1004 2014-1-5 0 0 0 0 1004 2014-1-6 0 0 0 0 1004 2014-1-7 0 0 0 0 1004
哎?我以为还需要根据表B里每个cid的开始时间和结束时间来取区间呢,原来是固定的前7天吗?

--测试数据
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天应该怎样写呢
加载更多回复(2)

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧