来自 3 个表的 MySQL 查询

Question

假设我有 3 个表......

____________
|pid |pname|
| 1  |  a  |
| 2  |  b  |
| 3  |  c  |
------------
product

____________
|pid | qty |
| 1  |  5  |
| 2  |  8  |
| 1  |  5  |
------------
stock

____________
|pid | qty |
| 2  |  4  |
| 1  |  3  |
| 1  |  2  |
------------
sale

现在我想显示，首先从产品表中选择快速项目 (a)，然后选择总数量。库存表和销售表中的项目的值，并从库存数量总数和销售数量总数中减去该值。像这个例子——

________________________________
|pname |  Stock | Sale | remain |
|  A   |   10   |  5   |    5   |
|  B   |   8    |  4   |    4   |
|  C   |   0    |  0   |    0   |
---------------------------------

Answer 1

您可以执行以下操作:参见 SQL Fiddle with Demo

select p.pname
  , st.TotalStock
  , sl.TotalSale
  , st.TotalStock - sl.TotalSale as Remain
from product p
inner join 
(
  select pid, sum(qty) as TotalStock
  from stock 
  group by pid
) st
  on p.pid = st.pid
inner join
(
  select pid, sum(qty) as TotalSale
  from sale
  group by pid
) sl
  on p.pid = sl.pid

INNER JOIN 将为您提供所有表中存在的记录，但如果您只想查看部分表中存在的记录，请使用 LEFT JOIN(参见 SQL Fiddle)

select p.pname
  , ifnull(st.TotalStock, 0) as TotalStock
  , ifnull(sl.TotalSale, 0) as TotalSale
  , ifnull(st.TotalStock, 0) - ifnull(sl.TotalSale, 0) as Remain
from product p
left join 
(
  select pid, sum(qty) as TotalStock
  from stock 
  group by pid
) st
  on p.pid = st.pid
left join
(
  select pid, sum(qty) as TotalSale
  from sale
  group by pid
) sl
  on p.pid = sl.pid