iT邦幫忙

0

MariaDB 無搜尋結果也要列出來

先上兩個表單格式
表單名稱:產品
產品
表單名稱:工具列表
工具列表

欲達成效果:
以產品表單來得知要製作A產品,要使用到AA工具1個,AB工具2個,AC工具3個
再用這個資訊去查詢工具列表,得知
需要AA工具1個,依照工具列表內派發AA工具中,狀態=1的工具,剛好流水號01符合條件
需要AB工具2個,但是倉庫裡面沒有AB工具狀態=1,顯示null(無工具)
需要AC工具3個,倉庫裡面AC工具狀態=1的只有兩個,雖不足額但仍輸出
欲達成效果
但是目前沒辦法讓工具名稱AB的null那一行顯示出來
因為我是用狀態=1來作條件篩選

【SQL】在含有GROUP BY的SELECT語句中如何顯示COUNT()為0的結果
這篇有點類似我想達成的效果,但是我弄不出來

目前達成的效果:
目前達成效果
目前使用的SQLCommand

set @NAME='';
set @sn=1;
select if (@NAME=A.工具,@sn:=@sn+1,@sn:=1) as 在庫流水號,
產品名稱,@NAME:=A.工具 as 工具名稱,需求數量,
流水號 as 工具流水編號,狀態,位置  from `產品` as A,
(select 位置,狀態,流水號,工具 from `工具列表`)as B
where 產品名稱='A' AND A.工具=B.工具 
ORDER BY 工具名稱 ,流水號;

範例表單

-- --------------------------------------------------------
-- 主機:                           localhost
-- 服務器版本:                        10.3.9-MariaDB - mariadb.org binary distribution
-- 服務器操作系統:                      Win64
-- HeidiSQL 版本:                  9.4.0.5125
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


-- 導出 兩表為零測試 的資料庫結構
CREATE DATABASE IF NOT EXISTS `兩表為零測試` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `兩表為零測試`;

-- 導出  表 兩表為零測試.工具列表 結構
CREATE TABLE IF NOT EXISTS `工具列表` (
  `工具` varchar(50) DEFAULT NULL,
  `流水號` varchar(50) DEFAULT NULL,
  `狀態` int(11) DEFAULT NULL,
  `位置` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 正在導出表  兩表為零測試.工具列表 的資料:~9 rows (大約)
/*!40000 ALTER TABLE `工具列表` DISABLE KEYS */;
INSERT INTO `工具列表` (`工具`, `流水號`, `狀態`, `位置`) VALUES
	('AA', '1', 1, '0001'),
	('AA', '2', 1, '0003'),
	('AA', '3', 1, '0002'),
	('AB', '1', 0, '0'),
	('AB', '2', 0, '0'),
	('AB', '3', 0, '0'),
	('AC', '1', 0, '0'),
	('AC', '2', 1, '0008'),
	('AC', '3', 1, '0009');
/*!40000 ALTER TABLE `工具列表` ENABLE KEYS */;

-- 導出  表 兩表為零測試.產品 結構
CREATE TABLE IF NOT EXISTS `產品` (
  `產品名稱` varchar(50) DEFAULT NULL,
  `工具` varchar(50) DEFAULT NULL,
  `需求數量` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 正在導出表  兩表為零測試.產品 的資料:~3 rows (大約)
/*!40000 ALTER TABLE `產品` DISABLE KEYS */;
INSERT INTO `產品` (`產品名稱`, `工具`, `需求數量`) VALUES
	('A', 'AA', 1),
	('A', 'AB', 2),
	('A', 'AC', 3);
/*!40000 ALTER TABLE `產品` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

(select 位置,狀態,流水號,工具 from `工具列表`)as B <-- 這是誰教你這樣寫的.
不就4個欄位, 然後 subquery 也是4個欄位.
另外呢...知道 outer join 嗎?
rew87516 iT邦新手 5 級 ‧ 2018-12-27 12:01:31 檢舉
哈!因為是非正規的自學DB所以都是網路上剪貼而來的,請勿見怪啦~
剛試著使用inner join 果然能用更短的方式得到一樣的結果
```
set @sn=1;
set @NAME='';
select 產品名稱,需求數量,流水號,狀態,位置,
if(@NAME=A.工具,@sn:=@sn+1,@sn:=1) as 在庫流水號,@NAME:=A.`工具`as 工具名稱
from `產品` as A
inner join `工具列表` as B
on A.`工具`=B.`工具`
```
![目前效果](https://i.imgur.com/Dz94cOG.png)
1
淺水員
iT邦研究生 4 級 ‧ 2018-12-27 18:35:46
最佳解答
SELECT
	A.`產品名稱` AS `產品名稱`,
	A.`工具` AS `工具名稱`,
	A.`需求數量` AS `需求數量`,
	C.numc AS `在庫流水號`,
	C.`流水號` AS `工具流水編號`,
	C.`狀態` AS `狀態`,
	C.`位置` AS `位置`
FROM
	`產品` AS A
LEFT JOIN
	(SELECT
		`工具`,`流水號`,`狀態`,`位置`,numc
	FROM
		(SELECT @num:=0,@type:='') AS init
	JOIN
		(SELECT
			*,
			@num := IF(@type=`工具`, @num+1, 1) numc,
			@type := `工具` dummy
		FROM
			`工具列表`
		WHERE
			`狀態`=1
		ORDER BY `工具`,`流水號`) AS B) AS C
ON A.`工具`=C.`工具` AND C.numc <= A.`需求數量`
ORDER BY A.`工具`,C.`流水號`;

參考:https://mariadb.com/kb/en/library/groupwise-max-in-mariadb/#using-variables

看更多先前的回應...收起先前的回應...
rew87516 iT邦新手 5 級 ‧ 2018-12-28 08:43:48 檢舉

這正是我要的效果! 謝謝
我來研究一下:D

rew87516 iT邦新手 5 級 ‧ 2018-12-28 10:54:27 檢舉

參考您的命令,改出我要的版本了
太感謝您了!!!!

rew87516 iT邦新手 5 級 ‧ 2018-12-28 11:02:42 檢舉

只不過這段是什麼意思阿

	FROM
		(SELECT @num:=0,@type:='') AS init

FROM後面不是都是接表單名稱嗎@@?

淺水員 iT邦研究生 4 級 ‧ 2018-12-28 11:39:13 檢舉

直接執行下面這行

SELECT @num:=0,@type:='';

就會發現他也很像表單那樣顯示出來
不過在這邊目的不是使用這個表單
( init 這個名稱後面都沒用到)
而是透過這樣的方式給 @num 跟 @type 初值

其實這塊跟我給的連結很結構上很類似

SELECT
		`工具`,`流水號`,`狀態`,`位置`,numc
FROM
	(SELECT @num:=0,@type:='') AS init
JOIN
	(SELECT
		*,
		@num := IF(@type=`工具`, @num+1, 1) numc,
		@type := `工具` dummy
	FROM
		`工具列表`
	WHERE
		`狀態`=1
	ORDER BY `工具`,`流水號`) AS B

這塊跑出來會是

工具 流水號 狀態 位置 numc
AA 1 1 0001 1
AA 2 1 0003 2
AA 3 1 0002 3
AC 2 1 0008 1
AC 3 1 0009 2

然後我把上面那塊包起來命名為C供上層做 LEFT JION 而已

rew87516 iT邦新手 5 級 ‧ 2018-12-28 11:46:43 檢舉

哦!!!! 我懂了
一般都是用"現有表單"做查詢用的內容
你這招是直接在命令裡創造一個表單,並利用它來設定變數與給初始值
再用JOIN填值
想都沒想過! 學到一招了!!!!

rew87516 iT邦新手 5 級 ‧ 2018-12-28 12:08:33 檢舉

可以再講解一下這行嗎?

@type := `工具` dummy

是把工具列表.工具的值丟到@type變數裡,再給他一個dummy的別名這樣嗎?

感覺很像這個

SELECT @type := '' dummy

跟下面這種寫法有不同嗎?

SELECT @type := '' as dummy

哦!! 我自己查到了,別名as的確可以省略

0
浩瀚星空
iT邦超人 1 級 ‧ 2018-12-27 10:41:50

其實我現在有點搞不太清楚你想要的需求是什麼。

以下是用清測你是否想要這樣處理

SELECT * FROM `產品` LEFT JOIN (SELECT 工具,sum(if(狀態=1,1,0)) as 剩餘數量 FROM `工具列表` group by 工具) AS 工具數量 using(工具)

還有,建議你最好棄用中文名稱欄位名。
現在可能沒事,未來是否會有事就不知道了。

我就有遇過一個一開始建議他不要用,他認為無所謂。
現在就碰上因為使用中文名稱。處理一些問題的痛苦。
然後因為也用久了,程式也都寫好了。要改名稱也不是,不改名稱也不是的地步了。

看更多先前的回應...收起先前的回應...
rew87516 iT邦新手 5 級 ‧ 2018-12-27 12:06:17 檢舉

瞭解~之後會改掉使用中文欄位名稱的壞習慣:P
我想要出的表是向下圖這樣:
欲達成效果
AA、AB、AC三種工具各三個,也就是有九個工具
依照需求數量與庫存數量取較低的數值輸出
但因為庫存數量為零時,整個欄位都不會輸出了
而輸出時工具的位置也要一併輸出,所以不能Group之後只顯示剩餘數量

小魚 iT邦大師 1 級 ‧ 2018-12-27 15:07:37 檢舉

恩, 資料庫資料表, 程式的變數, 這些最好都用英文比較好,
才不會發生不必要的麻煩.

那就用

SELECT * FROM `產品` LEFT JOIN (SELECT 工具,sum(if(狀態=1,1,0)) as 剩餘數量,GROUP_CONCAT(流水號),GROUP_CONCAT(位置) FROM `工具列表` group by 工具) AS 工具數量 using(工具)

處理吧。將流水號跟位置GROUP_CONCAT起來。
雖然如果還要做數量處理的話。很難處理

畢竟你同時要列出細項及條件。真的不好處理。
一般還是將資料讀出後再用程式來幫你處理掉會比較好。

rew87516 iT邦新手 5 級 ‧ 2018-12-28 08:42:30 檢舉

因為下SQLcommand的次數一多回應的時間就要等很久
所以如果要先下一次SQLcommand取得使用工具種類
再下一次SQLcommand取得工具位置
就會讓使用者等很久@@

因為你要的東西不做2次處理很麻煩。
因為不可能又要a又要b,然後a=b跟a<>b
這說真的很難達到這樣的條件。

我上面的語法也只是將所有位置先取出來。就是用一次sql將必要的資料給取出來而已。

之後再教由程式來處理就好了。

真應要用sql來做。其實也只是將兩段sql語法使用子查尋的方式來處理。
可是一般資料少還無所謂,資料多也是會很危險的。

我看 淺水員 已經有寫出來了。
這其實也算是一種子查尋的應用方式。

但說真的,雖可以用一次sql語法處理。但這樣的處理方式碰上資料筆數大,就有一定的危險

rew87516 iT邦新手 5 級 ‧ 2018-12-28 11:39:24 檢舉

我一開始也是希望下兩次SQL,後續由程式比對
不過程式端不買單 囧
您的方式雖然已將必要資料取出,但是前端沒辦法直接使用
必須還要過一道手續把各個流水號與位置拆開來重新對應
所以...我想對程式端來講,麻煩少一點是一點吧ˊ_ˋ

rew87516 iT邦新手 5 級 ‧ 2018-12-28 11:55:33 檢舉

不過危險的部分可以稍微說明一下嗎?
是指這種命令的下法會使DB server的負擔變很重?

淺水員 iT邦研究生 4 級 ‧ 2018-12-28 12:11:12 檢舉

其實我昨天是想說沒試過直接這樣取值
所以想說挑戰看看

平時的做法也是直接取出來
然後由後端程式拿掉多餘的資料再丟給前端
前端並不需要動到程式碼

至於效能部分
目前知道的是使用變數的那個查詢因為必須掃過整個「工具列表」
所以「工具列表」數量大的話應該會變慢

其實這要看兩邊的配合度就是了。
如果覺得可行的話。就這樣用吧。
只是只能告知有危險度就是了。

基本上資料量如果不大的話。倒是可以不用太理會。
這是資料量大的情況下才會碰到的危險。

0
小魚
iT邦大師 1 級 ‧ 2018-12-27 15:08:43

說真的,
在庫流水號 還不知道怎麼處理,
這還要查資料,
只是我沒時間去查,
其他的倒是用 LEFT JOIN 就好了.

rew87516 iT邦新手 5 級 ‧ 2018-12-28 11:04:28 檢舉

潛水員大大用了兩次JOIN,感覺超複雜
我雖然改出我要的版本了
不過他的命令我還是有看沒有懂 囧a

小魚 iT邦大師 1 級 ‧ 2018-12-28 13:32:48 檢舉

因為在庫流水號會比較麻煩, 其他還好.

我要發表回答

立即登入回答