具有多个条件的 SQLite CASE 查询

Question

我正在尝试执行查询以查找同一周同一商店中各种产品的最低价格

当我只有两个产品时，查询工作正常。

当我有 3 个或更多产品时，我做错了什么，但不知道是什么。

这是我用于两个产品的产品(工作正常(：

select a.* , b.*,
case when a.preis_pro_unze < b.preis_pro_unze then a.preis_pro_unze 
when b.preis_pro_unze < a.preis_pro_unze then b.preis_pro_unze
end as minpreis
FROM Heinz32_36 as a, Heinz32_36 as b 
WHERE
a.IRI_KEY = b.IRI_KEY 
AND a.week = b.week
AND a.UPC_DESC != b.UPC_DESC 

这是我为三种产品尝试的(不起作用(：

Select a.UPC_DESC, a.IRI_KEY , a.WEEK , a.Price_per_unit, a.Preis_pro_unze, 
b.UPC_DESC, b.IRI_KEY , b.WEEK , b.Price_per_unit, b.Preis_pro_unze, 
c.UPC_DESC, c.IRI_KEY , c.WEEK , c.Price_per_unit, c.Preis_pro_unze, 
case 
when min(min(a.preis_pro_unze , b.preis_pro_unze , c.preis_pro_unze )) = a.preis_pro_unze then a.preis_pro_unze
when min(min(a.preis_pro_unze , b.preis_pro_unze , c.preis_pro_unze )) = b.preis_pro_unze then b.preis_pro_unze
when min(min(a.preis_pro_unze , b.preis_pro_unze , c.preis_pro_unze )) = c.preis_pro_unze then c.preis_pro_unze
end as minpreis
FROM "prv24" as a , "prv24" as b, "prv24" as c
WHERE 
a.IRI_KEY = b.IRI_KEY = c.IRI_KEY AND
a.week = b.week = c.week AND
a.UPC_DESC  != b.UPC_DESC or a.UPC_DESC  != c.UPC_DESC
OR 
b.UPC_DESC != c.UPC_DESC  

限制 10

它可能是最后带有"！="条件的东西。我已经尝试用一些括号语句重写它，但这也没有完成这项工作。

我正在使用SQLite。我没有收到任何错误消息，但查询正在无休止地处理。

Answer 1

如果没有示例数据和架构，很难确定，但我认为您缺少一些括号。OR条件意味着您的查询正在评估所有AND条件，然后添加OR条件;这可能会导致一个巨大的数据集，这可以解释无休止的处理。

Select a.UPC_DESC, 
a.IRI_KEY , 
a.WEEK , 
a.Price_per_unit, 
a.Preis_pro_unze, 
b.UPC_DESC, 
b.IRI_KEY , 
b.WEEK , 
b.Price_per_unit, 
b.Preis_pro_unze, 
c.UPC_DESC, 
c.IRI_KEY , 
c.WEEK , 
c.Price_per_unit, 
c.Preis_pro_unze, 
case 
when min(min(a.preis_pro_unze , b.preis_pro_unze , c.preis_pro_unze )) = a.preis_pro_unze then a.preis_pro_unze
when min(min(a.preis_pro_unze , b.preis_pro_unze , c.preis_pro_unze )) = b.preis_pro_unze then b.preis_pro_unze
when min(min(a.preis_pro_unze , b.preis_pro_unze , c.preis_pro_unze )) = c.preis_pro_unze then c.preis_pro_unze
end as minpreis
FROM "prv24" as a , 
"prv24" as b, 
"prv24" as c
WHERE 
a.IRI_KEY = b.IRI_KEY = c.IRI_KEY AND
a.week = b.week = c.week AND
(a.UPC_DESC  != b.UPC_DESC or 
a.UPC_DESC  != c.UPC_DES  OR 
b.UPC_DESC != c.UPC_DESC)  

但是，您可能会发现，如果使用显式联接语法并使用有意义的别名，则更有意义。显式联接避免了您刚刚遇到的问题(弄乱联接的 OR 条件(，并使代码更易于阅读。

这是一个更整洁的版本：

Select a.UPC_DESC, 
a.IRI_KEY, 
a.WEEK, 
a.Price_per_unit, 
a.Preis_pro_unze, 
b.UPC_DESC, 
b.IRI_KEY, 
b.WEEK, 
b.Price_per_unit, 
b.Preis_pro_unze, 
c.UPC_DESC, 
c.IRI_KEY , 
c.WEEK , 
c.Price_per_unit, 
c.Preis_pro_unze, 
case 
when min(min(a.preis_pro_unze , b.preis_pro_unze , c.preis_pro_unze )) = a.preis_pro_unze then a.preis_pro_unze
when min(min(a.preis_pro_unze , b.preis_pro_unze , c.preis_pro_unze )) = b.preis_pro_unze then b.preis_pro_unze
when min(min(a.preis_pro_unze , b.preis_pro_unze , c.preis_pro_unze )) = c.preis_pro_unze then c.preis_pro_unze
end as minpreis 
FROM "prv24" as a 
inner join  "prv24" as b
on a.IRI_KEY = b.IRI_KEY
and a.week = b.week
inner join "prv24" as c
on a.IRI_KEY = c.IRI_KEY
and a.week = c.week
WHERE 
(a.UPC_DESC  != b.UPC_DESC or 
a.UPC_DESC  != c.UPC_DES  or 
b.UPC_DESC != c.UPC_DESC)