带有复杂where子句的单列的SQL查询

Question

我有一个表，在那里我存储关于汽车变化(部件的变化)的详细信息。

例如，下面是CarChangeHistory表，它很少对几个汽车中的部件进行更改(部件名称是在另一个表中预定义的，并且永远不会与默认列表不同)：

CarChangeHistory
CarGUID PartName    PNPNNewValue TimeOfChange
X123    Windshield  344DFSS
X133    Lights      BN23112
X899    Battery     DNNN222
X433    Battery     SM12345

汽车桌看起来是这样的：

CarTable
CarGUID Lights Windshield Battery Handle Door Mfg
X123    11111  344DFSS

CarChangeHistory表中还有许多其他类似的条目。

如果下面的where子句为真，我希望执行搜索，并返回最后7天窗口内的所有项：

(Lights LIKE %BN%) AND (Battery = 'DLK222' OR Windshield = true) AND (...) AND (...)

我可以将它转换为将我的表与类似的东西相匹配(这是更符合逻辑的，伪代码。因为如果我要在SQL查询中单独使用它，它将不会返回任何内容。由于和从上面的连接将尝试做和每生，但我想做它每N个变化/ raw在过去7天)：

(PartName = 'Lights' AND PNNewValue LIKE  '%BN%') 
AND  
((PartName = 'Battery' AND PNNewValue =  'DLK222')  OR  (PartName = 'Windshield')) 
AND
(...)
AND
(...)

所以..。如果我忽略了空的(...)，使用上面的示例表，它将返回：

X123    Windshield  344DFSS
X133    Lights      BN23112

如果我的例子不符合Lights，它将不会返回任何..。

我想最大的问题是和连接，如何在这样的查询中处理它？如何使用这个where子句来执行这样的搜索？

我尝试过以下几种方法，但我需要更改where子句(通过将(Lights LIKE %BN%) AND (Battery = 'DLK222' OR Windshield = true)扩展为(Lights LIKE %BN% OR Windshield = true) OR (Lights LIKE %BN% OR Battery = 'DLK222'))。我想如果我有更多的条件，它会变得相当复杂。

SELECT TimeOfChange, CarGUID 
FROM CarChangeHistory
WHERE (
(((PartName = 'Lights' AND PNNewValue LIKE '%BN%') OR (PartName = 'Battery' AND PNNewValue = 'DLK222')))
OR
(((PartName = 'Lights' AND PNNewValue LIKE '%BN%') OR (PartName = 'Windshield')))
) AND TimeOfChange BETWEEN DATEADD(DAY,-7,GETDATE()) AND GETDATE()
GROUP BY TimeOfChange, CarGUID
HAVING COUNT(*) = 2

有谁知道更好的解决办法吗？怎样才能最好地将我的逻辑where子句(如果我运行就不会返回任何内容)转换为能够在最后7天(或任何时间窗口)中实际过滤表中的数据的东西。

Answer 1

如果我正确理解，您希望在历史表中查找更改。这个表没有名为挡风玻璃、灯等的列，而是一个列PartName。因此，对于一张记录，您有一个PartName。它不能同时成为“挡风玻璃”和“灯光”。这给您留下了两个选择：

1)“使用存在”条款：

select carguid
from cars c
where exists
(
  select * 
  from carchangehistory cch
  where cch.carguid = c.carguid
  and partname = 'Lights' and pnnewvalue like  '%BN%'
  and timeofchange between dateadd(day,-7,getdate()) and getdate()
)
and exists
(
  select * 
  from carchangehistory cch
  where cch.carguid = c.carguid
  and ((partname = 'Battery' and pnnewvalue =  'DLK222') or (partname = 'Windshield')) 
  and timeofchange between dateadd(day,-7,getdate()) and getdate()
)
and exists
(
  ...
)

这让你所有的汽车在过去7天内进行了所有的查询更改。但是它一次又一次地查询同一个表。

2)在有条件从句中使用GROUP

  select carguid
  from carchangehistory
  where timeofchange between dateadd(day,-7,getdate()) and getdate()
  group by carguid
  having count(case when partname = 'Lights' and pnnewvalue like '%BN%' then 1 end) > 0
  and count(case when (partname = 'Battery' and pnnewvalue =  'DLK222') or partname = 'Windshield' then 1 end) > 0
  and count(case when ... then 1 end) > 0
  ...;

这将给出相同的结果，但只扫描一次历史表。