在group_concat中包含空结果 [英] Include null results in group_concat

问题描述

我有两个这样的桌子

profile_answers

+---------+------------+
|      id | class_name |
+---------+------------+
|       1 | Class 1    |
|       2 | Class 2    |
|       3 | Class 1    |
+---------+------------+

教育

+---------+--------------------+------------+
|      id | profile_answers_id |  sample    |
+---------+--------------------+------------+
|       1 | 1                  |     1234   |
|       2 | 1                  |     2334   |
|       3 | 1                  |     3434   |
+---------+------------+--------------------+

我运行了查询，

select educations.profile_answer_id, GROUP_CONCAT(educations.sample) from educations
LEFT JOIN profile_answers ON educations.profile_answers_id = profile_answers.id

我知道了

+--------+--------------------+-------------+
|      id | sample                          | 
+---------+--------------------+------------+
|       1 | 1234,2334,3434                  |
+---------+------------+--------------------+

我实际上想要

+--------+--------------------+-------------+
|      id | sample                          | 
+---------+--------------------+------------+
|       1 | 1234,2334,3434                  |
|       2 | NULL                            |
|       3 | NULL                            |  
+---------+------------+--------------------+

推荐答案

SELECT id,IFNULL(samples,'NULL') sample FROM 
(
    SELECT
        AA.id,
        GROUP_CONCAT(DISTINCT BB.sample) samples
    FROM
        profile_answers AA LEFT JOIN educations BB
        ON AA.id = BB.profile_answers_id
    GROUP BY AA.id
) A;

这篇关于在group_concat中包含空结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！