我使用 select table1.产品, table1.数量 , table1.数量-table2.订单 as 货舱存货
from table1, table2
group by table1.产品, table1.数量;
结果集是出现了
产品 货舱
A 9
B 9
C 9
请问我要如何显示全部的产品存货呢?
感谢
...全文
22427打赏收藏
使用table1 - table2,如何让结果集显示全部呢?
table1中有 产品 货舱 A 10 B 10 C 10 D 10 E 10 table2是订单 产品 订单 A 1 B 1 C 1 我使用 select table1.产品, table1.数量 , table1.数量-table2.订单 as 货舱存货 from table1, table2 group by table1.产品, table1.数量; 结果集是出现了 产品 货舱 A 9 B 9 C 9 请问我要如何显示全部的产品存货呢? 感谢
Product ID Product ID Store
P0001 One Peace 12
P0002 Dragon Ball Z 3
P0003 Dragon Ball Z II 18
P0004 Vampire Knight 23
P0005 B.O.D.Y. 20
P0006 New Engineering 5
P0007 Samurai Commando 25
P0008 Fruits Basket 23
P0009 Hell Girl 2
P0010 Slam Dunk 8
P0011 Lovely Complex 21
P0012 The Prince of Tennis 2
P0013 Pumpkin Scissors 18
P0014 Initial D 3
P0015 Air Gear 23
P0016 Manga Sutra 17
P0017 Naruto 9
P0018 The Walking Man 1
Product ID Product Name Total Order
P0001 One Peace 1
P0002 Dragon Ball Z 2
P0006 New Engineering 3
P0009 Hell Girl 4
P0010 Slam Dunk 5
P0012 The Prince of Tennis 6
P0014 Initial D 7
P0017 Naruto 8
P0018 The Walking Man 9
如果结果如下,对不对?不对的话,请给出你认为正确的结果。
Product ID Product Name Total Order
P0001 One Peace 4
P0002 Dragon Ball Z 5
P0006 New Engineering 1
P0009 Hell Girl 3
P0010 Slam Dunk 7
P0012 The Prince of Tennis 0
P0014 Initial D 4
P0017 Naruto 2
P0018 The Walking Man 1
如果结果如下,对不对?不对的话,请给出你认为正确的结果。
Product ID Product Name Total Order
P0001 One Peace 4
P0002 Dragon Ball Z 5
P0006 New Engineering 1
P0009 Hell Girl 3
P0010 Slam Dunk 7
P0012 The Prince of Tennis 0
P0014 Initial D 4
P0017 Naruto 2
P0018 The Walking Man 1
Order List
Order ID Customer Name Email Address Contact Product ID Quantity Order From Staff
OR001 Alexander alexander22@yahoo.com 93222362 P0002 3 S001
OR002 Christine christine27@live.com 89324122 P0001 5 S002
OR003 David Beckham davidbeckham@yahoo.com 96666215 P0010 5 S003
OR004 Michael Ovem michaelovem@live.com 96552153 P0006 5 S006
OR005 Jacky Chan jackychan@gmail.com 87459321 P0017 2 S002
OR006 Andy Lau andylau@hotmail.com 83321596 P0001 1 S005
OR007 Victoria victoria@gmail.com 86785912 P0017 2 S005
OR008 Lydia lydia@yahoo.com 93687962 P0001 3 S001
OR009 Peter Tan petertan@hotmail.com 98222168 P0014 2 S005
OR010 Edward edward19@yahoo.com 84795323 P0001 3 S001
OR011 Ng Jian Xian ngjianxian@gmail.com 96632230 P0014 1 S005
OR012 Chan Xi Xi chanxixi@gmail.com 80369233 P0009 1 S007
OR013 Arthur M.Law arthur@yahoo.com 81396753 P0017 5 S002
OR014 Laura N.Kebel laurakebel@yahoo.com 82236936 P0010 3 S003
OR015 Tan Xiao Fah xiaofah@hotmail.com 89321256 P0012 2 S004
OR016 Wong Soon Ket soonket@gmail.com 93258633 P0018 1 S007
Product List
Product ID Product Name Price (SGD) Store
P0001 One Peace 128 16
P0002 Dragon Ball Z 68 22
P0003 Dragon Ball Z II 78 18
P0004 Vampire Knight 48 23
P0005 B.O.D.Y. 48 20
P0006 New Engineering 52 21
P0007 Samurai Commando 38 25
P0008 Fruits Basket 36 23
P0009 Hell Girl 42 22
P0010 Slam Dunk 75 13
P0011 Lovely Complex 40 21
P0012 The Prince of Tennis 56 16
P0013 Pumpkin Scissors 46 18
P0014 Initial D 199 9
P0015 Air Gear 30 23
P0016 Manga Sutra 80 17
P0017 Naruto 109 16
P0018 The Walking Man 40 26
Total Order
Product ID Product Name Total Order
P0001 One Peace 12
P0002 Dragon Ball Z 3
P0006 New Engineering 5
P0009 Hell Girl 1
P0010 Slam Dunk 8
P0012 The Prince of Tennis 2
P0014 Initial D 3
P0017 Naruto 9
P0018 The Walking Man 1