SQL 选择除某些行之外的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7198748/
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
Select all except some rows
提问by nukl
let's say i have table with this structure:
假设我有这样结构的表：
id model color
------ -------- ----------
1 Ford yellow
2 Ford green
3 Ford red
4 Ford yellow
5 Subaru yellow
6 Subaru red
I need to make a query, which returns me every car in list, except for yellow ford's. Can somebody help?
我需要做一个查询，它会返回列表中的每辆车，除了黄色福特的。有人可以帮忙吗？
回答by Foo Bah
... WHERE NOT (model = 'Ford' AND color = 'yellow')
回答by Pran
If you want to deselect some rows dynamically, you may use it:
如果要动态取消选择某些行，可以使用它：
SELECT * FROM `table_1` WHERE id NOT IN (SELECT id FROM `table_2` WHERE column_name='value')
NOTE: Here, idis a column_name, which both tables have in common.
注意：这里，id是一个 column_name，这两个表有共同之处。
Good luck. :)
祝你好运。:)
回答by Kadir Can
In addition to Foo Bah's answer,
除了 Foo Bah 的回答，
if you have null column values like that;
如果您有这样的空列值；
NAME COLOR
Ford green
Ford red
Subaru yellow
Subaru red
Subaru NULL
Ford NULL
you need to use ISNULL() function to bring null column values
您需要使用 ISNULL() 函数来带空列值
... WHERE NOT (ISNULL(NAME,'') = 'Ford' AND ISNULL(COLOR,'') = 'yellow')
回答by Muhammad Wasim Akram
Hope this will help you.
希望这会帮助你。
$this->db->where('model !=','FORD' AND 'color!=','yellow');
$query= $this->db->get('table_name');
$res = $query->result_array();
return $res;
回答by Mindfulgeek
Use Color<> "yellow" and model <> "Ford" as your where clause
使用 Color<> "yellow" 和 model <> "Ford" 作为你的 where 子句
