我需要从表
中提取一些数据例如:
+-------------+------------+-------------+------------+
| Code | Progress | QTY | Faulty |
+-------------+------------+-------------+------------+
| A | 10 | 100 | 0 |
+-------------+------------+-------------+------------+
| B | 1 | 10 | 0 |
+-------------+------------+-------------+------------+
| C | 4 | 0 | 1 |
+-------------+------------+-------------+------------+
| D | 7 | 100 | 0 |
+-------------+------------+-------------+------------+
SELECT Code, Progress, QTY, Faulty
FROM myTable
WHERE Progress <> 7 AND Progress = 4 AND QTY > 0
如何选择所有具有-进度<> 7-和进度4,但仅当进度为的行QTY大于0时-,"故障"的值为1?
请尝试一下
SELECT
Code,
Progress,
QTY,
Faulty
FROM myTable
WHERE Progress NOT IN(4,7)
OR (Progress = 4 AND QTY > 0 AND Faulty = 1)
解释:
条件如Progress <> 7
,则记录为Progress = 4
。该特定记录可能没有QTY > 0 AND Faulty = 1
。
这就是为什么用Progress NOT IN (4,7)
作为第一条件。
现在你需要担心第二种情况,就像你提到的那样,它很直接。
(Progress = 4 AND QTY > 0 AND Faulty = 1)
具有Progress = 4 having QTY >0 and Faulty = 1
的记录将被纳入最终结果集。
select * from table
where Progress<>7
and Progress in (select Progress from table t where QTY>0 and Faulty =1)