mysql - SQL 使用 FOREIGN KEYS 选择行

Question

供应商表:

PK_ID  supplier  supplier_code
  1       x           abc
  2       y           def

项目表:

PK_ID   item_name   
 1        Name1           
 2        Name2

items_suppliers 表，包含 FOREIGN KEYS:

FK_ID_items  FK_ID_suppliers           
    1              1
    2              1  

我愿意:

选择一件商品，然后从 suppliers 表中查找具有相同供应商的其他商品。然后它应该返回“Name2”项。

[已编辑]:

获取ITEM1及其所有供应商，并查找下一个item与每个匹配的供应商 code> 仅查找与每个匹配的supplier 的下一个item。

<小时/>

正确答案(感谢@DarwinvonCorax - 得到@ThorstenKettner 的宝贵帮助):

SELECT i2.item_name, s.supplier, s.supplier_code
FROM items i1
JOIN items_suppliers is1
ON i1.PK_ID = is1.FK_ID_items
JOIN items_suppliers is2
ON is1.FK_ID_suppliers = is2.FK_ID_suppliers
JOIN items i2
ON is2.FK_ID_items = i2.PK_ID
JOIN suppliers s
ON is1.FK_ID_suppliers = s.PK_ID
WHERE i1.item_name = 'Name1'
ORDER BY
CASE WHEN i2.item_name = 'Name1'
     THEN 1
     ELSE 2
  END,
i2.item_name, s.supplier;

@ThorstenKettner 提出的解决方案也是正确的:

select item_name
 from items
where pk_id in
(
 select fk_id_items
   from items_suppliers
  where fk_id_suppliers in
  (
    select fk_id_suppliers  
    from items_suppliers
    where fk_id_items = 
      (
       select pk_id from items where item_name = 'Name1')
      )
 );

Answer 1

您需要执行回文连接(我的术语)。首先查找指定项目的供应商 ID，然后从 items_suppliers 查找同一供应商 ID 的所有记录。您将 items 连接到 items_suppliers、items_suppliers 和 items。然后在最后添加到 suppliers 的连接以获取供应商名称。

获得您想要的强制排序顺序有点棘手。我使用了在其他地方看到的黑客方法，但老实说我不知道​​它有多常见。我执行 SELECT 来仅获取 Item1 的供应商，并向结果添加一个值为 1 的常量列。我执行第二次 SELECT 来获取除 Item1 之外的所有商品的供应商，并添加常量列 2。 UNION 这两个查询，并从结果中仅选择所需的列，但 ORDER BY 常量，这将对值为 1 的所有行进行排序(Item1 的供应商)首先。

SELECT item_name, supplier, supplier_code
  FROM
    (SELECT '1' AS orderer, i1.item_name AS item_name, s.supplier AS supplier, s.supplier_code AS supplier_code
      FROM items i1
      JOIN items_suppliers is1
        ON i1.PK_ID = is1.FK_ID_items
      JOIN suppliers s
        ON is1.FK_ID_suppliers = s.PK_ID
      WHERE i1.item_name = 'Name1'
    UNION ALL
    SELECT '2', i2.item_name, s.supplier, s.supplier_code
      FROM items i1
      JOIN items_suppliers is1
        ON i1.PK_ID = is1.FK_ID_items
      JOIN items_suppliers is2
        ON is1.FK_ID_suppliers = is2.FK_ID_suppliers
      JOIN items i2
        ON is2.FK_ID_items = i2.PK_ID
      JOIN suppliers s
        ON is1.FK_ID_suppliers = s.PK_ID
      WHERE i1.item_name = 'Name1'
        AND i2.PK_ID <> i1.PK_ID
    ) r
  ORDER BY orderer, item_name, supplier;

不过，根据 Thorsten Kettner 的评论，我意识到我的 SQL 有点生疏且过时了；他的建议看起来像这样:

SELECT i2.item_name, s.supplier, s.supplier_code
  FROM items i1
  JOIN items_suppliers is1
    ON i1.PK_ID = is1.FK_ID_items
  JOIN items_suppliers is2
    ON is1.FK_ID_suppliers = is2.FK_ID_suppliers
  JOIN items i2
    ON is2.FK_ID_items = i2.PK_ID
  JOIN suppliers s
    ON is1.FK_ID_suppliers = s.PK_ID
  WHERE i1.item_name = 'Name1'
  ORDER BY
    CASE WHEN i2.item_name = 'Name1'
         THEN 1
         ELSE 2
      END,
    i2.item_name, s.supplier;