如何查询全部stock记录（含未关联share表的记录）

问题背景 #

现有两张数据表：

• stock表（主键为stkid）：

  stkid (pk), name
  

• share表（stkid为外键，关联stock表的stkid）：

  price, quantity, stkid (fk)
  

你原来的SQL用了隐式内连接，只能查出有对应share记录的股票，现在要改成不管有没有关联的share数据，所有stock记录都能显示。原SQL代码如下：

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)

解决办法：换用左连接 #

要保留所有stock记录，得用LEFT JOIN（左连接）替代原来的内连接，还要处理没有share数据时聚合函数返回NULL的问题，避免结果显示空值或报错。

修改后的SQL代码 #

select 
    s.name,
    0,
    -- 无持仓数据时平均值显示0，避免除以0报错
    case 
        when sum(sh.quantity) is null or sum(sh.quantity) = 0 then 0
        else sum(sh.price * sh.quantity) / sum(sh.quantity) 
    end as avg,
    -- 无持仓数据时数量显示0，不显示NULL
    coalesce(sum(sh.quantity), 0) as qty
from stock s
left join share sh on sh.stkid = s.stkid
group by s.stkid, s.name

关键说明 #

• LEFT JOIN：强制保留stock表的所有记录，哪怕share表里找不到对应的stkid
• COALESCE函数：把sum(sh.quantity)的NULL值转成0，结果更直观
• CASE判断：避免无持仓数据时出现除以0的错误，直接返回0
• GROUP BY补充name：部分数据库要求SELECT中的非聚合列必须出现在GROUP BY里，加上s.name更稳妥

内容的提问来源于stack exchange，提问作者coder 534