mysql - 按行查询分组但保留最小日期

Question

我有下一个包含数据的表格:

user     group     dt_action
-----    -----     ---------
John     salvage   2016-3-2
Dennis   fire      2016-1-1
Martha   fire      2016-12-23
John     rescue    2016-1-20
John     salvage   2017-1-26
Developer NULL     2016-5-6
Dennis   several   2016-4-29
Martha   fire      2003-1-1

我的想法是按“组”类别进行分组，计算每个用户的组数量，但在日期时间字段上，我想保留每个组的最小日期。结果应该是这样的:

user      group   count_group     dt_action_min
----      -----   -----------    -------------
John      salvage     2           2016-3-2
John      rescue      1           2016-1-20
Dennis    fire        1           2016-1-1
Dennis    several     1           2016-4-29
Martha    fire        2           2003-1-1
Developer NULL        1           2016-5-6

这是一个很久以前就偷走了我的梦想的查询，但我可以得到它!非常感谢，

丹妮

Answer 1

创建表/插入数据

CREATE TABLE DATA
    (`user` VARCHAR(9), `group` VARCHAR(7), `dt_action` VARCHAR(10))
;

INSERT INTO DATA
    (`user`, `group`, `dt_action`)
VALUES
    ('John', 'salvage', '2016-3-2'),
    ('Dennis', 'fire', '2016-1-1'),
    ('Martha', 'fire', '2016-12-23'),
    ('John', 'rescue', '2016-1-20'),
    ('John', 'salvage', '2017-1-26'),
    ('Developer', NULL, '2016-5-6'),
    ('Dennis', 'several', '2016-4-29'),
    ('Martha', 'fire', '2003-1-1')
;

查询

用你自己的表名替换数据

SELECT
    `data`.`user`
  , `data`.`group`
  , `data_group`.`count_group`
  , `data_group`.`min_dt_action`
FROM
 `data` 
INNER JOIN (
  SELECT
     `data`.user
   , `data`.`group` 
   , COUNT(*) count_group
   , MIN(`data`.`dt_action`) min_dt_action
  FROM 
   `data`
  GROUP BY
     `data`.user
   , `data`.`group` 
) data_group
ON
 `data`.`user` = data_group.`user`
AND
  `data`.dt_action = data_group.min_dt_action
ORDER BY 
 `data_group`.`count_group` DESC

结果

user       group    count_group  min_dt_action  
---------  -------  -----------  ---------------
Martha     fire               2  2003-1-1       
John       salvage            2  2016-3-2       
John       rescue             1  2016-1-20      
Developer  (NULL)             1  2016-5-6       
Dennis     fire               1  2016-1-1       
Dennis     several            1  2016-4-29