mysql按列值分组成行,MYSQL Group按列分组，每行2行

I need 2 id for each group.

SELECT `id`, `category`.`cat_name`

FROM `info`

LEFT JOIN `category` ON `info`.`cat_id` = `category`.`cat_id`

WHERE `category`.`cat_name` IS NOT NULL

GROUP BY `category`.`cat_name`

ORDER BY `category`.`cat_name` ASC

How to do this?

Sample Data:

id cat_name

1 Cat-1

2 Cat-1

3 Cat-2

4 Cat-1

5 Cat-2

6 Cat-1

7 Cat-2

Output Will be:

id cat_name

6 Cat-1

4 Cat-1

7 Cat-2

5 Cat-2

解决方案

If you need two arbitrary ids, then use min() and max():

SELECT c.`cat_name` , min(id), max(id)

FROM `info` i INNER JOIN

`category` c

ON i.`cat_id` = c.`cat_id`

WHERE c.`cat_name` IS NOT NULL

GROUP BY c`.`cat_name`

ORDER BY c.`cat_name` ASC ;

Note: You are using a LEFT JOIN and then aggregating by a column in the second table. This is usually not a good idea, because non-matches are all placed in a NULL group. Furthermore, your WHERE clause turns the LEFT JOIN to an INNER JOIN anyway, so I've fixed that. The WHERE clause may or may not be necessary, depending on whether or not cat_name is ever NULL.

If you want the two biggest or smallest -- and can bear to have them in the same column:

SELECT c.`cat_name`,

substring_index(group_concat id order by id), ',', 2) as ids_2

FROM `info` i INNER JOIN

`category` c

ON i.`cat_id` = c.`cat_id`

WHERE c.`cat_name` IS NOT NULL

GROUP BY c`.`cat_name`

ORDER BY c.`cat_name` ASC ;