SQL初心者ということであえて答えは載せませんが、以下のキーワードをもとに検索してみるとハッピーになれるかもです！
「IFNULL」、「COALESCE」
1Like
MYSQL NULLに0を代入したい
解決したいこと
プログラミング初心者ですがよろしくお願いします。
簡単な在庫管理表を left join、 group by(sum)、 ifnull関数を使って作成中です。
何とかテーブル同士の結合や加算までは出来たみたいなのですが、どうしてもカラム内の
NULLを0表示に変えることが出来ずに暗礁に乗り上げた状態です。
どこにifnullを使用したら良いかがわかりません、解決方法を教えて下さい。
PHPでmysqlにクエリを投げてます。
MYSQLのバージョン【5・7・34】
PHP のバージョン【7・3・1】
Product_Tbl
UnitIn_Tbl
UnitOut_Tbl
上記tblの結合です。
発生している問題・エラー
エラーメッセージは出ないのですがNULLの部分が0に変わりません。
今現在の結果です。
+-----------+-------------+-----------+------------+---------+----------+-------+
| ProductId | ProductName | UnitPrice | UnitNumber | Unit_In | Unit_Out | Total |
+-----------+-------------+-----------+------------+---------+----------+-------+
| 101 | 商品A | 200 | 50 | 60 | 35 | 75 |
| 102 | 商品B | 250 | 30 | 45 | 50 | 25 |
| 103 | 商品C | 150 | 60 | 30 | NULL | NULL |
| 104 | 商品D | 400 | 80 | NULL | 55 | NULL |
| 105 | 商品E | 300 | 110 | NULL | NULL | NULL |
+-----------+-------------+-----------+------------+---------+----------+-------+
該当するソースコード
$sql="SELECT
Product_Tbl.ProductId as ProductId,
Product_Tbl.ProductName as ProductName,
Product_Tbl.UnitPrice as UnitPrice,
Product_Tbl.UnitNumber as UnitNumber,
UnitIn.UnitIn_Sum as Unit_In,
UnitOut.UnitOut_Sum as Unit_Out,
(Product_Tbl.UnitNumber+UnitIn.UnitIn_Sum-UnitOut.UnitOut_Sum) as Total
FROM Product_Tbl
LEFT JOIN(SELECT IFNULL (SUM(UnitIn_Tbl.UnitInNumber),'0') as UnitIn_Sum,
UnitIn_Tbl.ProductInId
FROM UnitIn_Tbl GROUP BY UnitIn_Tbl.ProductInId) as UnitIn on UnitIn.
ProductInId = Product_Tbl.ProductId
LEFT JOIN(SELECT IFNULL (SUM(UnitOut_Tbl.UnitOutNumber),'0') as UnitOut_Sum,
UnitOut_Tbl.ProductOutId
FROM UnitOut_Tbl
GROUP BY UnitOut_Tbl.ProductOutId) as
UnitOut on UnitOut.ProductOutId = Product_Tbl.ProductId
ORDER BY ProductId";
自分で試したこと
色んなサイトを検索しましたが、答えにたどり着けず投稿に至りました。
よろしくお願いいたします。
0
3Answer
Comments
@wins09-yi
Questionerハッピーになりたいです！地道に努力したいと思います。
上記検索してみます、ありがとうございます。
入庫／出庫実績のない製品の入庫／出庫列が外部結合によってNULLになっています。
結合前のテーブルに対して何をしても効きません。
IFNULL関数をメインクエリのSELECT句に移動してください。
数値を扱っているので'0'の'は不要です。
0Like
Comments
@wins09-yi
Questionerご回答ありがとうございます。
今晩にでも見直してみます。@wins09-yi
Questionerご教示いただいた方法で「入庫／出庫実績のない製品の入庫／出庫列」がNULLになる件については解決出来ました！ありがとうございます。
ただTOTALに関しては相変わらずNULLのままとなっており、試行錯誤しましたがなかなか解決できずに困っているところです…
入庫列or出庫列が0の時のみTOTALがNULLになることが確認できており、
入庫も出庫も1以上の場合は正しく計算が出来ております。
このことから、0が数値では無く文字列として扱われているのではないかと考えていますが、解決には至っておりません…
参考までにSQL文と実行結果を掲載させていただきますので、
もしよろしければ再度ご教示いただければ幸いでございます。
【SQL文】
$sql="SELECT
Product_Tbl.ProductId as ProductId,
Product_Tbl.ProductName as ProductName,
Product_Tbl.UnitPrice as UnitPrice,
Product_Tbl.UnitNumber as UnitNumber,
ifnull (UnitIn.UnitIn_Sum,0) as Unit_In,
ifnull (UnitOut.UnitOut_Sum,0) as Unit_Out,
(Product_Tbl.UnitNumber+UnitIn.UnitIn_Sum-UnitOut.UnitOut_Sum) as Total
FROM Product_Tbl
LEFT JOIN(SELECT SUM(UnitIn_Tbl.UnitInNumber) as UnitIn_Sum,UnitIn_Tbl.ProductInId
FROM UnitIn_Tbl GROUP BY UnitIn_Tbl.ProductInId) as UnitIn on UnitIn.ProductInId = Product_Tbl.ProductId
LEFT JOIN(SELECT SUM(UnitOut_Tbl.UnitOutNumber) as UnitOut_Sum,UnitOut_Tbl.ProductOutId
FROM UnitOut_Tbl
GROUP BY UnitOut_Tbl.ProductOutId) as UnitOut on UnitOut.ProductOutId = Product_Tbl.ProductId
ORDER BY ProductId";
【実行結果】
+-----------+-------------+-----------+------------+---------+----------+-------+
| ProductId | ProductName | UnitPrice | UnitNumber | Unit_In | Unit_Out | Total |
+-----------+-------------+-----------+------------+---------+----------+-------+
| 101 | 商品A | 200 | 50 | 60 | 35 | 75 |
| 102 | 商品B | 250 | 30 | 45 | 50 | 25 |
| 103 | 商品C | 150 | 60 | 30 | 10 | 80 |
| 104 | 商品D | 400 | 80 | 0 | 55 | NULL |
| 105 | 商品E | 300 | 110 | 0 | 0 | NULL |
+-----------+-------------+-----------+------------+---------+----------+-------+
このことから、0が数値では無く文字列として扱われているのではないかと考えています
そうではなくて、NULLを含んだ計算の結果としてNULLが現れているだけです。
（MySQLは型に対して甘いので、'0' + 1も1として評価されます。）
SQLにおいて、NULLを含んだ式は原則それ自体がNULLに評価されます。
（1 OR NULLやIS NULL演算子, IFNULL関数などの例外を除く。）
たとえば、次のクエリを実行してみてください。
これがTotal列で起きていることです。
SELECT 1 + NULL
これでピンとこなければ、IFNULL(UnitIn.UnitIn_Sum, 0)が式全体として0になるだけで、
UnitIn.UnitIn_Sumなどは相変わらずNULLであることに注意してください。
意図通りにするには次のように書きます。
Product_Tbl.UnitNumber + IFNULL(UnitIn.UnitIn_Sum, 0) - IFNULL(UnitOut.UnitOut_Sum, 0) AS Total
面倒ですが、別名を用いてUnitNumber + Unit_In - Unit_Outとは書けません。
今回のようにSELECT句で射影するべき式を悩んでいるときは、
ひとまず全ての列を書き出してみることが、解決への近道です。
SELECT
*
FROM
-- 略
0Like
Comments
@wins09-yi
Questionerご回答ありがとうございます。
ご指摘の内容で修正した結果、無事理想通りに表示することが出来ました。
また、仕組みについても教えていただきありがとうございます。
今後の勉強において非常に役立つ内容でした。
完成まではまだまだかかりそうですが、
今回教えていただいたことも参考にしつつ、地道に頑張ってみたいと思います！
改めて、この度はありがとうございました。