--测试数据 IF NOT OBJECT_ID(N'Tempdb..#a') IS NULL DROP TABLE #a GO CREATE TABLE #a ( date1 DATETIME, c NUMERIC(18, 2), ) DECLARE @date1 DATETIME SET @date1 = '2016-5-1' WHILE @date1 <= '2016-10-1' BEGIN INSERT INTO #a VALUES ( @date1, 30 ) SET @date1 = DATEADD(DAY, 1, @date1) END--测试数据结束 SELECT * FROM #a AS aSELECT a1.[year], a1.[month], a1.xun, AVG(a1.c) AS avgc FROM ( SELECT YEAR(date1) AS YEAR, MONTH(date1) AS MONTH, DAY(date1) AS DAY, c, CASE WHEN DATEPART(DAY, a.date1) BETWEEN 1 AND 10 THEN '上旬' WHEN DATEPART(DAY, a.date1) BETWEEN 11 AND 20 THEN '中旬' ELSE '下旬' END AS xun FROM #a AS a ) a1 GROUP BY a1.[year], a1.[month], a1.xun
--测试数据 IF NOT OBJECT_ID(N'Tempdb..#a') IS NULL DROP TABLE #a GO CREATE TABLE #a ( date1 DATETIME, c NUMERIC(18, 2), ) DECLARE @date1 DATETIME SET @date1 = '2016-5-1' WHILE @date1 <= '2016-6-1' BEGIN INSERT INTO #a VALUES ( @date1, 30 ) SET @date1 = DATEADD(hour, 1, @date1) END--测试数据结束 SELECT * FROM #a AS aSELECT a1.[year], a1.[month], a1.xun, AVG(a1.c) AS avgc FROM ( SELECT YEAR(date1) AS YEAR, MONTH(date1) AS MONTH, DAY(date1) AS DAY, c, CASE WHEN DATEPART(DAY, a.date1) BETWEEN 1 AND 10 THEN '上旬' WHEN DATEPART(DAY, a.date1) BETWEEN 11 AND 20 THEN '中旬' ELSE '下旬' END AS xun FROM #a AS a ) a1 GROUP BY a1.[year], a1.[month], a1.xun
疏忽了,脚本也要改下,你看这个就好了 --测试数据 IF NOT OBJECT_ID(N'Tempdb..#a') IS NULL DROP TABLE #a GO CREATE TABLE #a ( date1 DATETIME, c NUMERIC(18, 2), ) DECLARE @date1 DATETIME SET @date1 = '2016-5-1' WHILE @date1 <= '2016-6-1' BEGIN INSERT INTO #a VALUES ( @date1, 30 ) SET @date1 = DATEADD(hour, 1, @date1) END--测试数据结束 SELECT * FROM #a AS aSELECT a1.[year], a1.[month], a1.xun, AVG(a1.c) AS avgc FROM ( SELECT YEAR(date1) AS YEAR, MONTH(date1) AS MONTH, DAY(date1) AS DAY, DATEPART(hh,date1) AS hour, c, CASE WHEN DATEPART(DAY, a.date1) BETWEEN 1 AND 10 THEN '上旬' WHEN DATEPART(DAY, a.date1) BETWEEN 11 AND 20 THEN '中旬' ELSE '下旬' END AS xun FROM #a AS a ) a1 GROUP BY a1.[year], a1.[month], a1.xun
--测试数据
IF NOT OBJECT_ID(N'Tempdb..#a') IS NULL
DROP TABLE #a
GO
CREATE TABLE #a
(
date1 DATETIME,
c NUMERIC(18, 2),
)
DECLARE @date1 DATETIME
SET @date1 = '2016-5-1'
WHILE @date1 <= '2016-10-1'
BEGIN
INSERT INTO #a
VALUES
(
@date1,
30
)
SET @date1 = DATEADD(DAY, 1, @date1)
END--测试数据结束
SELECT *
FROM #a AS aSELECT a1.[year],
a1.[month],
a1.xun,
AVG(a1.c) AS avgc
FROM (
SELECT YEAR(date1) AS YEAR,
MONTH(date1) AS MONTH,
DAY(date1) AS DAY,
c,
CASE
WHEN DATEPART(DAY, a.date1) BETWEEN 1
AND 10 THEN '上旬'
WHEN DATEPART(DAY, a.date1) BETWEEN 11 AND 20 THEN '中旬'
ELSE '下旬'
END AS xun
FROM #a AS a
) a1
GROUP BY
a1.[year],
a1.[month],
a1.xun
IF NOT OBJECT_ID(N'Tempdb..#a') IS NULL
DROP TABLE #a
GO
CREATE TABLE #a
(
date1 DATETIME,
c NUMERIC(18, 2),
)
DECLARE @date1 DATETIME
SET @date1 = '2016-5-1'
WHILE @date1 <= '2016-6-1'
BEGIN
INSERT INTO #a
VALUES
(
@date1,
30
)
SET @date1 = DATEADD(hour, 1, @date1)
END--测试数据结束
SELECT *
FROM #a AS aSELECT a1.[year],
a1.[month],
a1.xun,
AVG(a1.c) AS avgc
FROM (
SELECT YEAR(date1) AS YEAR,
MONTH(date1) AS MONTH,
DAY(date1) AS DAY,
c,
CASE
WHEN DATEPART(DAY, a.date1) BETWEEN 1
AND 10 THEN '上旬'
WHEN DATEPART(DAY, a.date1) BETWEEN 11 AND 20 THEN '中旬'
ELSE '下旬'
END AS xun
FROM #a AS a
) a1
GROUP BY
a1.[year],
a1.[month],
a1.xun
--测试数据
IF NOT OBJECT_ID(N'Tempdb..#a') IS NULL
DROP TABLE #a
GO
CREATE TABLE #a
(
date1 DATETIME,
c NUMERIC(18, 2),
)
DECLARE @date1 DATETIME
SET @date1 = '2016-5-1'
WHILE @date1 <= '2016-6-1'
BEGIN
INSERT INTO #a
VALUES
(
@date1,
30
)
SET @date1 = DATEADD(hour, 1, @date1)
END--测试数据结束
SELECT *
FROM #a AS aSELECT a1.[year],
a1.[month],
a1.xun,
AVG(a1.c) AS avgc
FROM (
SELECT YEAR(date1) AS YEAR,
MONTH(date1) AS MONTH,
DAY(date1) AS DAY,
DATEPART(hh,date1) AS hour,
c,
CASE
WHEN DATEPART(DAY, a.date1) BETWEEN 1
AND 10 THEN '上旬'
WHEN DATEPART(DAY, a.date1) BETWEEN 11 AND 20 THEN '中旬'
ELSE '下旬'
END AS xun
FROM #a AS a
) a1
GROUP BY
a1.[year],
a1.[month],
a1.xun