SQL分组查询简单示例代码

【核心代码】

--我这定义的是零时表变量

DECLARE @History TABLE(
     Id INT IDENTITY(1,1)           --Id自增字段
    ,[Date] SMALLDATETIME           --日期
    ,[Temperature] NUMERIC(20,10)   --温度,表示十进制数长度为20位其中小数位10位
)

INSERT INTO @History([Date],[Temperature])
      SELECT '2010-01-01',-8
UNION SELECT '2010-01-02',2
UNION SELECT '2010-02-01',14
UNION SELECT '2010-02-02',16
UNION SELECT '2010-12-31',12
UNION SELECT '2012-01-01',-9
UNION SELECT '2012-01-02',0
UNION SELECT '2012-02-01',10
UNION SELECT '2012-02-02',14
UNION SELECT '2012-02-29',19
UNION SELECT '2013-01-02',2
UNION SELECT '2013-02-01',12
UNION SELECT '2013-02-02',16
UNION SELECT '2013-02-28',19

--SELECT * FROM @History
--按年份 直接查询
SELECT YEAR([Date]) AS [Year]
    ,CASE WHEN (YEAR([Date]) % 4 = 0 AND YEAR([Date]) % 100 <> 0) OR YEAR([Date]) % 400 = 0 THEN 1 ELSE 0 END IsLeapYear --是否是闰年
    ,AVG([Temperature]) AS [AverageTemperature]
FROM @History
GROUP BY YEAR([Date]) --按年分组

-- 按年份 子查询模式
SELECT 
    [Year]
    ,CASE WHEN ([Year] % 4 = 0 AND [Year] % 100 <> 0) OR [Year] % 400 = 0 THEN 1 ELSE 0 END IsLeapYear --是否是闰年
    ,[AverageTemperature]
FROM
(
SELECT YEAR([Date]) AS [Year]
    ,AVG([Temperature]) AS [AverageTemperature]
FROM @History
GROUP BY YEAR([Date])
)
AS V

----------------------------万恶的分割线----------------------------------

--按 闰年/平年   直接查询
SELECT CASE WHEN (YEAR([Date]) % 4 = 0 AND YEAR([Date]) % 100 <> 0) OR YEAR([Date]) % 400 = 0 THEN 1 ELSE 0 END LeapYear --是否是闰年
    ,AVG([Temperature]) AS [AverageTemperature]
FROM @History
GROUP BY CASE WHEN (YEAR([Date]) % 4 = 0 AND YEAR([Date]) % 100 <> 0) OR YEAR([Date]) % 400 = 0 THEN 1 ELSE 0 END

--按 闰年/平年   子查询模式
SELECT 
    LeapYear
    ,AVG([Temperature]) AS [AverageTemperature]
FROM
(
    SELECT CASE WHEN (YEAR([Date]) % 4 = 0 AND YEAR([Date]) % 100 <> 0) OR YEAR([Date]) % 400 = 0 THEN 1 ELSE 0 END LeapYear --是否是闰年
    ,[Temperature]
    FROM @History
)
AS V
GROUP BY LeapYear