English | 正體中文 | 简体中文

SQL in,not in


范例资料表 : Orders (以下范例皆用此表当作资料来源)
OrderNo CustomerName Shipped SalePrice Quantity
20101213095 John True 13500 10
20100620135 Jimmy False 25096 22
20100620392 Vincent False 7200 5
20100530006 Tyler True 92500 50
20100515029 Alan False 100 50


Example
寻找指定栏位中是否有相等于多个搜寻值的资料列

Sytax : SELECT * FROM table_name WHERE column_name IN('value1', 'value2'...)
SQL
SELECT * FROM Orders WHERE CustomerName IN ('John', 'Alan', 'Tyler')

会输出结果
OrderNo CustomerName Shipped SalePrice Quantity
20101213095 John True 13500 10
20100530006 Tyler True 92500 50
20100515029 Alan False 100 50


Example
寻找指定栏位中是否有相等于多个搜寻值(数字类型)的资料列

Sytax : SELECT * FROM table_name WHERE column_name IN(value1, value2...)
SQL
SELECT * FROM Orders WHERE SalePrice IN (7200, 25096, 100)

会输出结果
OrderNo CustomerName Shipped SalePrice Quantity
20100620135 Jimmy False 25096 22
20100620392 Vincent False 7200 5
20100515029 Alan False 100 50

Example
寻找指定栏位中是否有不等于任何一个搜寻值的资料列

Sytax : SELECT * FROM table_name WHERE column_name NOT IN(value1, value2...)
SQL
SELECT * FROM Orders WHERE SalePrice NOT IN (7200, 25096, 100)

会输出结果
OrderNo CustomerName Shipped SalePrice Quantity
20101213095 John True 13500 10
20100530006 Tyler True 92500 50

© 2012 PureExample.com contact