2 張 SQL Table 用 GROUP BY 串起來

sql group by

fastmove 2019-04-26 14:35:57 ‧ 10639 瀏覽

分享至

小弟很偶爾才會寫一些 SQL 查詢式，還不是很熟，有以下問題想請教...

現在有2張表，一張是 inventory，另一張是 order

inventory：
品名倉別庫存數量
玩偶 - 台北 --- 30
牙刷 - 台北 --- 40
茶杯 - 台北 --- 50
牙刷 - 台中 --- 60
茶杯 - 台中 --- 70

order：
訂單號品名倉別預定出貨數量
01 - 茶杯 - 台北 --- 15
02 - 牙刷 - 台北 --- 12
03 - 茶杯 - 台北 --- 8
04 - 牙刷 - 台中 --- 9
05 - 牙刷 - 台北 --- 10

我想以 inventory 這張表為主體，加上一欄是 order 表的 group by 結果：

品名倉別庫存數量預定出貨數量
玩偶 - 台北 - 30 ----- 0
牙刷 - 台北 - 40 ----- 22
茶杯 - 台北 - 50 ----- 23
牙刷 - 台中 - 60 ----- 9
茶杯 - 台中 - 70 ----- 0

看起來很簡單，寫起來也一下子就寫完了，但產出結果後比對了一下，發現有個 bug，一直解不掉 XD
"預定出貨數量" 老是會被重複計算....
也就是茶杯只有台北倉要出貨 23 個，但會變成台中倉也會顯出要出貨 23 個
以下是小弟寫的查詢式，還請各位高手不吝指正....

SELECT inventory.品名, inventory.倉別, inventory.庫存數量, SUM(order.預定出貨數量)
FROM inventory
INNER JOIN order ON inventory.品名 = order.品名
GROUP BY inventory.品名, inventory.倉別, inventory.庫存數量

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

小魚

iT邦大師 1 級 ‧ 2019-04-26 14:49:37

最佳解答

1.通常我們不會直接拿產品名稱來做關聯, 會給他一個代號
2.你用 INNER JOIN 怎麼有辦法顯示出0的資料出來呢?
3.SQL的系統很多, 你要說明一下你用的是哪種SQL

我假設你用的是MS-SQL好了,
以下語法沒有直接用資料驗證,
所以可能會有點誤差,
你就試試看吧.

SELECT a.品名, a.倉別, a.庫存數量, ISNULL(SUM(b.預定出貨數量), 0) AS sum
FROM inventory AS a
LEFT JOIN order AS b ON a.品名 = b.品名 AND a.倉別 = b.倉別
GROUP BY a.品名, a.倉別, a.庫存數量

你的問題可能是出在 ON a.品名 = b.品名 AND a.倉別 = b.倉別
原因請自己想.
不過說真的,
這個資料庫結構設計不是很好就是了...
主要問題是出在我上面說的第1點...

回應 2
分享
檢舉

fastmove iT邦新手 5 級 ‧ 2019-04-26 15:51:39 檢舉

是的，因為我是把表格簡化後才上來發問的(讓解答者能容易理解)，所以結構設計怪怪的，真的不好意思。

很感謝小魚大的幫忙，我就是少了這個 "a.倉別 = b.倉別" 的條件式... 因為表格很大，欄位對來對去頭就暈了 Orz

再次感謝！問題已解決！

小魚 iT邦大師 1 級 ‧ 2019-04-26 16:05:03 檢舉

會需要用到兩個欄位去Join,
這個設計感覺不是很好,
或是我們會建兩張表,
一張表放產品,
一張表放區域,
然後Join產品這張表,
同時Join區域這張表,
通常區域一定是 Inner Join,
產品有可能會 Left Join.

登入發表回應

Zed_Yang

iT邦新手 3 級 ‧ 2019-04-26 15:03:03

DROP TABLE #inventory
CREATE TABLE #inventory
	(品名 nvarchar(6), 倉別 varchar(4), 庫存數量 int)
;

INSERT INTO #inventory
	(品名, 倉別, 庫存數量)
VALUES
	('玩偶', '台北', 30),
	('牙刷', '台北', 40),
	('茶杯', '台北', 50),
	('牙刷', '台中', 60),
	('茶杯', '台中', 70)

DROP TABLE #ordertable
CREATE TABLE #ordertable
	(訂單號 int, 品名 varchar(6), 倉別 varchar(4) , 預定出貨數量 int)
;

INSERT INTO #ordertable
	(訂單號, 品名, 倉別,預定出貨數量)
VALUES
	(01,'茶杯', '台北', 15),
	(02,'牙刷', '台北', 12),
	(03,'茶杯', '台北', 8),
	(04,'牙刷', '台中', 9),
	(05,'牙刷', '台北', 10)


 	 select A.品名 , A.倉別 ,A.庫存數量 ,SUM(ISNULL(B.預定出貨數量,0))總共出貨數量
	FROM #inventory A
 LEFT JOIN #ordertable B ON B.倉別 = A.倉別 AND B.品名 = A.品名
 GROUP BY A.品名 , A.倉別 ,A.庫存數量
 ORDER BY 倉別,品名

回應 1
分享
檢舉

fastmove iT邦新手 5 級 ‧ 2019-04-26 15:52:43 檢舉

非常謝謝您的協助！問題已經解決了 : )

登入發表回應

iT邦新手 2 級 ‧ 2019-04-29 09:16:31

提供另一個解決方式(subquery)，DB-->MYSQL

SELECT odr.item,odr.loc,sum(odrqty),(select sum(qty) from inv where inv.item=odr.item and inv.loc=odr.loc) FROM odr group by item,loc

-- 資料表結構 inv
CREATE TABLE inv (
item varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
loc varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
qty int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 資料表的匯出資料 inv
INSERT INTO inv (item, loc, qty) VALUES
('玩偶', '台北', 30),
('牙刷', '台北', 40),
('茶杯', '台北', 50),
('牙刷', '台中', 60),
('茶杯', '台中', 70);

-- 資料表結構 odr
CREATE TABLE odr (
odr int(11) NOT NULL,
item varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
loc varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
odrqty int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 資料表的匯出資料 odr
INSERT INTO odr (odr, item, loc, odrqty) VALUES
(1, '茶杯', '台北', 15),
(2, '牙刷', '台北', 12),
(3, '茶杯', '台北', 8),
(4, '牙刷', '台中', 9),
(5, '牙刷', '台北', 10);