使SQL查询具有多个条件和变量更高效。

I have this query which does get the results I require but is incredibly slow and surely there must be a better way of doing this as I would need to run this regularly.

我有这样一个查询，它确实得到了我需要的结果，但是速度非常慢，而且肯定有更好的方法来做这个，因为我需要经常运行这个。

Each where clause has two variables which will always be the same number but I need to use this with 50+ BigIDs, the example shows 3 but I would do it for BigID 1 to 50+.

每个where子句都有两个变量，它们的值总是相同的，但是我需要用这个来表示50+ BigIDs，这个例子显示了3，但是对于BigID 1到50+我要这样做。

I am unsure how to make this shorter because of the two variables (one of which being in a subquery) and group by which is required. Any help or pointing in the right direction would be appreciated. Thanks.

由于需要两个变量(其中一个在子查询中)和组，所以我不确定如何将其缩短。任何帮助或指向正确的方向都是值得赞赏的。谢谢。

 SELECT BigID,count(LittleID)
      FROM Table1
      where ( (BigID=1 and LittleID not in (SELECT LittleID FROM Table2 where BigID=1)) or
              (BigID=2 and LittleID not in (SELECT LittleID FROM Table2 where BigID=2)) or
              (BigID=3 and LittleID not in (SELECT LittleID FROM Table2 where BigID=3)) )
      group by BigID

3 个解决方案

#1

One method is a correlated subquery:

一种方法是关联子查询:

  SELECT t1.BigID, count(t1.LittleID)
  FROM Table1 t1
  WHERE t1.BigID IN (1, 2, 3) and
        t1.LittleID not in (SELECT t2.LittleID
                            FROM Table2 t2
                            WHERE t2.BigID = t1.BigId
                           )
  GROUP BY t1.BigID

#2

SELECT t1.BigID, COUNT(t1.LittleID)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.LittleID = t2.LittleID AND t1.BigID = t2.BigID
WHERE t1.BigID IN (1, 2, 3)
    AND t2.LittleID IS NULL
GROUP BY t1.BigID

#3

SELECT Table1.BigID,
       COUNT(Table1.LittleID)
FROM Table1
LEFT JOIN Table2 ON Table1.LittleID = Table2.LittleID
    AND Table1.BigID = Table2.BigID
WHERE Table2.LittleID IS NULL
    AND Table1.BigID IN (1, 2, 3)
GROUP BY Table1.BigID