我正在嘗試添加每臺機器參考的小時數。我必須查看三個表才能獲得正確的最新資料。我能夠獲得需要添加的小時數串列。
但是，我嘗試了各種方法來使用 SUM() 函式，但它總是給我帶來聚合錯誤。
這是 SQL 代碼：
SELECT
(SELECT TOP 1 change.hours
FROM change WHERE change.id = part.id
ORDER BY change.timeStamp DESC) as 'Hours'
FROM change
INNER JOIN part ON (part.id = change.id)
INNER JOIN completed ON (part.id = completed.id)
WHERE part.id NOT IN (SELECT completed.id FROM completed WHERE completed.completed = 1)
and (SELECT TOP 1 change.machine FROM change WHERE part.id = change.id ORDER BY change.timeStamp DESC ) = :machine
GROUP BY change.id, part.id
我基本上需要單細胞結果，這將增加每臺機器的所有小時數。結果將顯示在 GUI 上。
結果應該是 機器 1 必須是 12 小時 機器 2 必須是 18 小時 機器 3 必須是 18 小時
表
部分
| ID | 數字 | 描述 | 作業 |
|---|---|---|---|
| 14 | 40023-10-100-10-03 | 根據 | 40023 |
| 15 | 40023-10-200-10-03 | 根據 | 40023 |
| 16 | 40024-10-100-10-01 | 傳感器支架 | 40024 |
| 17 | 40024-10-100-10-02 | 邊 | 40024 |
| 18 | 40025-10-100-10-01 | 傳送帶保持 | 40025 |
| 19 | 40025-10-200-00-01 | 部分 | 40025 |
| 20 | 40026-10-400-00-01 | 電機安裝座 | 40026 |
| 21 | 40026-10-200-10-10 | 三角臂 | 40026 |
| 22 | 40023-10-200-10-03 | 根據 | 40023 |
改變
| ID | 數量 | 小時 | 機器 | 操作員 | 開始時間 | 停止時間 | 完全的 | date | timeStamp |
|---|---|---|---|---|---|---|---|---|---|
| 14 | 0 | 0 | 2 | 2 | NULL | NULL | False | NULL | 2021-10-28 00:00:00.000 |
| 15 | 0 | 0 | 4 | 3 | NULL | NULL | False | NULL | 2021-10-28 11:01:41.427 |
| 19 | 0 | 0 | 3 | 1 | NULL | NULL | False | NULL | 2021-10-28 11:10:50.730 |
| 18 | 0 | 0 | 2 | 3 | NULL | NULL | False | NULL | 2021-10-28 11:13:46.213 |
| 16 | 3 | 2.5 | 2 | 2 | NULL | NULL | False | 2021-10-27 | 2021-10-28 13:41:12.393 |
| 16 | 3 | 2.5 | 2 | 2 | NULL | NULL | False | 2021-10-27 | 2021-10-28 13:41:12.393 |
| 15 | 1 | 9 | 3 | 3 | NULL | NULL | True | 2021-10-29 | 2021-10-28 21:38:44.883 |
| 14 | 0 | 0 | 1 | 1 | NULL | NULL | False | NULL | 2021-11-01 10:36:43.223 |
| 14 | 0 | 0 | 1 | 1 | NULL | NULL | False | NULL | 2021-11-01 10:37:47.153 |
| 16 | 1 | 0.5 | 2 | 2 | NULL | NULL | False | 2021-11-01 | 2021-11-01 11:12:06.840 |
| 21 | 0 | 0 | 1 | 1 | NULL | NULL | False | NULL | 2021-11-01 11:45:30.050 |
| 20 | 0 | 0 | 2 | 3 | NULL | NULL | False | NULL | 2021-11-10 10:44:00.000 |
| 23 | 0 | 0 | 0 | 0 | NULL | NULL | True | 2021-11-02 | 2021-11-02 16:26:18.583 |
| 16 | 1 | 1 | 2 | 2 | NULL | NULL | False | 2021-11-01 | 2021-11-01 11:03:44.160 |
| 17 | 0 | 0 | 2 | 2 | NULL | NULL | False | NULL | 2021-10-28 11:25:03.967 |
| 17 | 0 | 0 | 1 | 1 | NULL | NULL | False | NULL | 2021-11-01 10:40:36.850 |
| 17 | 0 | 0 | 1 | 1 | NULL | NULL | False | NULL | 2021-11-01 10:42:56.350 |
| 22 | 0 | 0 | 3 | 2 | NULL | NULL | False | NULL | 2021-11-02 11:58:08.360 |
| 17 | 0 | 0 | 1 | 2 | NULL | NULL | False | NULL | 2021-11-01 10:43:44.273 |
| 14 | 0 | 0 | 1 | 1 | NULL | NULL | False | NULL | 2021-11-01 10:44:23.440 |
| 14 | 0 | 0 | 1 | 1 | NULL | NULL | False | NULL | 2021-11-02 12:57:06.810 |
change
| id | hours | qty | machine | operator | notes | rush | timeStamp |
|---|---|---|---|---|---|---|---|
| 14 | 2 | 3 | 2 | 1 | False | 2021-10-28 10:48:54.910 | |
| 15 | 10 | 1 | 3 | 2 | False | 2021-10-28 10:49:47.643 | |
| 16 | 7 | 10 | 2 | 3 | Need material | True | 2021-10-28 10:50:33.880 |
| 17 | 4 | 2 | 1 | 1 | False | 2021-10-28 00:00:00.000 | |
| 18 | 5 | 1 | 2 | 2 | False | 2021-10-28 10:53:15.470 | |
| 19 | 8 | 3 | 3 | 3 | False | 2021-10-28 11:10:50.573 | |
| 14 | 3 | 4 | 1 | 1 | waiting for mills | False | 2021-10-29 08:12:00.000 |
| 17 | 4 | 2 | 1 | 1 | True | 2021-11-01 10:40:36.707 | |
| 17 | 4 | 2 | 1 | 1 | True | 2021-11-01 10:42:56.150 | |
| 16 | 8 | 10 | 2 | 3 | Need material | False | 2021-11-01 10:43:29.930 |
| 17 | 4 | 2 | 1 | 2 | False | 2021-11-01 10:43:44.047 | |
| 14 | 3 | 4 | 1 | 1 | False | 2021-11-01 10:44:23.317 | |
| 20 | 2 | 4 | 2 | 3 | False | 2021-11-01 11:44:10.257 | |
| 21 | 5 | 3 | 1 | 1 | Need material | True | 2021-11-01 11:45:29.927 |
| 22 | 10 | 1 | 3 | 2 | False | 2021-11-02 11:58:08.220 | |
| 14 | 3 | 4 | 1 | 1 | True | 2021-11-02 12:57:06.683 | |
| 14 | 4 | 2 | 1 | 1 | waiting for bits | False | 2021-10-29 00:00:00.000 |
| 14 | 3 | 4 | 1 | 1 | wrong mills came. Need to order another ones | False | 2021-11-01 10:36:42.997 |
| 14 | 3 | 4 | 1 | 1 | wrong mills came. Need to order another ones | False | 2021-11-01 10:37:46.983 |
uj5u.com熱心網友回復：
好吧，我不明白你是如何計算預期結果的，我相信你的查詢可以改進很多（如果你更好地解釋你的要求，我可以幫助你改進它）。同時，此查詢回傳您想要的內容：
select machine, sum(Hours) Hours from (
SELECT change.machine,
(SELECT TOP 1 change.hours
FROM change WHERE change.id = part.id
ORDER BY change.timeStamp DESC) as 'Hours'
FROM change
INNER JOIN part ON (part.id = change.id)
INNER JOIN completed ON (part.id = completed.id)
WHERE part.id NOT IN (SELECT completed.id FROM completed WHERE completed.completed = 1)
GROUP BY change.id, part.id, change.machine
) as a
group by machine
它回傳：
| 機器 | 小時 |
|---|---|
| 1 | 12 |
| 2 | 18 |
| 3 | 18 |
DBFiddle：https ://dbfiddle.uk/ ? rdbms = sqlserver_2019 & fiddle = 80ab33a349388896af3ffcb8954c56c9
uj5u.com熱心網友回復：
這是偉大的作業，卡洛斯。
我能夠添加到您的代碼中以獲得我需要的結果
有顯示哪臺機器的輸入。:machine 是輸入。
SELECT ISNULL((
SELECT sum(Hours)
FROM (SELECT
(SELECT TOP 1 change.hours
FROM change WHERE change.id = part.id
ORDER BY change.timeStamp DESC) as 'Hours'
FROM change
INNER JOIN part ON (part.id = change.id)
INNER JOIN completed ON (part.id = completed.id)
WHERE part.id NOT IN (SELECT completed.id FROM completed WHERE completed.completed = 1)
and (SELECT TOP 1 change.machine FROM change WHERE part.id = change.id ORDER BY change.timeStamp DESC) = :machine
GROUP BY change.id, part.id) as a), 0)
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/347869.html
標籤：sql sql-server sum ignition