SQL 如何在sql select中排除具有某些值的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15389091/
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
How to exclude records with certain values in sql select
提问by MisterIsaak
How do I only select the stores that don't have client 5?
我如何只选择没有客户的商店5？
StoreId ClientId
------- ---------
1 4
1 5
2 5
2 6
2 7
3 8
I'm trying something like this:
我正在尝试这样的事情：
SELECT SC.StoreId FROM StoreClients
INNER JOIN StoreClients SC
ON StoreClients.StoreId = SC.StoreId
WHERE SC.ClientId = 5
GROUP BY StoreClients.StoreId
That seems to get me all the stores that have that client but I can't do the opposite because if I do <> 5ill still get Store 1and 2which I don't want.
这似乎让我的一切，有客户门店，但我不能这样做，因为如果我做相反的<> 5病仍然获得商店1和2我不想要的。
I'm basically trying to use this result in another query's EXISTS INclause
我基本上是想在另一个查询的EXISTS IN子句中使用这个结果
回答by AdaTheDev
One way:
单程：
SELECT DISTINCT sc.StoreId
FROM StoreClients sc
WHERE NOT EXISTS(
SELECT * FROM StoreClients sc2
WHERE sc2.StoreId = sc.StoreId AND sc2.ClientId = 5)
回答by MarcinJuraszek
SELECT SC.StoreId
FROM StoreClients SC
WHERE SC.StoreId NOT IN (SELECT StoreId FROM StoreClients WHERE ClientId = 5)
In this way neither JOINnor GROUP BYis necessary.
这样既JOIN没有GROUP BY必要也没有必要。
回答by John Woo
SELECT DISTINCT a.StoreID
FROM tableName a
LEFT JOIN tableName b
ON a.StoreID = b.StoreID AND b.ClientID = 5
WHERE b.StoreID IS NULL
OUTPUT
输出
╔═════════╗
║ STOREID ║
╠═════════╣
║ 3 ║
╚═════════╝
回答by squillman
SELECT StoreId
FROM StoreClients
WHERE StoreId NOT IN (
SELECT StoreId
FROM StoreClients
Where ClientId=5
)
回答by lozsui
You can use EXCEPTsyntax, for example:
您可以使用EXCEPT语法，例如：
SELECT var FROM table1
EXCEPT
SELECT var FROM table2
回答by kombo
<>will surely give you all values not equal to 5.
If you have more than one record in table it will give you all except 5.
If on the other hand you have only one, you will get surely one.
Give the table schema so that one can help you properly
<>肯定会给你所有不等于 5 的值。如果你在表中有多个记录，它会给你除 5 之外的所有值。另一方面，如果你只有一个，你肯定会得到一个。给出表模式，以便人们可以正确地帮助您
