MYSQL Group按列分组，每行2行

2018-06-09 11:20:30

我需要每个组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

如果你需要两个任意的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` 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 ;

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;

在Group-wise-max博客中进行更多讨论。

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

在MySQL中，你可以通过一个自我连接来模拟这种情况，这个自我连接记录的id大于或等于同一个cat_name （PARTITION ... 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    |
+----+----------+

链接地址: http://www.djcxy.com/p/28397.html

上一篇: MYSQL Group by column with 2 rows for each group

下一篇: Not able to access local server running after VPN connection