多行 SQL Where 子句 [英] Multiple row SQL Where clause

问题描述

这可能是一个简单的 SQL 语句，但是我已经有一段时间没有完成 SQL 并且遇到了问题.我有这个桌子设计:

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:

 ID   PositionId    Qty     LeagueId
 1        1          1         5
 2        3          2         5
 3        8          5         2
 4        1          6         4

我需要得到的是所有具有特定 PositionId 和 Qty 的行.类似的东西:

What I need to get are all the rows that have specific PositionId's and Qty's. Something like:

 SELECT       ID, PositionId, LeagueId, Qty
 FROM         Lineups
 WHERE        (PositionId = 1 AND Qty = 1) AND (PositionId = 3 AND Qty = 2)

我想要得到的是 LeagueId 5 返回，因为它的 PositionId 为 1 和数量 1，PositionId 为 3 和数量 2.我不想使用 OR 语句，因为如果我将 WHERE 更改为:

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:

 WHERE (PositionId = 1 AND Qty = 1) OR (PositionId = 3 AND Qty = 1)

然后仍会返回 5 的 LeagueId.

Then LeagueId of 5 will still get returned.

推荐答案

试试这个:

   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

这篇关于多行 SQL Where 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！