SELECT DISTINCT Campaign_id
FROM Impressions
WHERE Date BETWEEN '2015-03-01' AND '2015-03-31' ;
上面的查询给出了在2015年3月1日至2015年3月31日期间任何一天活动过的Campaign_id的结果。
我希望结果集包含那些在2015年3月1日至2015年3月31日期间所有日期都活动过的campaign_id。
我该如何做?
SELECT DISTINCT Campaign_id
FROM Impressions
WHERE Date BETWEEN '2015-03-01' AND '2015-03-31' ;
DATE
是一个不带时间部分的日期数据类型。DECLARE @Start DATE = '2015-03-01',
@End DATE = '2015-03-31'
SELECT Campaign_id
FROM Impressions
WHERE Date BETWEEN @Start AND @End
GROUP BY Campaign_id
HAVING COUNT(DISTINCT Date) = 1 + DATEDIFF(DAY, @Start, @End);
或者一个不带变量的版本
SELECT Campaign_id
FROM Impressions
CROSS APPLY (VALUES ({ d '2015-03-01' },
{ d '2015-03-31' })) V([Start], [End])
WHERE [Date] BETWEEN [Start] AND [End]
GROUP BY Campaign_id, [Start], [End]
HAVING COUNT(DISTINCT Date) = 1 + DATEDIFF(DAY, [Start], [End]);
在使用 COUNT(DISTINCT)
时,可以结合 HAVING
子句:
SELECT Campaign_id
FROM Impressions
WHERE Date between '2015-03-01' and '2015-03-31'
GROUP BY Campaign_id
HAVING COUNT(DISTINCT Date) = 31;
BETWEEN
是一个坏主意。WITH params as (
SELECT CAST('2015-03-01' as DATE) as date1, CAST('2015-03-31' as DATE) date2
)
SELECT i.Campaign_id
FROM params CROSS JOIN
Impressions i
WHERE i.Date >= params.Date1 and i.Date < DATEADD(day, 1, params.Date2)
GROUP BY i.Campaign_id, params.date1, params.date2
HAVING COUNT(DISTINCT i.Date) = 1 + DATEDIFF(day, params.date1, params.date2);
JOIN
而不是 CROSS JOIN
。出于习惯,我总是在使用 CROSS JOIN
的查询中放置一个参数 CTE。
DATEDIFF
只是避免了硬编码这个数字。 - Martin Smith