将 SQL 查找表与数据表联接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2015207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining SQL lookup table with data table
提问by NOMO
I have a lookup table say cities with fields CityId, CityName
我有一个查找表，上面写着带有 CityId、CityName 字段的城市
CityId CityName
1 New York
2 San Francisco
3 Chicago
I have an orders table which has fields: CityId, CustId, CompletedOrders, PendingOrders
我有一个订单表，其中包含以下字段：CityId、CustId、CompletedOrders、PendingOrders
CityId CustId CompletedOrders PendingOrders
1 123 100 50
2 123 75 20
I want a table/report that lists orders details of a given customer in all cities, i.e. the result I need is:
我想要一个表格/报告，列出所有城市中给定客户的订单详细信息，即我需要的结果是：
CityId CityName CustId CompletedOrders PendingOrders
1 New York 123 100 50
2 San Francisco 123 75 20
3 Chicago 123 0 0
How to do that ?
怎么做 ？
回答by Peter Lang
SELECT
c.CityId
c.CityName
o.CustId,
o.CompletedOrders
o.PendingOrders
FROM cities c
LEFT JOIN orders o ON ( c.CityId = o.CityId )
This will return all the rows that you want, but for the rows that don't exist in detailsit will return NULLvalues, so you would get:
这将返回您想要的所有行，但对于其中不存在的details行将返回NULL值，因此您将获得：
CityId CityName CustId CompletedOrders PendingOrders
1 New York 123 100 50
2 San Francisco 123 75 20
3 Chicago 123 NULL NULL
The solution to get 0instead depends on your database. With MySQL use IFNULL, with Oracle use NVL.
获取的解决方案0取决于您的数据库。配合MySQL使用IFNULL，配合Oracle使用NVL。
回答by anishMarokey
try this
尝试这个
select c.CityId,c.CityName,o.CustId,o.CompletedOrders,o.PendingOrders
from orders Left join cities
on o.CityId = c.CityId
