sql - 当单个列包含来自 2 个不同表的值时如何获取数据？

Question

我知道这是一个令人困惑的问题，所以让我先解释一下这个场景。

场景是:

有3张 table

1. service_master(id,name,cost,duration,visit_count,remarks)

2. product_master (id, name, cost)

3. 销售(id、item_id、item_type)

所以，在sell表中

id 用于主键，

item_id 有来自 2 个表的数据(product_master 和 service_master)

item_type 将用于识别该项目是服务还是产品( P, S)

所以问题是当我想为用户获取所有已售出的商品时，如何获取商品名称而不是 IDes

请建议我应该怎么做，如果我做错了请纠正我。

非常感谢!

Answer 1

如果 ID 在服务表和产品表中都是唯一的(可能通过使用序列)，您可以:

SELECT
  sell.id sell_id
  , sell.item_type
  , item_detail.id
  , item_detail.name
  , item_detail.cost
FROM sell
JOIN
  (SELECT id, name, cost FROM service_master
   UNION ALL
   SELECT id, name, cost FROM product_master
  ) item_detail
  ON sell.item_id = item_detail.id
;

否则，您还有另一种选择(除了 Thorsten Kettner 和 DhruvJoshi 建议的那些)，如果您的表很大，您可能想要测试性能影响 - 但您想要检索非常小的数据子集(例如:一张发票):

SELECT
  id sell_id
  , CASE item_type
    WHEN 'P'
    THEN (SELECT name FROM product_master WHERE sell.item_id = id)
    WHEN 'S'
    THEN (SELECT name FROM service_master WHERE sell.item_id = id)
    END AS item_name
FROM sell
;

查看实际效果:SQL Fiddle

更新

有一种简单地连接表的方法——它甚至适用于重叠的 ID:

SELECT
  sell.id sell_id
  , sell.item_type
  , item_detail.id
  , item_detail.name
  , item_detail.cost
FROM sell2 sell
JOIN
  (SELECT 'S' flag, id, name, cost FROM service_master2
   UNION ALL
   SELECT 'P', id, name, cost FROM product_master2
  ) item_detail
  ON sell.item_type = item_detail.flag
  AND sell.item_id = item_detail.id
;

对比一下:SQL Fiddle .

请评论，如果这需要调整/进一步的细节。