将SQL查找表与数据表连接 [英] Joining SQL lookup table with data table

问题描述

我有一个查找表，其中列出了具有字段CityId，CityName的城市

I have a lookup table say cities with fields CityId, CityName

CityId   CityName
1        New York 
2        San Francisco
3        Chicago

我有一个订单表，其中包含以下字段:CityId，CustId，CompletedOrders，PendingOrders

I have an orders table which has fields: 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

该怎么做?

推荐答案

SELECT
  c.CityId
  c.CityName
  o.CustId,
  o.CompletedOrders
  o.PendingOrders
FROM cities c
LEFT JOIN orders o ON ( c.CityId = o.CityId )

这将返回所需的所有行，但是对于details中不存在的行，它将返回NULL值，因此您将获得:

This will return all the rows that you want, but for the rows that don't exist in details it will return NULL values, so you would get:

CityId CityName      CustId CompletedOrders PendingOrders
1      New York      123    100             50
2      San Francisco 123    75              20
3      Chicago       123    NULL            NULL

获取0的解决方案取决于您的数据库.对于MySQL使用IFNULL，对于Oracle使用NVL.

The solution to get 0 instead depends on your database. With MySQL use IFNULL, with Oracle use NVL.

这篇关于将SQL查找表与数据表连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！