按日期分组，按月、年分列

Question

我这里有一个数据样品。我曾经问过这里，哪个是有效的，但是当我对它进行了更深入的测试时，我忘记了一个事实，那就是我也应该对Year进行解释。

所以简单地说，我想把记录按月范围分组。Aug至Jan (1组)，Feb至July (2组)。因为我想知道每6个月有多少学校物体被创建。一个持续的趋势是具体的。

结果集，如：

+-----------+-------+---------------+
| sc_object | count | range         |
+-----------+-------+---------------+
| pencil    | 1     | 2013-8 2014-1 |
| eraser    | 1     | 2014-2 2014-7 |
| pencil    | 1     | 2014-2 2014-7 |
| sharpener | 1     | 2014-2 2014-7 |
| pencil    | 1     | 2014-8 2015-1 |
| eraser    | 1     | 2015-2 2015-7 |
| pencil    | 1     | 2015-2 2015-7 | 
| sharpener | 1     | 2015-2 2015-7 |
| eraser    | 1     | 2015-8 2016-1 | 
| sharpener | 2     | 2016-2 2016-7 | 
| pencil    | 1     | 2016-2 2016-7 |
| sharpener | 1     | 2016-8 2017-1 |
| eraser    | 1     | 2016-8 2017-1 |
+-----------+-------+---------------+

任何帮助都将是非常感谢的。非常感谢!

Answer 1

您可以这样做(http://www.sqlfiddle.com/#!3/41ac6/19)：

WITH cteRanges
AS
(
    SELECT
            site,
            CASE
              WHEN MONTH(date) IN (1) THEN CAST(YEAR(date) - 1 AS VARCHAR(4)) + '-8' + ' ' + CAST(YEAR(date) AS VARCHAR(4)) + '-1'
              WHEN MONTH(date) IN (8, 9, 10, 11, 12) THEN CAST(YEAR(date) AS VARCHAR(4)) + '-8' + ' ' + CAST(YEAR(date) + 1 AS VARCHAR(4)) + '-1'
              ELSE CAST(YEAR(date) AS VARCHAR(4)) + '-2' + ' ' + CAST(YEAR(date) AS VARCHAR(4)) + '-7'
            END AS range
    FROM
            test
)

SELECT
          site,
          COUNT(*),
          range
FROM
          cteRanges
GROUP BY
          site,
          range
ORDER BY
          range,
          site;

根据月份和年份计算范围，然后汇总结果。