带有“垂直"的MSSQL Select-其中 [英] MSSQL Select with "vertical"-where
本文介绍了带有“垂直"的MSSQL Select-其中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
除了垂直位置",我真的不知道该怎么解释.
I don't really know how to explain except with "vertical where".
想象一下下表:
TAGID|PRODUCTID|SHOP_ID
59 |3418-7 |38
61 |3418-7 |38
60 |4227-4 |38
61 |4227-4 |38
现在,我想返回所有与标签ID相关的产品ID:59,61.换句话说,两个标签ID都存在行的产品ID的值.
Now I want to return all product IDs, that have relation to the tag IDs: 59,61. In other words, values of product ID where rows exist for both tag IDs.
所以我想返回3418-7,而不是4227-4
So I want to return 3418-7, but not 4227-4
如何在SQL语句中将其写得尽可能简单?
How do I write this as simple as possible in a SQL statement?
这是我到目前为止的工作声明,但我认为可以用一种更明智的方式来完成此操作:
This is the working statement I have so far, but I feel this could be done in a much smarter way:
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
推荐答案
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
这篇关于带有“垂直"的MSSQL Select-其中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文