MySQL：使用主键和外键连接的两个表，如何获取所有记录，即使外键尚未填充

Question

我希望找到解决办法

我有一张带有主键stock的表

stkid (pk), name

第二张表（share）

price, quantity, stkid (fk)

我运行了这个查询，但它只显示了已经在共享记录中有记录的股票 我希望显示所有股票，即使在共享记录中没有记录

select name, 
       0, 
       sum(price*quantity) / sum(quantity) as avg, 
       sum(quantity) as qty 
from stock,
     share 
where share.stkid = stock.stkid 
group by (stock.stkid)

Answer 1

您可以使用LEFT JOIN语句，它会从share表中选择相关行，即使在stock表中没有链接的行也会选择出来。

SELECT
  name,
  0,
  sum(price*quantity) / sum(quantity) as avg,
  sum(quantity) as qty
FROM stock
LEFT JOIN share ON share.stkid = stock.stkid
GROUP BY stock.stkid