我需要有關 SQL 查詢的幫助

我不太擅長 SQL 查詢，想知道是否有人知道正確的查詢以返回以下資訊。我很難弄清楚這一點。我正在使用 Microsoft SQL Server 2012。

範例數據庫數據

ID   | DATE                | SHIFT#| LINE# | TOTAL | UPTIME (seconds)
1    | 2019-03-13 10:31:29 | 1     | 1     | 345   | 600
2    | 2019-03-13 10:31:39 | 1     | 2     | 254   | 400
3    | 2019-03-13 10:31:49 | 1     | 3     | 198   | 550
4    | 2019-03-13 10:41:29 | 1     | 1     | 300   | 500
5    | 2019-03-13 10:51:39 | 1     | 2     | 154   | 440
6    | 2019-03-13 10:54:49 | 1     | 3     | 298   | 520
7    | 2019-03-13 15:21:19 | 2     | 1     | 100   | 450
8    | 2019-03-13 15:21:29 | 2     | 2     | 330   | 360
9    | 2019-03-13 15:21:39 | 2     | 3     | 400   | 450
10   | 2019-03-13 15:41:19 | 2     | 1     | 200   | 450
11   | 2019-03-13 15:41:29 | 2     | 2     | 300   | 350
12   | 2019-03-13 15:41:39 | 2     | 3     | 400   | 450
13   | 2019-03-14 10:42:09 | 1     | 1     | 345   | 600
14   | 2019-03-14 10:42:19 | 1     | 2     | 254   | 400
15   | 2019-03-14 10:42:29 | 1     | 3     | 198   | 550
16   | 2019-03-14 10:44:09 | 1     | 1     | 340   | 500
17   | 2019-03-14 10:44:19 | 1     | 2     | 204   | 410
18   | 2019-03-14 10:44:29 | 1     | 3     | 178   | 450
19   | 2019-03-14 15:32:29 | 2     | 1     | 200   | 450
20   | 2019-03-14 15:32:39 | 2     | 2     | 300   | 350
21   | 2019-03-14 15:32:49 | 2     | 3     | 400   | 450
22   | 2019-03-14 15:36:29 | 2     | 1     | 210   | 250
23   | 2019-03-14 15:36:39 | 2     | 2     | 320   | 355
24   | 2019-03-14 15:36:49 | 2     | 3     | 450   | 455

我希望查詢的結果是每個DATE,SHIFT#和一行LINE#。需要包括SUM欄位的TOTAL和欄位SUM的。UPTIME我試圖讓它看起來像這樣：

返回的記錄格式

DATE       | SHIFT# | LINE# | SUM TOTAL | SUM UPTIME
2019-03-13 | 1      | 1     | 645       | 1100
2019-03-13 | 1      | 2     | 399       | 840
2019-03-13 | 1      | 3     | 494       | 1070
2019-03-13 | 2      | 1     | 300       | 900
2019-03-13 | 2      | 2     | 630       | 710
2019-03-13 | 2      | 3     | 800       | 900
2019-03-14 | 1      | 1     | 685       | 1100
2019-03-14 | 1      | 2     | 458       | 810
2019-03-14 | 1      | 3     | 376       | 1000
2019-03-14 | 2      | 1     | 410       | 700
2019-03-14 | 2      | 2     | 620       | 705
2019-03-14 | 2      | 3     | 850       | 905

您只需要一個帶有聚合和GROUP BY的基本SELECT。您可以使用CAST函式截斷以僅使用該部分：DATETIME``DATE

SELECT CAST([DATE] AS DATE) AS [DATE]
    , [SHIFT]
    , [LINE#]
    , SUM([TOTAL]) AS [SUM TOTAL]
    , SUM([UPTIME (seconds)]) AS [UPTIME]
 FROM MyTable
GROUP BY CAST([DATE] AS DATE), [SHIFT], [LINE#]
ORDER BY CAST([DATE] AS DATE), [SHIFT], [LINE#];

查看文件中的範例。GROUP BY

這是一個似乎可以提供您想要的結果的解決方案。

--demo setup
drop table if exists table1
go
CREATE TABLE table1 (
 ID INTEGER,
 DATE VARCHAR(19),
 SHIFT# INTEGER,
 LINE# INTEGER,
 TOTAL INTEGER,
 UPTIME  INTEGER
);

INSERT INTO table1
 (ID, DATE, SHIFT#, LINE#, TOTAL, UPTIME )
VALUES
 ('1', '2019-03-13 10:31:29', '1', '1', '345', '600'),
 ('2', '2019-03-13 10:31:39', '1', '2', '254', '400'),
 ('3', '2019-03-13 10:31:49', '1', '3', '198', '550'),
 ('4', '2019-03-13 10:41:29', '1', '1', '300', '500'),
 ('5', '2019-03-13 10:51:39', '1', '2', '154', '440'),
 ('6', '2019-03-13 10:54:49', '1', '3', '298', '520'),
 ('7', '2019-03-13 15:21:19', '2', '1', '100', '450'),
 ('8', '2019-03-13 15:21:29', '2', '2', '330', '360'),
 ('9', '2019-03-13 15:21:39', '2', '3', '400', '450'),
 ('10', '2019-03-13 15:41:19', '2', '1', '200', '450'),
 ('11', '2019-03-13 15:41:29', '2', '2', '300', '350'),
 ('12', '2019-03-13 15:41:39', '2', '3', '400', '450'),
 ('13', '2019-03-14 10:42:09', '1', '1', '345', '600'),
 ('14', '2019-03-14 10:42:19', '1', '2', '254', '400'),
 ('15', '2019-03-14 10:42:29', '1', '3', '198', '550'),
 ('16', '2019-03-14 10:44:09', '1', '1', '340', '500'),
 ('17', '2019-03-14 10:44:19', '1', '2', '204', '410'),
 ('18', '2019-03-14 10:44:29', '1', '3', '178', '450'),
 ('19', '2019-03-14 15:32:29', '2', '1', '200', '450'),
 ('20', '2019-03-14 15:32:39', '2', '2', '300', '350'),
 ('21', '2019-03-14 15:32:49', '2', '3', '400', '450'),
 ('22', '2019-03-14 15:36:29', '2', '1', '210', '250'),
 ('23', '2019-03-14 15:36:39', '2', '2', '320', '355'),
 ('24', '2019-03-14 15:36:49', '2', '3', '450', '455');

--the solution
;WITH _cte
AS (
   SELECT convert(DATE, [date]) AS [date]
       ,[shift#]
       ,[line#]
       ,total
       ,uptime
   FROM table1
   )
SELECT [date]
   ,[shift#]
   ,[line#]
   ,sum(total) as [SUM TOTAL]
   ,sum(uptime) as [SUM UPTIME]
FROM _cte
GROUP BY [DATE]
   ,[SHIFT#]
   ,[line#]

---

| date       | shift# | line# | SUM TOTAL        | SUM UPTIME       |
|------------|--------|-------|------------------|------------------|
| 2019-03-13 | 1      | 1     | 645              | 1100             |
| 2019-03-13 | 1      | 2     | 408              | 840              |
| 2019-03-13 | 1      | 3     | 496              | 1070             |
| 2019-03-13 | 2      | 1     | 300              | 900              |
| 2019-03-13 | 2      | 2     | 630              | 710              |
| 2019-03-13 | 2      | 3     | 800              | 900              |
| 2019-03-14 | 1      | 1     | 685              | 1100             |
| 2019-03-14 | 1      | 2     | 458              | 810              |
| 2019-03-14 | 1      | 3     | 376              | 1000             |
| 2019-03-14 | 2      | 1     | 410              | 700              |
| 2019-03-14 | 2      | 2     | 620              | 705              |
| 2019-03-14 | 2      | 3     | 850              | 905              |

引用自：https://dba.stackexchange.com/questions/232465