我正在尝试从不同的表中选择字段,并使用GROUP_CONNCT((对行进行分组。但是,如果GROUP_CONNCT((中的某个字段返回NULL,则选定的整行将为NULL。
"成就"one_answers"社会"中的字段可以为空。每个档案可以有多行成就和社交信息。
我曾尝试使用COALESCE更改查询,但没有成功:
SELECT p.UserId, p.Username, p.TextInfo, GROUP_CONCAT(DISTINCT CONCAT(COALESCE(a.Rank,''), '#', COALESCE(a.Tourney,''))) AS achievements, GROUP_CONCAT(DISTINCT CONCAT(COALESCE(s.platform,''),'#', COALESCE(s.URL,''))) AS socials from profile p INNER JOIN achievement a ON p.UserId = a.UserId INNER JOIN social s ON p.UserId = s.UserId WHERE p.Username LIKE '%uagib%'
预期结果:
<UserId> | <Username> | <TextInfo> | (multiple) <Rank>#<Tourney> | (multiple) <Platform>#<URL> |
Group by子句缺少
SELECT p.UserId, p.Username, p.TextInfo,
GROUP_CONCAT(DISTINCT CONCAT(COALESCE(a.Rank,''), '#', COALESCE(a.Tourney,''))) AS achievements,
GROUP_CONCAT(DISTINCT CONCAT(COALESCE(s.platform,''),'#', COALESCE(s.URL,''))) AS socials
from profile p INNER JOIN achievement a ON p.UserId = a.UserId
INNER JOIN social s ON p.UserId = s.UserId
WHERE p.Username LIKE '%uagib%'
group by p.UserId, p.Username, p.TextInfo
我建议对此进行子查询:
SELECT p.UserId, p.Username, p.TextInfo,
(SELECT GROUP_CONCAT(COALESCE(a.Rank, ''), '#', COALESCE(a.Tourney, ''))
FROM achievement a
WHERE p.UserId = a.UserId
) as achievements,
(SELECT GROUP_CONCAT(COALESCE(s.platform, ''), '#', COALESCE(s.URL, ''))
FROM social s
WHERE p.UserId = s.UserId
) as socials
FROM profile p
WHERE p.Username LIKE '%uagib%';
注:
- 这将检索用户,即使他们缺少属性和/或社交
DISTINCT
可能不是必需的,因为沿着两个独立的维度连接时没有偏笛卡尔乘积- CCD_ 2是不必要的。
GROUP_CONCAT()
接受多个参数