MYSQL按列分组，每组2行

Question

我需要每个组2个id。

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 

该怎么做呢？

示例数据：

id  cat_name
1   Cat-1
2   Cat-1
3   Cat-2
4   Cat-1
5   Cat-2
6   Cat-1
7   Cat-2

输出将为：

id  cat_name
6   Cat-1
4   Cat-1
7   Cat-2
5   Cat-2

Answer 1

如果需要两个任意的ids，则使用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` IS NOT NULL
GROUP BY c`.`cat_name`
ORDER BY c.`cat_name` ASC ;

注意:您正在使用LEFT JOIN，然后按第二个表中的列进行聚合。这通常不是一个好主意，因为不匹配的都放在一个NULL组中。此外，您的WHERE子句无论如何都会将LEFT JOIN转换为INNER JOIN，所以我已经解决了这个问题。根据cat_name是否为NULL，WHERE子句可能是必需的，也可能不是必需的。

如果您想要最大或最小的两个--并且可以忍受将它们放在同一列中：

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 ;

Answer 2

SELECT  id, cat_name
    FROM  
      ( SELECT  @prev := '', @n := 0 ) init
    JOIN  
      ( SELECT  @n := if(c.cat_name != @prev, 1, @n + 1) AS n,
                @prev := c.cat_name,
                c.cat_name,
                i.id
            FROM  `info`
            LEFT JOIN  `category` ON i.`cat_id` = c.`cat_id`
            ORDER BY  c.cat_name ASC, i.id DESC 
      ) x
    WHERE  n <= 2
    ORDER BY  cat_name ASC, id DESC; 

。

Answer 3

在支持窗口函数数据库中，您可以枚举每个组(ROW_NUMBER() OVER (PARTITION BY cat_name ORDER BY id DESC))中每条记录的位置，然后选择相对位置1或2中的记录。

在MySQL中，您可以通过自联接来模拟这一点，该自联接计算其id大于或等于同一cat_name (分区...ORDER BY id DESC)。cat_name组中的记录#1只有一条>=其id的记录，而记录#N有N条这样的记录。

此查询

 SELECT id, cat_name
   FROM (   SELECT c.id, c.cat_name, COUNT(1) AS relative_position_in_group
              FROM category c
         LEFT JOIN category others
                ON c.cat_name = others.cat_name
                   AND
                   c.id <= others.id
          GROUP BY 1, 2) d
   WHERE relative_position_in_group <= 2
ORDER BY cat_name, id DESC;

产生：

+----+----------+
| id | cat_name |
+----+----------+
|  6 | Cat-1    |
|  4 | Cat-1    |
|  7 | Cat-2    |
|  5 | Cat-2    |
+----+----------+