我不确定表达这个问题的正确方法是什么,但我希望这是有意义的。我有一个查询:

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

10-06 08:50
查看更多