多行 SQL Where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3488371/
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
Multiple row SQL Where clause
提问by Kris B
This is probably a simple SQL statement, but it's been a while since I've done SQL and I'm having issues with it. I have this table design:
这可能是一个简单的 SQL 语句，但是我已经有一段时间没有完成 SQL 并且遇到了问题。我有这个桌子设计：
ID PositionId Qty LeagueId
1 1 1 5
2 3 2 5
3 8 5 2
4 1 6 4
What I need to get are all the rows that have specific PositionId's and Qty's. Something like:
我需要得到的是所有具有特定 PositionId 和 Qty 的行。就像是：
SELECT ID, PositionId, LeagueId, Qty
FROM Lineups
WHERE (PositionId = 1 AND Qty = 1) AND (PositionId = 3 AND Qty = 2)
What I'm trying to get is LeagueId 5 returned since it has both PositionId of 1 and Qty 1 and PositionId of 3 and Qty 2. I don't want to use an OR statement because if I change the WHERE to:
我想要得到的是 LeagueId 5 返回，因为它的 PositionId 为 1，数量为 1，PositionId 为 3 和数量为 2。我不想使用 OR 语句，因为如果我将 WHERE 更改为：
WHERE (PositionId = 1 AND Qty = 1) OR (PositionId = 3 AND Qty = 1)
Then LeagueId of 5 will still get returned.
然后仍会返回 5 的 LeagueId。
回答by Will A
A general way of performing this would be:
执行此操作的一般方法是：
SELECT LeagueId
FROM Lineups
WHERE (PositionId = 1 AND Qty = 1) OR (PositionId = 3 AND Qty = 2) OR ...
GROUP BY LeagueId
HAVING COUNT(*) = <number of OR'ed together clauses>
回答by Charles Bretana
Try this:
尝试这个：
Select Distinct LeagueId
From LineUps L
Where Exists (Select * From LineUps
Where LeagueId = L.LeagueId
And PositionId = 1
And Qty = 1)
And Exists (Select * From LineUps
Where LeagueId = L.LeagueId
And PositionId = 3
And Qty = 2)
This more closely semantically represents your intent
这在语义上更接近地代表您的意图
回答by Nicolas78
This should return 5:
这应该返回 5：
SELECT DISTINCT lineups1.leagueid
FROM lineups AS lineups1 INNER JOIN lineups AS LINEUPS2
ON lineups1.LeagueId=lineups2.LeagueId
WHERE lineups1.PositionId=1 AND lineups2.Qty = 1
AND lineups2.PositionId=3 AND lineups2.Qty = 2
Since you can only select single rows, you have to JOIN another table if you want to consider more than one. In this case, the table you're "self-joining" lineups, retrieving the value from one row based on conditions from another row (of course it doesn't matter whose leagueid you take because they're identical).
由于您只能选择单行，如果您想考虑多个表，则必须加入另一个表。在这种情况下，表是“自加入”阵容，根据另一行的条件从一行中检索值（当然，您选择谁的 Leagueid 并不重要，因为它们是相同的）。
UpdateYou can of course extend this to
更新您当然可以将其扩展到
SELECT lineups1.ID, ..., lineupts2.ID, ...
to retrieve whichever fields you want to retrieve.
检索您要检索的任何字段。
回答by MrRobot614
SELECT DISTINCT LeagueId /*to display non-repeating record*/
FROM Lineups
WHERE PositionId in (1,3) AND Qty in (1,2) /*OR*/
The First statement will return 2 records which the League ID's are 5, but if your intention is to get the league ID's containing those positions and QTY, replace the 'AND' with 'OR' then it will return league Id 4 and 5.
First 语句将返回 2 条记录，其中联赛 ID 为 5，但如果您打算获取包含这些位置和数量的联赛 ID，请将“AND”替换为“OR”，然后它将返回联赛 ID 4 和 5。
回答by silvo
You can also try:
你也可以试试：
SELECT ID, PositionId, LeagueId, Qty
FROM Lineups
WHERE (PositionId = 1 AND Qty = 1)
AND ID IN (SELECT ID FROM Lineups WHERE PositionId=3 AND Qty=2)
