MySQL 使用 CASE WHEN 和 IN 子查询

Question

这是我想要实现的目标的简要说明；我的查询如下。

我有两张表，一张包含 500 种产品 product_limit500，另一张包含订单 order_limit1000。

我的目标是创建一个表，其中包含每个订单的所有 500 种产品，所有产品的顺序相同(如 订单一后跟所有其他产品，然后订单二后跟相同订单中的产品)。

这是我尝试使用的查询。

SELECT 
  r1.order_id, 
  r1.product_id, 
  r1.product_name,
  CASE WHEN p1.product_id IN (SELECT 
                                r2.product_id 
                              FROM 
                                order_limit1000 r2 
                              WHERE 
                                r2.order_id = r1.order_id) THEN 's'
  ELSE '?'
  END as 'torf'
FROM 
  order_limit1000 r1, 
  product_limit500 p1;

这是目前的结果。

  order_id  product_id  product_name    torf
   5    13176   Bag of Organic Bananas  ?
   5    13176   Bag of Organic Bananas  s
   5    13176   Bag of Organic Bananas  ?
   5    13176   Bag of Organic Bananas  ?
   5    13176   Bag of Organic Bananas  s
   5    13176   Bag of Organic Bananas  ?
   5    13176   Bag of Organic Bananas  ?
   5    13176   Bag of Organic Bananas  ?
   5    13176   Bag of Organic Bananas  ?
   5    13176   Bag of Organic Bananas  ?
   5    13176   Bag of Organic Bananas  s
   5    13176   Bag of Organic Bananas  ?
   ...

感谢您提供的任何帮助。

...

现在我使用的 View 包含 500 个产品和 1000 行订单，我仍在考虑订单的最终大小 code>，大概有 500k 到 1mi 行。但我使用的数据集有大约 4.5mi 的行。

预期结果如下所示。

 order_id   product_id  product_name    torf
    5           123        tomatoes      s
    5           124        limes         ?
    ...
    100         123        tomatoes      ?
    100         124        limes         ?

Answer 1

请不要在表名之间使用逗号，当然也不要这样做来替代交叉联接。我不确定为什么您希望每个产品都针对每个订单，但要做到这一点，您确实需要完整的笛卡尔积，因此您需要交叉连接。

SELECT
    r1.order_id
  , p1.product_id
  , p1.product_name
  , CASE
        WHEN r1.product_id = p1.product_id THEN 's'
        ELSE '?'
    END AS 'torf'
FROM order_limit1000 r1
CROSS JOIN product_limit500 p1
ORDER BY
    r1.order_id
  , p1.product_id
  , p1.product_name

鉴于订单表中已经有一个product_id，您不需要另一个相关子查询来测试是否应该输出“s”或“?”。注意:我假设产品名称来自产品表，而不是订单表。

  order_id   R1.product_id   P1.product_id   product_name   torf  
 ---------- --------------- --------------- -------------- ------ 
         5             123             123   tomatoes       s     << r1.product_id = p1.product_id
         5                             124   limes          ?     
       ...                                                        
       100                             123   tomatoes       ?     
       100                             124   limes          ?