MySQL:可以对多行进行group_concat吗? [英] MySQL: is it possible to group_concat multiple rows?
本文介绍了MySQL:可以对多行进行group_concat吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这就是我想要的:
attribute_name attribute_id attribute_value
--------------------------------------------------------------------
Appliances 16, 17, 18, 19 Washer, Dryer, Dishwasher, Microwave
Consoles 7, 3 PS3, XBox
这里很接近我所拥有的:
Here's close to what I've got:
attribute_name attribute_id attribute_value
-------------------------------------------------
Appliances 16 Washer
Appliances 17 Dryer
Appliances 18 Dishwasher
Appliances 19 Microwave
Consoles 7 PS3
Consoles 3 XBox
...来自此查询:
SELECT a.name AS attribute_name,
av.attribute_value_id, av.value AS attribute_value
FROM attribute_value av
INNER JOIN attribute a
ON av.attribute_id = a.attribute_id
WHERE av.attribute_value_id IN
(SELECT attribute_value_id
FROM property_attribute
WHERE property_id = 1)
ORDER BY a.name;
我在GROUP_CONCAT上没有成功.我什至不知道我想要什么.
I've had no success with GROUP_CONCAT. I don't even know what I want is possible.
推荐答案
您现有的查询将返回生成连接列所需的所有内容.如果将现有查询包装在子查询中,则可以同时GROUP_CONCAT()
列和GROUP BY attribute_name
:
Your existing query is returning everything you need to produce the concatenated columns. If you wrap your existing query in a subquery, you can GROUP_CONCAT()
both columns and GROUP BY attribute_name
:
SELECT
attribute_name,
GROUP_CONCAT(attribute_value_id) AS attribute_value_ids,
GROUP_CONCAT(attribute_value) AS attribute_values
FROM (
/* Wrap the body of your existing query in a subselect */
SELECT
a.name AS attribute_name,
av.attribute_value_id,
av.value AS attribute_value
FROM
attribute_value av
INNER JOIN attribute a
ON av.attribute_id = a.attribute_id
WHERE
av.attribute_value_id IN
(SELECT attribute_value_id
FROM property_attribute
WHERE property_id = 1)
) attr_groups
GROUP BY attribute_name
ORDER BY attribute_name;
这篇关于MySQL:可以对多行进行group_concat吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文