mysql - 如何在SQL中跳过特定行

我的数据库中有一个表，看起来像：

+-------------+---------+--------+
| ProductName | Status  | Branch |
+-------------+---------+--------+
| P1          | dead    |      1 |
| P1          | dead    |      2 |
| P2          | expired |      1 |
| P3          | expired |      3 |
+-------------+---------+--------+

我想将比较后的结果显示为：（比较分支1和分支2的产品）

+-------------+---------+--------+
| ProductName | Branch 1|Branch 2|
+-------------+---------+--------+
| P1          | dead    |    dead|
| P2          | expired |    OK  |
+-------------+---------+--------+

但在我运行以下查询之后：

<pre>
SELECT  ProductName,
MAX(CASE
WHEN Branch=1
THEN Status
ELSE NULL
END) AS 'Branch 1',
MAX(CASE
WHEN Branch=2
THEN Status
ELSE NULL
END) AS 'Branch 2'
FROM    MyTable
GROUP BY ProductName;

我得到的结果是：

+-------------+---------+--------+
| ProductName | Branch 1|Branch 2|
+-------------+---------+--------+
| P1          | dead    |    dead|
| P2          | expired |    OK  |
| P3          | OK      |    OK  |
+-------------+---------+--------+

SQLFiddle
我要的是不要在表中包含除分支1或分支2以外的产品。谢谢

最佳答案

使用WHERE Branch IN(1,2)筛选查询结果

SELECT  ProductName,
      COALESCE(  MAX(CASE
            WHEN Branch=1
            THEN Status
            ELSE NULL
        END) ,'OK') AS 'Branch 1',
       COALESCE( MAX(CASE
            WHEN Branch=2
            THEN Status
            ELSE NULL
        END),'OK') AS 'Branch 2'
FROM    MyTable
WHERE Branch IN(1,2)
GROUP BY ProductName

DEMO

关于mysql - 如何在SQL中跳过特定行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31807352/