sql - 从 SQL 查询中过滤数据

Question

在下表中，我需要从表中排除CategoryID=1002并Gender='KIDS'记录并返回其他记录。

+------+--------+
|  ID  | Gender |
+------+--------+
| 1001 | MENS   |
| 1002 | MENS   |
| 1001 | WOMENS |
| 1002 | WOMENS |
| 1001 | KIDS   |
| 1002 | KIDS   |
+------+--------+

预期成绩：

+------+--------+
|  ID  | Gender |
+------+--------+
| 1001 | MENS   |
| 1002 | MENS   |
| 1001 | WOMENS |
| 1002 | WOMENS |
| 1001 | KIDS   |
+------+--------+

标签： sqlsql-servertsqlfiltering

Answer 1

您可以使用CASE表达式作为

SELECT *
FROM
(
  VALUES
  (1001,    'MENS'),
  (1002,    'MENS'),
  (1001,    'WOMENS'),
  (1002,    'WOMENS'),
  (1001,    'KIDS'),
  (1002,    'KIDS')
) T(Id, Gender)
WHERE CASE WHEN ID = 1002 AND Gender = 'KIDS' THEN 1 ELSE 0 END = 0;

在线演示