mysql - MySQL case 语句中的额外列条件不起作用

Question

我有一个条件，MySQL 中的字段可以具有相同的值(端口号)，但可以基于主机 ID 代表两个不同的设备。我有以下内容，但似乎不起作用:

SELECT s.name AS 'ingester',
    d.dev_used AS 'size',
    Cast(CASE d.namespace_port
        (WHEN 4015 AND d.das_station IN(11,22,23)) THEN 'BPN1'
        (WHEN 4015 AND d.das_station NOT IN(11,22,23)) THEN 'SJC12'
        WHEN 4016 THEN 'SJC17'
        WHEN 4017 THEN 'SJC18'
        WHEN 4018 THEN 'SJC19'
        WHEN 4019 THEN 'SJC5'
        WHEN 4020 THEN 'SJC6'
        WHEN 4021 THEN 'SJC7'
        WHEN 4022 THEN 'SJC8'
        WHEN 4023 THEN 'SJC9'
        WHEN 4024 THEN 'SJC10'
        WHEN 4025 THEN 'SJC11'
        WHEN 4026 THEN 'SJC20'
        WHEN 4027 THEN 'SJC13'
        WHEN 4028 THEN 'SJC14'
        WHEN 4029 THEN 'SJC15'
        WHEN 4030 THEN 'SJC16'
    END AS CHAR) AS namespace,
    Date_format(d.date_attached, '%b-%e-%Y %l:%i %p') AS 'date_attached',
    Date_format(d.replication_started, '%b-%e-%Y %l:%i %p') AS 'date_started',
    Date_format(d.replication_completed, '%b-%e-%Y %l:%i %p') AS 'date_finished',
    Date_format(d.date_detached, '%b-%e-%Y %l:%i %p') AS 'date_detached'
FROM   das.stations s,
       das.device d
WHERE  d.das_station = s.id
AND svcid = "trrq"
ORDER  BY d.date_attached DESC

阅读MYSQL CASE STATEMENT MULTIPLE CONDITIONS ，看起来这应该是可能的，但是当我尝试执行查询时，我得到以下结果:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4015 AND d.das_station IN(11,22,23)) THEN 'BPN1'
 4015 AND d.das_station NOT IN (1' at line 4

在这样的 case 语句中不可能有 AND 条件吗？

编辑:粘贴到 Mysql 中似乎不喜欢查询

mysql> SELECT s.name AS 'ingester',
    ->     d.dev_used AS 'size',
    ->     Cast(CASE d.namespace_port
    ->
Display all 891 possibilities? (y or n)
    ->  (4015 AND d.das_station IN (11,22,23)) THEN 'BPN1'
    ->
Display all 891 possibilities? (y or n)
    ->  (4015 AND d.das_station NOT IN (11,22,23)) THEN 'SJC12'
    ->         WHEN 4016 THEN 'SJC17'
    ->         WHEN 4017 THEN 'SJC18'
    ->         WHEN 4018 THEN 'SJC19'
    ->         WHEN 4019 THEN 'SJC5'
    ->         WHEN 4020 THEN 'SJC6'
    ->         WHEN 4021 THEN 'SJC7'
    ->         WHEN 4022 THEN 'SJC8'
    ->         WHEN 4023 THEN 'SJC9'
    ->         WHEN 4024 THEN 'SJC10'
    ->         WHEN 4025 THEN 'SJC11'
    ->         WHEN 4026 THEN 'SJC20'
    ->         WHEN 4027 THEN 'SJC13'
    ->         WHEN 4028 THEN 'SJC14'
    ->         WHEN 4029 THEN 'SJC15'
    ->         WHEN 4030 THEN 'SJC16'
    ->     END AS CHAR) AS namespace,
    ->     Date_format(d.date_attached, '%b-%e-%Y %l:%i %p') AS 'date_attached',
    ->     Date_format(d.replication_started, '%b-%e-%Y %l:%i %p') AS 'date_started',
    ->     Date_format(d.replication_completed, '%b-%e-%Y %l:%i %p') AS 'date_finished',
    ->     Date_format(d.date_detached, '%b-%e-%Y %l:%i %p') AS 'date_detached'
    -> FROM   das.stations s,
    ->        das.device d
    -> WHERE  d.das_station = s.id
    -> AND svcid = "trrq"
    -> ORDER  BY d.date_attached DESC

如果我删除换行，它似乎可以正确粘贴，但仍然不尊重第二个字段:

mysql> SELECT s.name AS 'ingester',    d.das_station,  d.dev_used AS 'size',     Cast(CASE d.namespace_port WHEN (4015 AND d.das_station = 11) THEN 'BPN1' WHEN (4015 AND d.das_station NOT IN(11,22,23)) THEN 'SJC12' WHEN 4016 THEN 'SJC17' WHEN 4017 THEN 'SJC18' WHEN 4018 THEN 'SJC19' WHEN 4019 THEN 'SJC5' WHEN 4020 THEN 'SJC6'  WHEN 4021 THEN 'SJC7' WHEN 4022 THEN 'SJC8'  WHEN 4023 THEN 'SJC9' WHEN 4024 THEN 'SJC10' WHEN 4025 THEN 'SJC11' WHEN 4026 THEN 'SJC20' WHEN 4027 THEN 'SJC13' WHEN 4028 THEN 'SJC14' WHEN 4029 THEN 'SJC15' WHEN 4030 THEN 'SJC16' END AS CHAR) AS namespace,     Date_format(d.date_attached, '%b-%e-%Y %l:%i %p') AS 'date_attached',     Date_format(d.replication_started, '%b-%e-%Y %l:%i %p') AS 'date_started',     Date_format(d.replication_completed, '%b-%e-%Y %l:%i %p') AS 'date_finished',     Date_format(d.date_detached, '%b-%e-%Y %l:%i %p') AS 'date_detached' FROM   das.stations s,        das.device d WHERE  d.das_station = s.id AND svcid = "trrq" ORDER  BY d.date_attached DESC;
+---------------+-------------+------+-----------+---------------------+--------------+---------------------+---------------------+
| ingester      | das_station | size | namespace | date_attached       | date_started | date_finished       | date_detached       |
+---------------+-------------+------+-----------+---------------------+--------------+---------------------+---------------------+
| ca-adsdas2-p1 |          11 | NULL | NULL      | Jan-20-2016 1:42 PM | NULL         | NULL                | Jan-21-2016 3:16 PM |
| ca-adsdas2-p1 |          11 | NULL | NULL      | Dec-21-2015 7:25 PM | NULL         | Dec-26-2015 9:22 PM | Jan-20-2016 1:33 PM |
+---------------+-------------+------+-----------+---------------------+--------------+---------------------+---------------------+

如果 das_station = 11，则命名空间中应该显示“BPN1”，而不是 null。

Answer 1

通过在 WHEN 中包含 namespace_port 来解决，如下所示:

SELECT s.name AS 'ingester',
    d.dev_used AS 'size',
    Cast(CASE WHEN (d.namespace_port = 4015 AND d.das_station IN (11,22,23)) THEN 'BPN1' WHEN (d.namespace_port = 4015 AND d.das_station NOT IN (11,22,23)) THEN 'SJC12' WHEN d.namespace_port = 4016 THEN 'SJC17' WHEN d.namespace_port = 4017 THEN 'SJC18' WHEN d.namespace_port = 4018 THEN 'SJC19' WHEN d.namespace_port = 4019 THEN 'SJC5' WHEN d.namespace_port = 4020 THEN 'SJC6'  WHEN d.namespace_port = 4021 THEN 'SJC7' WHEN d.namespace_port = 4022 THEN 'SJC8'  WHEN d.namespace_port = 4023 THEN 'SJC9' WHEN d.namespace_port = 4024 THEN 'SJC10' WHEN d.namespace_port = 4025 THEN 'SJC11' WHEN d.namespace_port = 4026 THEN 'SJC20' WHEN d.namespace_port = 4027 THEN 'SJC13' WHEN d.namespace_port = 4028 THEN 'SJC14' WHEN d.namespace_port = 4029 THEN 'SJC15' WHEN d.namespace_port = 4030 THEN 'SJC16' END AS CHAR) AS namespace,
    Date_format(d.date_attached, '%b-%e-%Y %l:%i %p') AS 'date_attached',
    Date_format(d.replication_started, '%b-%e-%Y %l:%i %p') AS 'date_started',
    Date_format(d.replication_completed, '%b-%e-%Y %l:%i %p') AS 'date_finished',
    Date_format(d.date_detached, '%b-%e-%Y %l:%i %p') AS 'date_detached'
FROM   das.stations s,
       das.device d
WHERE  d.das_station = s.id
AND svcid = "96wa"
ORDER  BY d.date_attached DESC

结果如下:

+------------------+--------------+-----------+---------------------+---------------------+----------------------+---------------------+
| ingester         | size         | namespace | date_attached       | date_started        | date_finished        | date_detached       |
+------------------+--------------+-----------+---------------------+---------------------+----------------------+---------------------+
| bpn-dasingest-p2 |         NULL | NULL      | Mar-29-2017 7:41 AM | NULL                | NULL                 | Mar-30-2017 8:58 AM |
| bpn-dasingest-p2 | 247547875328 | BPN1      | Mar-27-2017 7:55 AM | Mar-27-2017 9:33 AM | Mar-27-2017 12:27 PM | Mar-29-2017 7:41 AM |
+------------------+--------------+-----------+---------------------+---------------------+----------------------+---------------------+