先上兩個表單格式
表單名稱:產品
表單名稱:工具列表
欲達成效果:
以產品表單來得知要製作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
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
這正是我要的效果! 謝謝
我來研究一下:D
參考您的命令,改出我要的版本了
太感謝您了!!!!
只不過這段是什麼意思阿
FROM
(SELECT @num:=0,@type:='') AS init
FROM後面不是都是接表單名稱嗎@@?
直接執行下面這行
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 而已 |
哦!!!! 我懂了
一般都是用"現有表單"做查詢用的內容
你這招是直接在命令裡創造一個表單,並利用它來設定變數與給初始值
再用JOIN填值
想都沒想過! 學到一招了!!!!
可以再講解一下這行嗎?
@type := `工具` dummy
是把工具列表
.工具
的值丟到@type變數裡,再給他一個dummy的別名這樣嗎?
感覺很像這個
SELECT @type := '' dummy
跟下面這種寫法有不同嗎?
SELECT @type := '' as dummy
哦!! 我自己查到了,別名as的確可以省略
其實我現在有點搞不太清楚你想要的需求是什麼。
以下是用清測你是否想要這樣處理
SELECT * FROM `產品` LEFT JOIN (SELECT 工具,sum(if(狀態=1,1,0)) as 剩餘數量 FROM `工具列表` group by 工具) AS 工具數量 using(工具)
還有,建議你最好棄用中文名稱欄位名。
現在可能沒事,未來是否會有事就不知道了。
我就有遇過一個一開始建議他不要用,他認為無所謂。
現在就碰上因為使用中文名稱。處理一些問題的痛苦。
然後因為也用久了,程式也都寫好了。要改名稱也不是,不改名稱也不是的地步了。
瞭解~之後會改掉使用中文欄位名稱的壞習慣:P
我想要出的表是向下圖這樣:
AA、AB、AC三種工具各三個,也就是有九個工具
依照需求數量與庫存數量取較低的數值輸出
但因為庫存數量為零時,整個欄位都不會輸出了
而輸出時工具的位置也要一併輸出,所以不能Group之後只顯示剩餘數量
恩, 資料庫資料表, 程式的變數, 這些最好都用英文比較好,
才不會發生不必要的麻煩.
那就用
SELECT * FROM `產品` LEFT JOIN (SELECT 工具,sum(if(狀態=1,1,0)) as 剩餘數量,GROUP_CONCAT(流水號),GROUP_CONCAT(位置) FROM `工具列表` group by 工具) AS 工具數量 using(工具)
處理吧。將流水號跟位置GROUP_CONCAT起來。
雖然如果還要做數量處理的話。很難處理
畢竟你同時要列出細項及條件。真的不好處理。
一般還是將資料讀出後再用程式來幫你處理掉會比較好。
因為下SQLcommand的次數一多回應的時間就要等很久
所以如果要先下一次SQLcommand取得使用工具種類
再下一次SQLcommand取得工具位置
就會讓使用者等很久@@
因為你要的東西不做2次處理很麻煩。
因為不可能又要a又要b,然後a=b跟a<>b
這說真的很難達到這樣的條件。
我上面的語法也只是將所有位置先取出來。就是用一次sql將必要的資料給取出來而已。
之後再教由程式來處理就好了。
真應要用sql來做。其實也只是將兩段sql語法使用子查尋的方式來處理。
可是一般資料少還無所謂,資料多也是會很危險的。
我看 淺水員 已經有寫出來了。
這其實也算是一種子查尋的應用方式。
但說真的,雖可以用一次sql語法處理。但這樣的處理方式碰上資料筆數大,就有一定的危險
我一開始也是希望下兩次SQL,後續由程式比對
不過程式端不買單 囧
您的方式雖然已將必要資料取出,但是前端沒辦法直接使用
必須還要過一道手續把各個流水號與位置拆開來重新對應
所以...我想對程式端來講,麻煩少一點是一點吧ˊ_ˋ
說真的,
在庫流水號 還不知道怎麼處理,
這還要查資料,
只是我沒時間去查,
其他的倒是用 LEFT JOIN 就好了.