MYSQL Group按列分组，每行2行 [英] MYSQL Group by column with 2 rows for each group

问题描述

我需要2 id 给每个组。

  SELECT` id`，`category`.cat_name` 
从`info` 
 LEFT JOIN`category` ON`info`.`cat_id` =`category`.`cat_id` 
 WHERE`category `.cat_name`不为空
 GROUP BY`category`.`cat_name` 
 ORDER BY`category`.`cat_name` ASC 
   
 
如何做到这一点？
 
 
 
样本数据：
 
 
  id cat_name 
 1 Cat-1 
 2 Cat-1 
 3 Cat-2 
 4 Cat-1 
 5 Cat-2 
 6 Cat-1 
 7 Cat-2 
  
 be：
  id cat_name 
 6 Cat-1 
 4 Cat-1 
 7 Cat -2 
 5 Cat-2 
  
 
 
解决方案
 If你需要两个任意的ID，然后使用 min（）和 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`不是NULL 
 GROUP BY c`.cat_name` 
 ORDER BY c.`cat_name` ASC; 
  
注意：您正在使用 LEFT JOIN 然后通过第二个表中的列进行聚合。这通常不是一个好主意，因为非匹配都放在 NULL 组中。此外，你的 WHERE 子句将 LEFT JOIN 变成 INNER JOIN  cat_name 是否曾经存在 WHERE 子句可能或可能不需要， NULL 。
 
 
 
如果你想要两个最大或最小的 - 并且可以忍受它们在同一列中：
  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`不是NULL 
 GROUP BY c`.`cat_name` 
 ORDER BY c.`cat_name` ASC; 
  
 
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 ;


                        
这篇关于MYSQL Group按列分组，每行2行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！