iT邦幫忙

0

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

各位前輩好:

請問要如何下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,這樣不知道行不行
yafuu168 iT邦研究生 3 級 ‧ 2014-11-17 10:06:15 檢舉
oracle 作法
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
yafuu168 iT邦研究生 3 級 ‧ 2014-11-17 10:08:52 檢舉
你的步驟是解題順序,但通常資料庫的東西,很少人會用這種方式運作,
通常會同樓上所說

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

bee78726 iT邦新手 3 級 ‧ 2014-11-17 10:30:54 檢舉
了解,受教了,感謝。
yafuu168 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  

yafuu168 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。
yafuu168 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前輩貼在回答區吧~~我好選出一個解答

1 個回答

0
yafuu168
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版成功。

我要發表回答

立即登入回答