时间戳

首页 > 解决方案 > MySQL - 如何在嵌套的 IFNULL...GROUP_CONCAT() 条件下选择多个列？

mysql - MySQL - 如何在嵌套的 IFNULL...GROUP_CONCAT() 条件下选择多个列？

问题描述

我有一个连接到 MySQL (5.5.64-MariaDB) 数据库的 Web 应用程序。

查询之一如下：

SELECT 
        d.id, 
        d.label AS display_label, 
        d.anchor, 
        r.id AS regulation_id, 
        IFNULL(
            (SELECT GROUP_CONCAT(value) FROM display_substances `ds` 
              WHERE `ds`.`display_id` = `d`.`id`
              AND ds.substance_id = 1 -- For example, substance ID = 1 
              GROUP BY `ds`.`display_id`
            ), "Not Listed"
        ) `display_value` FROM displays `d`
        JOIN groups g ON d.group_id = g.id
        JOIN regulations r ON g.regulation_id = r.id

输出示例如下：

+-----+------------------------------------+------------------------------------------------------------------------------------------+
| id  | name                               | display_value                                                                            |
+-----+------------------------------------+------------------------------------------------------------------------------------------+
|   4 | techfunction                       | Intermediate / monomer;  Corrosion inhibitor / anodiser / galvaniser; Catalyst; Additive |
| 323 | russia_chemsafety_register_display | Not Listed                                                                               |
| 733 | peru_pcb_display                   | Not Listed                                                                               |
+-----+------------------------------------+------------------------------------------------------------------------------------------+

这个查询可以满足我们的需要。出于解释目的：

  1. 有2张桌子，displays和display_substances
  2. 查询正在display_substances.value为每个displays.id
  3. 如果没有对应display_substances.value的，则返回字符串“未列出”（请参阅​​上面的查询）。如果有对应的值则display_substances.value返回。所以在上面的示例数据中，ID323和733指的是没有对应条目的场景，因此我们想要“未列出”。相反，ID4确实有一个值（“中间体/单体；缓蚀剂/阳极氧化剂/镀锌剂；催化剂；添加剂”）所以我们得到了。

表结构如下：

DESCRIBE displays;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(127)         | NO   |     | NULL    |                |
| label    | varchar(255)         | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+


DESCRIBE display_substances;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| display_id   | smallint(5) unsigned  | NO   | MUL | NULL    |                |
| substance_id | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| value        | text                  | NO   |     | NULL    |                |
| automated    | tinyint(4)            | YES  |     | NULL    |                | 
+--------------+-----------------------+------+-----+---------+----------------+

我希望能够display_substances.automated从我的查询中返回（参考上面的表结构）作为列。但我看不出如何做到这一点。

display_substances对表的引用是ds，所以我不能在初始SELECT语句中使用它，因为那时没有别名。同样，没有任何JOIN条件可以使它成为可能，因为并非返回的每一行都从中获取数据display_substances（即那些“未列出”的行没有从该表中获取任何内容）。

如果我想display_value在上面的示例输出旁边显示一个附加列display_substances.automated，或者NULL如果它不存在，我该如何实现？

作为参考，该automated字段包含 1（表示我们的应用程序通过自动化流程获得的数据），或者NULL如果它不是自动化的。

标签： mysql

解决方案

没有 JOIN 条件可以使其成为可能，因为并非返回的每一行都从 display_substances 获取数据

对于这种情况，您可以使用LEFT JOIN：

SELECT d.id, d.label display_label, d.anchor, r.id regulation_id, 
  COALESCE(ds.value, 'Not Listed') display_value,
  ds.automated     
FROM displays d
INNER JOIN groups g ON d.group_id = g.id
INNER JOIN regulations r ON g.regulation_id = r.id
LEFT JOIN (
  SELECT display_id, GROUP_CONCAT(value) value, MAX(automated) automated     
  FROM display_substances  
  WHERE substance_id = 1
  GROUP BY display_id
) ds ON ds.display_id = d.id

我用作MAX(automated)返回的列，但您可以GROUP_CONCAT(automated)像使用 for valueand一样使用COALESCE()：

COALESCE(ds.automated, 'Not Listed')

推荐阅读