CONCAT仅返回非空行

时间：2023-02-13 11:51:11

I have searched in a few topics for how to resolve this issue I am having with a query that uses CONCAT but yet must return the groups even if the concat is null and I have found 2 solutions that doesnt work.

我已经在一些主题中搜索了如何解决这个问题,我正在使用一个使用CONCAT但仍然必须返回组的查询,即使concat为null并且我找到了2个不起作用的解决方案。

First solution was to use ISNULL:

第一个解决方案是使用ISNULL:

#1582 - Incorrect parameter count in the call to native function 'ISNULL'

Second was to use IFNULL which works partially, it does find which are not null but yet doesnt print the null ones.

第二个是使用部分起作用的IFNULL,它确实发现哪些不是空但是不打印空的。

Here is the query:

这是查询:

  SELECT g.name, IFNULL(GROUP_CONCAT(c.name),'') AS commands
    FROM site_access b
    JOIN groups g ON b.group_id = g.id
    JOIN group_commands gc ON g.id = gc.group_id
    JOIN commands c ON gc.command_id = c.id
   WHERE b.site_id = 1
GROUP BY g.name
ORDER BY g.status ASC

I have 8 site_access, 8 groups and currently registered and only 2 commands that are assigned to group 1 and group 2, what currently happens is that it does print these 2 groups but ignore all the rest because they dont have commands.

我有8个site_access,8个组并且当前已注册,只有2个命令分配给组1和组2,目前发生的是它确实打印了这两个组但忽略了所有其余组,因为它们没有命令。

Here is an example of the output:

以下是输出示例:

Admin - add, del, announce
Member - find

Desired output:

Admin - add, del, announce
Member - find
Banned
OhterGroup1
OhterGroup2
OhterGroup3
OhterGroup4
OhterGroup5

if you need more information about the tables there is a sample of it in here: MySQL query for multiple tables being secondary tables multiple items?

如果你需要关于这些表的更多信息,可以在这里找到它的一个示例:MySQL查询多个表是次表多项​​?

1 个解决方案

#1

2  

Try using a LEFT JOIN to the groups_commands instead. Note how the JOIN to commands has also been moved in this query.

尝试使用LEFT JOIN来代替groups_commands。请注意在此查询中如何移动JOIN命令。

SELECT g.name, IFNULL(GROUP_CONCAT(c.name),'') AS commands
FROM site_access b
    INNER JOIN groups g 
        ON b.group_id = g.id
    LEFT JOIN group_commands gc
        INNER JOIN commands c 
            ON gc.command_id = c.id
        ON g.id = gc.group_id
WHERE b.site_id = 1
GROUP BY g.name
ORDER BY g.status ASC

#1

2  

Try using a LEFT JOIN to the groups_commands instead. Note how the JOIN to commands has also been moved in this query.

尝试使用LEFT JOIN来代替groups_commands。请注意在此查询中如何移动JOIN命令。

SELECT g.name, IFNULL(GROUP_CONCAT(c.name),'') AS commands
FROM site_access b
    INNER JOIN groups g 
        ON b.group_id = g.id
    LEFT JOIN group_commands gc
        INNER JOIN commands c 
            ON gc.command_id = c.id
        ON g.id = gc.group_id
WHERE b.site_id = 1
GROUP BY g.name
ORDER BY g.status ASC
标签：

相关文章