sqlite 如何将行中的值与另一个表中的列进行比较

vh0rcniy 于 2023-04-30 发布在 SQLite

关注(0)|答案(1)|浏览(204)

我有两个表Customer_Foods和Food_Options。Customer_Foods在某些列中包含空白值。我试图找出Customer_Foods的给定行中哪些值从Food_Options的值列表中缺失。最后，我想用第二个表中的选项填充Customer_Foods表的空白处，而不重复每个ID的选项。
Customer_Foods：
| ID|食品1|食品2|食品3|食品4|
| --------------|--------------|--------------|--------------|--------------|
| 一一一|苹果|空白|梨|樱桃|
| 一百一十二|苹果|香蕉|橙子|空白|
| 一百一十三|橙子|樱桃|空白|空白|
Food_Options：
| 食品|
| --------------|
| 苹果|
| 樱桃|
| 香蕉|
| 橙子|
| 梨|
我一直在尝试使用这个NOT IN查询，但却被如何从Customer_Foods表的一行中收集可比较的值列表以与Food_Options表的单个列进行比较所难倒

SELECT *
FROM Food_Options
WHERE Foods NOT IN (SELECT *
                    FROM Customer_Foods
                    WHERE ID = 113)

如果我列出一个列名（即Food 1）子查询，它就可以工作，但我不知道如何一次在所有Customer_Foods列中执行此操作。

SELECT *
FROM Food_Options
WHERE Foods NOT IN (SELECT Food1
                    FROM Customer_Foods
                    WHERE ID = 113)

sqlite

来源：https://stackoverflow.com/questions/76125196/how-to-compare-values-in-a-row-to-a-column-in-another-table

1条答案

按热度按时间

blmhpbnm1#

您需要选择子查询中的所有food列，使用union all和CTE，您可以将查询修改为：

WITH CTE AS
(
  SELECT ID, Food1 AS Food FROM Customer_Foods
  UNION ALL
  SELECT ID, Food2 FROM Customer_Foods
  UNION ALL
  SELECT ID, Food3 FROM Customer_Foods
  UNION ALL 
  SELECT ID, Food4 FROM Customer_Foods
 )

SELECT *
FROM Food_Options
WHERE Foods NOT IN
    (SELECT Food
     FROM CTE
     WHERE ID = 113)

如果你的DBMS支持unpivot功能（如SQL Server），你可以使用它来代替union。
要从Food_Options表中获取一行空白食物列被缺失的食物值替换，请尝试以下操作：

WITH CTE AS( -- unpivot keeping the order of each column
  SELECT ID, Food1 AS Foods, 1 AS ord FROM Customer_Foods
  UNION ALL
  SELECT ID, Food2, 2 FROM Customer_Foods
  UNION ALL
  SELECT ID, Food3, 3 FROM Customer_Foods
  UNION ALL 
  SELECT ID, Food4, 4 FROM Customer_Foods
),
CTE2 AS ( -- get the missing foods and give them numbering 
  SELECT FO.Foods,C1.ord,C1.ID,
     ROW_NUMBER() OVER (ORDER BY FO.Foods) RN
  FROM Food_Options FO LEFT JOIN CTE C1 ON FO.Foods=C1.Foods  AND C1.ID= 113
  WHERE C1.ID IS NULL
),
CTE3 AS( -- match the missing foods based on the order of columns (ord) and missing foods order
  SELECT C1.ID, C1.ord, COALESCE(NULLIF(C1.Foods,''),C2.Foods) Foods
  FROM 
   (
    SELECT ID, ord,Foods, 
      ROW_NUMBER() OVER (ORDER BY CASE WHEN Foods='' THEN 1 ELSE 2 END, ord) RN 
    FROM CTE WHERE ID = 113
   ) C1  LEFT JOIN CTE2 C2  
  ON C2.RN = C1.RN
)
SELECT ID, -- use conditional aggregation to re-pivot the data
  MAX(CASE WHEN ord=1 THEN Foods END) Food1,
  MAX(CASE WHEN ord=2 THEN Foods END) Food2,
  MAX(CASE WHEN ord=3 THEN Foods END) Food3,
  MAX(CASE WHEN ord=4 THEN Foods END) Food4
FROM CTE3
GROUP BY ID

ID=113的输出：

ID  Food1   Food2   Food3   Food4
113 Orange  Cherry  Apple   Banana

demo

赞(0）回复(0）举报 2023-04-30

我来回答

sqlite 如何将行中的值与另一个表中的列进行比较

1条答案

相关问题

热门标签

最新问答