英文:
I want find all records even if not yet popoulated with foreign key
问题
表格 stock：
stkid (主键), name
表格 share：
price, quantity, stkid (外键)
我运行了这个查询，但它只显示已经在股票中有记录的股票。我想显示所有股票，即使在股票中没有记录也要显示。
选择名称，
0，
总价值*数量之和) / 数量之和) as 平均价,
数量之和) as 数量
从股票，
分享
其中共享. stkid = 股票. stkid
按(stock.stkid)分组
英文:
Table stock:
stkid (pk), name
Table share:
price, quantity, stkid (fk)
I run this query but it shows stocks only that have records in shares already. I want to show all stocks even with no records in shares.
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)
答案1
得分: 0
你可以使用左连接语句，即使在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
英文:
You can use a LEFT JOIN statement, it will still select the lines from the stock table even if there is no linked row in the share table.
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论