我不确定表达这个问题的正确方法是什么,但我希望这是有意义的。我有一个查询:
SELECT
c.ID,
c.Name,
c.DateAdded,
c.LastContact
FROM
computers AS c
WHERE
LENGTH(DateAdded) > 1
ORDER BY
DateAdded ASC;
哪个返回:
ID Name DateAdded LastContact
1 MUTHER 9/2/2012 1:23 5/17/2016 13:57
5 VGER 12/19/2012 10:44 5/17/2016 13:56
7 HELPER 1/13/2013 18:09 5/17/2016 13:57
9 JARVIS 1/15/2013 0:28 7/31/2015 0:20
12 PHLFS1 1/17/2013 18:41 2/17/2013 13:37
我可以通过以下查询计算按季度添加计算机的日期:
SELECT
YEAR(DateAdded) AS YEAR,
QUARTER(DateAdded) AS QUARTER,
COUNT(ComputerID) AS 'added-during-qtr'
FROM
computers
WHERE
LENGTH(DateAdded) > 1
GROUP BY
YEAR(DateAdded),
QUARTER(DateAdded)
ORDER BY
YEAR(DateAdded),
QUARTER(DateAdded)
这让我了解了何时激活:
year quarter added-during-qtr
2012 3 1
2012 4 1
2013 1 3
但是如果他们在该季度是否活跃,那不是我想要的:
year quarter active-during-qtr
2012 3 1
2012 4 2
2013 1 5
2013 2 4
我不确定是否要问正确的方法,但是到了一天结束时,我想计算DateAdded和LastContact之间“活动”的计算机数量,按年份和季度排序。理想情况下,无需在case语句中手动添加范围。提前致谢。
最佳答案
仍然无法理解为什么您对2013年第二季度有所期待。
但是,这是我的尝试:
http://sqlfiddle.com/#!9/ce133d/7
SELECT
p.year,
p.quarter,
COUNT(DISTINCT c.id)
FROM (
SELECT
YEAR(DateAdded) AS YEAR,
QUARTER(DateAdded) AS QUARTER,
CONCAT(YEAR(DateAdded),QUARTER(DateAdded)) period
FROM computers
WHERE LENGTH(DateAdded) > 1
GROUP BY YEAR(DateAdded), QUARTER(DateAdded)
ORDER BY YEAR(DateAdded), QUARTER(DateAdded)
) p #periods
LEFT JOIN computers c
ON p.period>=CONCAT(YEAR(c.DateAdded),QUARTER(c.DateAdded))
GROUP BY p.period
更新根据您的最新评论,可以进行以下修改:
http://sqlfiddle.com/#!9/0c323e/5
SELECT
p.year,
p.quarter,
COUNT(DISTINCT c.id)
FROM (
SELECT
y.year,
q.quarter,
CONCAT(y.year,q.quarter) period
FROM (SELECT 2012 AS year UNION SELECT 2013 ) y
LEFT JOIN (SELECT 1 AS quarter UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) q
ON 1
) p #periods
LEFT JOIN computers c
ON p.period>=CONCAT(YEAR(c.DateAdded),QUARTER(c.DateAdded))
AND p.period<=CONCAT(YEAR(c.LastContact),QUARTER(c.LastContact))
GROUP BY p.period
如果需要限制期限,可以在
WHERE
之前添加GROUP BY p.period
子句,如下所示:WHERE p.period<='20132'
http://sqlfiddle.com/#!9/0c323e/7