代码之家 › 专栏 › 技术社区 › Kevin Howard Goldberg

如何在分组之前进行排序(mysql、mariadb)

groupwise-maximum mariadb sql-order-by group-by mysql

Kevin Howard Goldberg · 技术社区 · 7 年前

设置:

每个联系人可以有一个或多个销售。每笔销售都有创建日期,可以打开或关闭。

我想生成一个查询来返回一个连接的结果集,该结果集对于每个联系人只有一条记录,如下所示:

如果联系人有任何未结销售,请返回最近创建的销售。

我试着按is\u open进行排序,创建日期时间,然后按contact\u id进行分组,但Group-by表示它在进行任何排序之前选择了自己的记录,所以它不起作用。

CREATE TABLE `my_contacts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `my_sales` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `contact_id` INT(11) NOT NULL,
  `description` VARCHAR(45) NOT NULL,
  `is_open` INT(1) NOT NULL,
  `create_date_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`));

INSERT INTO `my_contacts` (`name`) VALUES ('Jim');
INSERT INTO `my_contacts` (`name`) VALUES ('Jane');
INSERT INTO `my_contacts` (`name`) VALUES ('Roger');
INSERT INTO `my_contacts` (`name`) VALUES ('Alice');

INSERT INTO `my_sales` VALUES (NULL, '2', 'Books', '0', '2017-09-06');
INSERT INTO `my_sales` VALUES (NULL, '3', 'Toys', '0', '2017-06-21');
INSERT INTO `my_sales` VALUES (NULL, '2', 'Groceries', '1', '2017-05-06');
INSERT INTO `my_sales` VALUES (NULL, '1', 'Water', '0', '2016-09-21');
INSERT INTO `my_sales` VALUES (NULL, '4', 'Toys', '1', '2017-04-04');
INSERT INTO `my_sales` VALUES (NULL, '3', 'Food', '1', '2017-05-06');
INSERT INTO `my_sales` VALUES (NULL, '2', 'Water', '1', '2017-04-07');
INSERT INTO `my_sales` VALUES (NULL, '4', 'Food', '1', '2017-01-02');
INSERT INTO `my_sales` VALUES (NULL, '1', 'Food', '0', '2017-07-09');

结果:

id  contact_id  description is_open create_date_time    id  name
9   1           Food        0       7/9/2017 0:00       1   Jim
3   2           Groceries   1       5/6/2017 0:00       2   Jane
6   3           Food        1       5/6/2017 0:00       3   Roger
5   4           Toys        1       4/4/2017 0:00       4   Alice

1 回复 | 直到 7 年前

Adrian Maxwell 7 年前

在支持“窗口功能”的数据库版本中,可以通过使用 ROW_NUMBER() OVER()和所需的排序在OVER子句中应用,如下所示(使用Mariadb 10.2):

select
*
from (
     select
      s.*, c.name
     , row_number() over(partition by c.id order by s.is_open DESC, create_date_time DESC) as rn
     from my_contacts c
     left join my_sales s on c.id = s.contact_id
     ) d
where rn = 1
order by d.contact_id

请注意,您必须通过row_number()的别名进行过滤,因此您需要一个子查询构造,如上文所示。

id | contact_id | description | is_open | create_date_time    | name  | rn
-: | ---------: | :---------- | ------: | :------------------ | :---- | -:
 9 |          1 | Food        |       0 | 2017-07-09 00:00:00 | Jim   |  1
 3 |          2 | Groceries   |       1 | 2017-05-06 00:00:00 | Jane  |  1
 6 |          3 | Food        |       1 | 2017-05-06 00:00:00 | Roger |  1
 5 |          4 | Toys        |       1 | 2017-04-04 00:00:00 | Alice |  1

D小提琴 here

对于不支持ROW_NUMBER()的MySQL或Mariadb版本,请尝试以下方法使用变量模拟row_number()的效果(并获得相同的结果):

select
        d.contact_id
      , d.description
      , d.is_open
      , d.create_date_time
      , c.name
from (
        SELECT
                @row_num :=IF(@prev_value=s.contact_id,@row_num+1,1) AS RN
              , s.contact_id
              , s.description
              , s.is_open
              , s.create_date_time
              , @prev_value := s.contact_id
        FROM my_sales s
        CROSS JOIN (
                    SELECT @row_num :=1 x,  @prev_value :=0 y
                   ) vars
        ORDER BY
                s.contact_id
              , s.is_open DESC
              , s.create_date_time DESC
    ) d
inner join my_contacts c on c.id = d.contact_id
where d.rn = 1
order by d.contact_id

;

有关此方法,请参阅: http://sqlfiddle.com/#!9/bfa9809/1