MSSQL 选择“垂直”-where

Question

除了“垂直位置”之外，我真的不知道如何解释。

想象一下下表：

TAGID|PRODUCTID|SHOP_ID
59   |3418-7   |38
61   |3418-7   |38
60   |4227-4   |38
61   |4227-4   |38

现在我想返回与标签 ID：59,61 相关的所有产品 ID。换句话说，两个标签 ID 都存在行的产品 ID 值。

所以我想返回 3418-7，但不是 4227-4

如何在 SQL 语句中尽可能简单地编写它？

这是我到目前为止的工作声明，但我觉得这可以通过更聪明的方式来完成：

SELECT 
  productid 
FROM shop_tag_relations 
WHERE 
  productid IN (select productid from shop_tag_relations WHERE tagid=59) 
AND 
  productid IN (select productid from shop_tag_relations WHERE tagid=61)
GROUP BY productid,shop_id

Answer 1

SELECT PRODUCTID
FROM T
WHERE TAGID IN (59,61)
GROUP BY PRODUCTID
HAVING COUNT(DISTINCT TAGID) = 2

或者

SELECT PRODUCTID
FROM T
WHERE TAGID = 59
INTERSECT
SELECT PRODUCTID
FROM T
WHERE TAGID = 61

Answer 2

SELECT DISTINCT 
a.PRODUCTID 
FROM mytable AS a
INNER JOIN mytable AS b ON a.PRODUCTID=b.PRODUCTID
WHERE a.TAGID=59
AND b.TAGID=61
;

Answer 3

SELECT ProductId
FROM shop_tag_relations 
WHERE TAGID IN (59,61)
GROUP BY ProductId
HAVING COUNT(DISTINCT TagId) = 2