你知道用非空值对行进行分组的方法吗?
例如:
ID title description address
------------------------------------
1 | john | row1 | EMPTY
1 | john | EMPTY | 2
1 | john | EMPTY | EMPTY
结果
ID title description address
------------------------------------
1 | john | row1 | 2
选择是一个联合:
SELECT
id,
title,
description,
address
FROM
(
SELECT a.id, a.title, b.value as description, "" as address
FROM #__content as a
LEFT JOIN #__fieldsattach_values as b ON a.id = b.articleid
WHERE a.catid IN (15, 16)
AND b.fieldsid = 4 AND a.language = "ca-ES" AND a.state = "1"
GROUP BY a.id UNION ALL SELECT a.id, a.title, "" as description , b.value as address
FROM #__content as a LEFT JOIN #__fieldsattach_values as b ON a.id = b.articleid
WHERE a.catid IN (15, 16) AND b.fieldsid = 5 AND a.language = "ca-ES"
AND a.state = "1" GROUP BY a.id
) AS z
在MySQL上,你可以像这样使用GROUP_CONCAT函数:
SELECT ID, Name,
GROUP_CONCAT(Direction) AS Direction,
GROUP_CONCAT(Group) AS Group,
GROUP_CONCAT(Profile) AS Profile
FROM MyTable
GROUP BY ID, Name
如果一列中有多个非空值,则对于每对 ID 和 Name,这些值将默认用逗号连接。
你想做的事情似乎有点肮脏。顺便说一句,我假设EMPTY
意味着NULL
.
您可以按分组方式获取组的最大值。请记住,与示例中所示的方式相同,如果组中有多个非空值,则只会保留一个(最大值):
select id, name, max(direction), max(`group`), max(profile) from name_table
group by id
请注意,group
是一个保留字,您还可以按id
和name
进行分组,以使查询更加标准。
编辑:
根据您的激进问题编辑,您可以执行以下操作以保持相同的逻辑:
select id, name, max(direction), max(`group`), max(profile) from (
Select ID, Name, Direction, "" as Group, "" Profile FROM name_table
UNION ALL
Select ID, Name, "" a Direction, group Group, "" Profile FROM name_table
UNION ALL
Select ID, Name, "" a Direction, "" Group, profile Profile FROM name_table
) s
group by id
请记住group
是一个保留词。