sql select查询排除某些值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20893403/
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 select query excluding certain values
提问by misguided
The requirement is to exclude ItemA and ItemE from the search as they contain value 1 in columnB.
要求是从搜索中排除 ItemA 和 ItemE，因为它们在 columnB 中包含值 1。
The final result should be something like this
最终的结果应该是这样的
I have tried SELECT * FROM my_table WHERE ColumnB not like '1', but this just removes the rows having 1 , I want the whole ITEM A/ITEM E removed as the contain value 1.
我已经尝试过SELECT * FROM my_table WHERE ColumnB not like '1'，但这只是删除了具有 1 的行，我希望将整个 ITEM A/ITEM E 作为包含值 1 删除。
回答by krokodilko
Try:
尝试：
SELECT *
FROM tablee
WHERE ColumnA NOT IN (
SELECT columnA FROM tablee
WHERE columnB = 1
);
or
或者
SELECT *
FROM tablee t1
WHERE NOT EXISTS(
SELECT 1 FROM tablee t2
WHERE t2.columnB = 1 and t1.columnA = t2.columnA
);
回答by Damien Black
First we make a sub-query that looks for rows with ColumnB = '1'. Then we left join that sub query to our main table on matching ColumnA names. Items that have a different row with a '1' in ColumnB will now have a value there, other rows will just have NULL because of the left join. We then use a where to only find the ones that didn't have a match.
首先，我们创建一个子查询来查找 ColumnB = '1' 的行。然后我们将该子查询加入到我们的主表中，以匹配 ColumnA 名称。在 ColumnB 中具有“1”的不同行的项目现在将在那里有一个值，由于左连接，其他行将只有 NULL。然后我们使用 where 只找到那些没有匹配的。
SELECT main.* from my_table AS main
LEFT JOIN (
SELECT * FROM my_table
WHERE ColumnB = '1'
) AS remove
ON remove.ColumnA = main.ColumnA
WHERE remove.ColumnB != '1'
To learn more about sql go through the primer at w3schools
要了解有关 sql 的更多信息，请阅读w3schools的入门
回答by misguided
Thanks for your help guys . I came up with the solution
谢谢你们的帮助。我想出了解决方案
select *
from test
where columnA not in (select columnA from test where ColumnB = 1 )
回答by vishal adatala
try this
尝试这个
select * from my_table where ColumnA!=ItemA and ColumnA!=ItemE;
