MySQL 具有多个值的 SQL 语句 WHERE 子句条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28061790/
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 WHERE clause condition with multiple values
提问by nobalG
I was having some problem when trying to write a SQL statement with multiple values for WHERE clause condition. So basically I will draw a buffer on the map and it will return me a list of eventID.
我在尝试为 WHERE 子句条件编写具有多个值的 SQL 语句时遇到了一些问题。所以基本上我会在地图上绘制一个缓冲区，它会返回一个 eventID 列表。
So I created this SQL statement and tested it:
所以我创建了这个 SQL 语句并对其进行了测试：
SELECT count(*) AS totalRaces, a.races FROM bookedevent be INNER JOIN
account a
ON be.bookedEventBY = a.accountName
WHERE be.eventID = 70 OR be.eventID = 69 OR be.eventID = 55
GROUP BY a.races
It works. But then again, this SQL statement only works for 3 eventID. For some buffer it might return me up to 10 eventID. So how should I format it in this case?
有用。但是话又说回来，这个 SQL 语句只适用于 3 个 eventID。对于某些缓冲区，它可能会返回最多 10 个 eventID。那么在这种情况下我应该如何格式化它？
Thanks in advance.
提前致谢。
回答by nobalG
Use INclause
使用IN子句
SELECT count(*) AS totalRaces, a.races FROM bookedevent be INNER JOIN
account a
ON be.bookedEventBY = a.accountName
WHERE be.eventID in(70,69,55)
GROUP BY a.races
回答by Ewan
You Need to use dynamic SQL.
您需要使用动态 SQL。
Then you can pass your list of eventIDs as a comma separated list. You can then dynamically vary the number of items in the IN cluse
然后，您可以将 eventID 列表作为逗号分隔列表传递。然后，您可以动态改变 IN 语句中的项目数
declare @Events as varchar(1000)
declare @SQL as varchar(1000)
Select @Events = '55, 60, 70'
select @SQL = 'SELECT count(*) AS totalRaces, a.races
FROM bookedevent be
INNER JOIN account a ON be.bookedEventBY = a.accountName
WHERE be.eventID in (' + @Events + ')
GROUP BY a.races'
Exec (@SQL);
