[php+mysql]資料庫解析：找出還有庫存足夠的店家資料

javascript php mysql asp.net database 資料庫

isthome 2014-11-15 19:36:46 ‧ 7209 瀏覽

分享至

各位前輩好：

請問要如何下SQL語法，可以達到『列出還可以購買 "喵喵貓食" 的店家資料』
(1)搜尋[商品table]的商品、
(2)列出[店家table]的資料為主畫面，陳列有吻合該商品的店家。
(3)找出的店家的商品不應該為售完狀態，若庫存為0就不被搜尋出來了。

以下是我推估的方法，但是一直無法補完整的語法...

sql="
SELECT 店家.name,商品.* FROM 店家
LEFT JOIN 商品 ON 商品.name = "喵喵貓食"
AND ( SELECT SUM(售出資料.selled) where 售出資料.pid=商品.pid )
.... ....
"
店家table

sid name site
101 A店家台北市
102 B店家高雄市
105 C店家高雄市

商品table

pid sid name storage(存量/個)
301 101 汪汪狗食 5
302 101 喵喵貓食 3
303 105 喵喵貓食 3
305 102 汪汪狗食 3
306 102 喵喵貓食 10
307 102 吼吼飼料 10

售出資料table

sellid sid pid time selled(售出/個)
1 101 301 2014/11/10 1
2 102 306 2014/11/10 8
3 102 305 2014/11/10 3
4 105 303 2014/11/10 1
5 105 303 2014/11/10 2

---列出所有店家SQL語法

$result = mysql_query("SELECT 店家.name FROM 店家 ");
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    echo $row[0];
}

構思流程：
1.使用者搜尋"喵喵貓食"，先到 [商品table] 找到共3筆，pid={302,303,306}
並得知庫存量分別為 storage ={3,3,10} 個

SELECT 商品.storage FROM 商品 where name="喵喵貓食";

2.再到 [售出資料table] 查看已被賣出的數量

pid=302的被賣出0個 =>表示還有3個可被買
pid=303的被賣出3個 =>表示已售罄
pid=306的被賣出8個 =>表示還有2個可被買

SELECT SUM(售出資料.selled) FROM 售出資料 group by sid having sum(selled) > 0

3.從步驟2得知只剩下 2家店有多的商品，pid={302,306}

並藉由此pid找出店家ID，也就是sid ={101,102}
接著只要就是到 [店家table]找出吻合sid的店家.name

SELECT 店家.name FROM 店家 where 店家.sid in {101,102}

4.完成

看更多先前的討論...收起先前的討論...

bee78726 iT邦新手 3 級 ‧ 2014-11-15 23:02:15 檢舉

可以先更新庫存table數量
再select數量大於0的sid，這樣不知道行不行

月半車甫 iT邦研究生 3 級 ‧ 2014-11-17 10:06:15 檢舉

ｏｒａｃｌｅ　作法

select m.pid, m.sid, m.name, m.storage,
      nvl( (select sum(s.selled)
          from 售出資料table s
         where s.sid = m.sid
           and s.pid = m.pid ) ,0) selled
from 商品table m
where m.name = '喵喵貓食'
and m.storage -
      nvl( (select sum(s.selled)
          from 售出資料table s
         where s.sid = m.sid
           and s.pid = m.pid ) ,0) >0

PID	SID	NAME	STORAGE	SELLED
302	101	喵喵貓食	3	0
306	102	喵喵貓食	10	8

月半車甫 iT邦研究生 3 級 ‧ 2014-11-17 10:08:52 檢舉

你的步驟是解題順序，但通常資料庫的東西，很少人會用這種方式運作，
通常會同樓上所說

先更新庫存table數量
再select數量大於0的sid

bee78726 iT邦新手 3 級 ‧ 2014-11-17 10:30:54 檢舉

了解，受教了，感謝。

月半車甫 iT邦研究生 3 級 ‧ 2014-11-17 14:02:34 檢舉

bee78726大：我是在回應提問者啦～不是說你的作法。Sorry造成誤解了

話說，我看過一些進耗存系統，通常真的會有特地記錄料號的目前庫存！
也就是你說的先更新庫存table數量....

但，即使在Oracle ebs erp，也沒有單一Table記錄目前這個料號的庫存。

例如：用fifo（先進先出）的方式記錄，所以會有一個表格記錄每一筆進項的數量，
然後再每一個銷項去從最前頭的進項記錄去扣除數量直到0為止。
這個表格可能單一倉庫也會有好多筆剩餘庫存的紀錄存在。
我已也是必須加總這個表格才會得到真正庫存，但缺點是無法直接得到指定的某一天的庫存。

只是台灣似乎習慣使然，
還是會想隨時記錄起來最即時的庫存，以便select數量大於0的sid嗎？
所以應該很多企業有做這種客製吧！

Albert iT邦高手 1 級 ‧ 2014-11-17 15:23:08 檢舉

在 Review Oracle EBS 跨國菁英團隊的實作中常看到
nvl( (select sum(s.selled) from 售出資料table s
where s.sid = m.sid
and s.pid = m.pid )
既然 Where 是 Sub-Select 全面比對都會建議融入系統中
select * from (
select m.pid, m.sid, m.name, m.storage,
nvl( (select sum(s.selled)
from 售出資料table s
where s.sid = m.sid
and s.pid = m.pid ) ,0) selled
from 商品table m ) aa
where aa.name = '喵喵貓食'
and (aa.storage - aa.selled) > 0
如果資料千萬筆以上時 ,為了執行績效時要一次sum所有資料
我們會建議 sum 先產生 sum_table 再 join sum_table

小小建議可以試試 [程式可讀性] 跟 [執行績效]

isthome iT邦新手 4 級 ‧ 2014-11-17 20:53:54 檢舉

請問是在哪一步驟先做呢??

isthome iT邦新手 4 級 ‧ 2014-11-18 11:07:53 檢舉

yafuu168提到：

只是台灣似乎習慣使然，
還是會想隨時記錄起來最即時的庫存，以便select數量大於0的sid嗎？
所以應該很多企業有做這種客製吧！

請問yafuu168前輩，
我目前的這種資料庫存放方式是自己想的，
有何不妥的地方嗎?

另外我有補上SQL語法，
您的方式我再試試看，感謝

isthome iT邦新手 4 級 ‧ 2014-11-18 11:19:22 檢舉

http://sqlfiddle.com/#!2/578cea/4

目前修改yafuu168前輩的SQL語法如下，執行結果如截圖。

select m.pid, m.sid, m.name, m.storage,  
      IFNULL( (select sum(s.selled)  
          from sold s  
         where s.sid = m.sid  
           and s.pid = m.pid ) ,0) selled  
from prod m  
where m.name = '喵喵貓食'  
and m.storage -  
      IFNULL( (select sum(s.selled)  
          from sold s  
         where s.sid = m.sid  
           and s.pid = m.pid ) ,0) >0

月半車甫 iT邦研究生 3 級 ‧ 2014-11-18 15:33:50 檢舉

之前礙於回應字數限制<1000,所以create table, insert into 部分，我就省略了。
只丟結果，但確實是驗證過，差別只在這是Oracle SQL, 你需要自己改成mysql可用。

albertachen大的建議是對的，這種只考慮題目所提的資料筆數來找解答，很容易忽略效能，
但我習慣了User開始反應報表變慢之後，才開始SQL Tuning...這是不好的觀念，謹記！

這種資料架構好或不好？
我只能說這不像是實務上運作的資料庫....比較像是為了學sql而作的練習題....
如果只是個練習題，不需要討論"妥不妥"！

能做出結果、能按步解題（你的1.2.3.4.），就是好的開始，
我只是把你的步驟變成單一SQL。

真要說些建議，你可以思考看看實務過程，庫存是如何入進去的，銷售又是如何產生的？
這兩個動作決定了，要不要直接扣除庫存的動作要擺在哪裡執行？甚至退貨動作如何運作？

所以，你想了解的，已經是表單過帳流程（進銷存）的議題了.....

isthome iT邦新手 4 級 ‧ 2014-11-18 23:06:37 檢舉

yafuu168前輩：
您的語法我有修改成mysql( mysql不支援nvl 我改成IFNULL)
，的確是可行的。我也有截圖跟留下mysql語法，
可見2014-11-18 11:19:22。

月半車甫 iT邦研究生 3 級 ‧ 2014-11-19 11:29:01 檢舉

我有看到你post的結果了阿～忘了回應你了！
沒想到只差個字！學到了～感恩。

Albert iT邦高手 1 級 ‧ 2014-11-20 06:52:04 檢舉

albertachen提到：
select * from (
select m.pid, m.sid, m.name, m.storage,
nvl( (select sum(s.selled)
from 售出資料table s
where s.sid = m.sid
and s.pid = m.p...(恕刪)

應該是這樣才對
select m.pid, m.sid, m.name, m.storage,
nvl( (select sum(s.selled)
from 售出資料table s
where s.sid = m.sid
and s.pid = m.pid ) ,0) selled
from 商品table m
m.name = '喵喵貓食') aa
where (aa.storage - aa.selled) > 0
我們是Oracle EBS 最佳外圍系統免去Oracle EBS 權費用
外圍系統可以 200人到 2000人都不需要版權費用
提供技術轉移,客戶可以任意修改
客戶可以任意布點
客戶可以複製轉售
www.idempiere.com.tw

isthome iT邦新手 4 級 ‧ 2014-11-24 12:17:27 檢舉

在請在yafuu168前輩貼在回答區吧~~我好選出一個解答

登入發表討論

直播研討會

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

直播中

1 個回答

月半車甫

iT邦研究生 3 級 ‧ 2014-11-24 16:10:17

最佳解答

Oracle 作法供參考～

&lt;pre class="c" name="code">select m.pid, m.sid, m.name, m.storage,
      nvl( (select sum(s.selled)
          from 售出資料table s
         where s.sid = m.sid
           and s.pid = m.pid ) ,0) selled
from 商品table m
where m.name = '喵喵貓食'
and m.storage -
      nvl( (select sum(s.selled)
          from 售出資料table s
         where s.sid = m.sid
           and s.pid = m.pid ) ,0) >0

恭喜你自己改成mysql版成功。