SQL选择具有多个值的行

I have these 2 tables:

我有这两个表：

Table SW_ITEM:

表SW_ITEM：

ID  SWID  ITEM_ID
1    1      99
2    2      99
3    5      99
4    2      100
5    1      100
6    1      101
7    2      102

Table ITEM:

表项目：

  ID   FILENAME
  99      abc
  100     def
  101     geh
  102     ijk

column ITEM_ID is a foreign key to the column ID of table ITEM.

列ITEM_ID是表ITEM的列ID的外键。

So I want all filenames which have the SWID "1" AND "2" (that would be ITEMID 99 and 100, so their filenames are "abc" and "def")

所以我想要所有SWID为“1”和“2”的文件名（即ITEMID 99和100，所以他们的文件名是“abc”和“def”）

Here I have to say that it is possible that ITEM_ID has more than one entry with the same SWID, so I cannot use this SQL:

在这里我不得不说ITEM_ID有可能有多个具有相同SWID的条目，所以我不能使用这个SQL：

SELECT ITEM_ID FROM SW_ITEM
WHERE SWID  IN (1,2) 
GROUP BY ITEM_ID
HAVING COUNT(ITEM_ID) = 2

So is there any other possibility to get all entries which have the SWID 1 and 2 (creating a join for every SWID is also not an option - because with many entries it would be really slow)

那么还有其他任何可能获得具有SWID 1和2的条目（为每个SWID创建连接也不是一个选项 - 因为有很多条目它会非常慢）

Kind regards

亲切的问候

2 个解决方案

#1

You need to use DISTINCT in COUNT and count SWID instead of ITEM_ID:

您需要在COUNT中使用DISTINCT并计算SWID而不是ITEM_ID：

SELECT ITEM_ID FROM SW_ITEM
WHERE  SWID IN (1,2) 
GROUP  BY ITEM_ID
HAVING COUNT(DISTINCT SWID) = 2;

Please checkout this demo.

请查看此演示。

To retrieve all filenames, try:

要检索所有文件名，请尝试：

SELECT ITEM_ID, FILENAME
FROM   ITEM JOIN SW_ITEM ON ITEM.ID = SW_ITEM.ITEM_ID
WHERE  SWID IN (1,2) 
GROUP  BY ITEM_ID
HAVING COUNT(DISTINCT SWID) = 2;

Demo

#2

I have a little different problem where I have to find a person with multiple entries in the same table based on email for that the above solution didn't work for me. You can try using the following,

我有一个不同的问题，我必须根据电子邮件找到同一个表中有多个条目的人，因为上述解决方案对我不起作用。您可以尝试使用以下方法，

SELECT person_id, 
(ROW_NUMBER () OVER (PARTITION BY pers_email ORDER BY pers_name) person_count  
from pers_table
WHERE person_count > 2;

Try this hope it works :)

试试这个希望它有效:)