比如表有2個欄位datetime、data
2020-12-28 23:59:59 5
2020-12-29 00:29:59 20
2020-12-29 06:29:59 100
2020-12-29 06:59:59 200
2020-12-29 07:29:59 260
.........
2020-12-29 09:29:59 360
.......
2020-12-29 14:29:59 560
........
2020-12-29 18:59:59 760
........
2020-12-29 23:59:59 1000
data資料是遞增的，每半小時一條記錄,上面只是1天的記錄
現在要查每個月每天00:00-7:30,7:30-9:30,9:30-14:30,14:30-19:00,19:00-00:00這幾個時段的累積量,即
00:00-7:30 7:30-9:30 9:30-14:30 14:30-19:00 19:00-00:00
2020-12-01 ..... .... .... ...... .........
.........
2020-12-28 ..... .... .... ...... .........
2020-12-29 255 100 200 200 240
請問用sql陳述句怎么實作，謝謝
uj5u.com熱心網友回復：
CREATE TABLE #t
(
a DATETIME,
b INT
)
INSERT INTO #T VALUES('2020-12-28 23:59:59', 5)
INSERT INTO #T VALUES('2020-12-29 00:29:59', 20)
INSERT INTO #T VALUES('2020-12-29 06:29:59', 100)
INSERT INTO #T VALUES('2020-12-29 06:59:59', 200)
INSERT INTO #T VALUES('2020-12-29 07:29:59', 260)
INSERT INTO #T VALUES('2020-12-29 09:29:59', 360)
INSERT INTO #T VALUES('2020-12-29 14:29:59', 560)
INSERT INTO #T VALUES('2020-12-29 18:59:59', 760)
INSERT INTO #T VALUES('2020-12-29 23:59:59', 1000)
--sql2012
SELECT a1,
SUM(CASE WHEN a2 >='00:00' AND a2<'07:30' THEN b ELSE 0 END) AS '00:00-07:30',
SUM(CASE WHEN a2 >='07:30' AND a2<'09:30' THEN b ELSE 0 END) AS '07:30-09:30',
SUM(CASE WHEN a2 >='09:30' AND a2<'14:30' THEN b ELSE 0 END) AS '09:30-14:30',
SUM(CASE WHEN a2 >='14:30' AND a2<'19:00' THEN b ELSE 0 END) AS '14:30-19:00',
SUM(CASE WHEN a2 >='19:00' THEN b ELSE 0 END) AS '19:00-00:00'
FROM
(
SELECT CONVERT(VARCHAR(10),a,121) AS a1,CONVERT(VARCHAR(5),a,108) AS a2,b-ISNULL( LAG(b) OVER(ORDER BY a),0) AS b FROM #t
) a
GROUP BY a1
--sql2008
;
WITH ct
AS
(
SELECT * ,ROW_NUMBER() OVER(ORDER BY a) as id FROM #t
)
SELECT a1,
SUM(CASE WHEN a2 >='00:00' AND a2<'07:30' THEN b ELSE 0 END) AS '00:00-07:30',
SUM(CASE WHEN a2 >='07:30' AND a2<'09:30' THEN b ELSE 0 END) AS '07:30-09:30',
SUM(CASE WHEN a2 >='09:30' AND a2<'14:30' THEN b ELSE 0 END) AS '09:30-14:30',
SUM(CASE WHEN a2 >='14:30' AND a2<'19:00' THEN b ELSE 0 END) AS '14:30-19:00',
SUM(CASE WHEN a2 >='19:00' THEN b ELSE 0 END) AS '19:00-00:00'
FROM
(
SELECT CONVERT(VARCHAR(10),a.a,121) AS a1,CONVERT(VARCHAR(5),a.a,108) AS a2,a.b-ISNULL(b.b,0) AS b
FROM ct a LEFT JOIN ct b ON b.id = a.id -1
) a
GROUP BY a1
DROP TABLE #t
uj5u.com熱心網友回復：
CREATE TABLE #A(
DTIME DATETIME,
DATA INT
)
INSERT INTO #A VALUES ('2020-12-28 23:59:59',5)
INSERT INTO #A VALUES ('2020-12-29 00:29:59',20)
INSERT INTO #A VALUES ('2020-12-29 06:29:59',100)
INSERT INTO #A VALUES ('2020-12-29 06:59:59',200)
INSERT INTO #A VALUES ('2020-12-29 07:29:59',260)
INSERT INTO #A VALUES ('2020-12-29 09:29:59',360)
INSERT INTO #A VALUES ('2020-12-29 14:29:59',560)
INSERT INTO #A VALUES ('2020-12-29 18:59:59',760)
INSERT INTO #A VALUES ('2020-12-29 23:59:59',1000)
SELECT CONVERT(DATE,A.DTIME,21) [DATETIME],
----把這段復制五遍
ISNULL(((SELECT TOP 1 B.DATA FROM #A B
WHERE B.DTIME<(CONVERT(VARCHAR,CONVERT(DATE,A.DTIME,21))+' 07:30:00')
ORDER BY B.DTIME DESC)
-(SELECT TOP 1 B.DATA FROM #A B
WHERE B.DTIME<(CONVERT(VARCHAR,CONVERT(DATE,A.DTIME,21))+' 00:00:00')
ORDER BY B.DTIME DESC)),0) '0:00-7:30'
----
FROM #A A
GROUP BY CONVERT(DATE,A.DTIME,21)
DROP TABLE #A
這個寫法好像不太行...看著就麻煩
uj5u.com熱心網友回復：
1#的已經OK，另一種思路：用pivot篩選并轉置行列，再減前一個的資料
DECLARE @t TABLE ( dt DATETIME NOT NULL, val INT NOT NULL)
INSERT INTO @t (dt,val) VALUES('2020-12-28 23:59:59', 5)
,('2020-12-29 00:29:59', 20),('2020-12-29 06:29:59', 100)
,('2020-12-29 06:59:59', 200),('2020-12-29 07:29:59', 260)
,('2020-12-29 09:29:59', 360),('2020-12-29 14:29:59', 560)
,('2020-12-29 18:59:59', 760),('2020-12-29 23:59:59', 1000)
SELECT p.Date,COALESCE(p.[07:29] - LAG([23:59],1,0) OVER (ORDER BY p.Date),0) AS [00:00-07:30],
ISNULL(p.[09:29],0) - ISNULL(p.[07:29],0) AS [07:30-09:30],
ISNULL(p.[14:29],0) - isnull(p.[09:29],0) AS [09:30-14:30],
ISNULL(p.[18:59],0) - ISNULL(p.[14:29],0) AS [14:30-19:00],
ISNULL(p.[23:59],0) - ISNULL(p.[18:59],0) AS [19:00-00:00]
FROM (
SELECT CAST(dt AS DATE) AS [Date], CONVERT(VARCHAR(5), dt,108) AS [Time], val FROM @t) t
PIVOT (SUM(val) FOR t.Time IN ([07:29],[09:29],[14:29],[18:59],[23:59])) p
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/242613.html
標籤：疑難問題
上一篇：查詢結果如何按條件分頁問題