使用具有多个值的 Where 子句的 SQL 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10012695/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Statement using Where clause with multiple values
提问by Sophie
I have a table that has multiple rows with the following fields:
我有一个多行的表，其中包含以下字段：
PersonName SongName Status
I want to use names selected from a multiple selection listbox, which I can retrieve the values, and then do a where clause so it shows the song names that the selected people can all play, therefore status is complete.
我想使用从多选列表框中选择的名称，我可以检索值，然后执行 where 子句，以便显示所选人员都可以播放的歌曲名称，因此状态已完成。
For example:
例如：
PersonName SongName Status
Holly Highland Complete
Holly Mech Complete
Ryan Highland Complete
If I select Holly and Ryan from the list box and press the button the query should just show Highland as that is what they both know.
如果我从列表框中选择 Holly 和 Ryan 并按下按钮，查询应该只显示 Highland，因为这是他们都知道的。
回答by Mosty Mostacho
Try this:
尝试这个：
select songName from t
where personName in ('Ryan', 'Holly')
group by songName
having count(distinct personName) = 2
The number in the having should match the amount of people. If you also need the Status to be Completeuse this whereclause instead of the previous one:
拥有中的人数应与人数相匹配。如果您还需要 StatusComplete使用此where子句而不是前一个子句：
where personName in ('Ryan', 'Holly') and status = 'Complete'
回答by sll
SELECT PersonName, songName, status
FROM table
WHERE name IN ('Holly', 'Ryan')
If you are using parametrized Stored procedure:
如果您使用的是参数化存储过程：
- Pass in comma separated string
- Use special function to split comma separated string into table value variable
- Use
INNER JOIN ON t.PersonName = newTable.PersonNameusing a table variable which contains passed in names
- 传入逗号分隔的字符串
- 使用特殊函数将逗号分隔的字符串拆分为表值变量
- 使用
INNER JOIN ON t.PersonName = newTable.PersonName包含传入名称的表变量
回答by Sam DeHaan
Select t1.SongName
From tablename t1
left join tablename t2
on t1.SongName = t2.SongName
and t1.PersonName <> t2.PersonName
and t1.Status = 'Complete' -- my assumption that this is necessary
and t2.Status = 'Complete' -- my assumption that this is necessary
and t1.PersonName IN ('Holly', 'Ryan')
and t2.PersonName IN ('Holly', 'Ryan')
