mysql - 如何在不使用聚合函数的情况下按列 id 和 manager_id 分组

Question

如何在不使用聚合函数的情况下按列 id 和 manager_id 进行分组。以下是示例数据。我想按 id 和 manager_id 分组并按 created_date desc 排序。

   id       manager_id    manager_mail                                     created_date  
------        ----------  --------------------------------------    ---------------------
  1765        2182  Sumana.n@test.com         2016-08-16 11:35:31
  1765        2182  Sumana.n@test.com         2016-08-16 11:35:32
  1765        2182  Sumana.n@test.com         2016-07-05 14:01:15
  1765        2182  Sumana.n@test.com         2016-07-05 14:00:22
  1765        2182  Sumana.n@test.com         2016-07-05 13:59:12
  1765        2182  Sumana.n@test.com         2016-07-05 13:58:13
  1765        2182  Sumana.n@test.com         2016-06-09 08:34:55
  1765        2652  Abhijit.s@test.com        2016-04-22 01:37:39
  1765        2652  Abhijit.s@test.com        2016-02-02 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-31 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-30 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-29 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-28 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-27 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-26 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-25 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-24 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-23 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-22 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-21 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-20 23:00:00
  1765        2652  Abhijit.s@test.com        2016-01-08 23:00:00
  1765        2652  Abhijit.s@test.com        2015-12-15 11:51:57
  1765        1702  KARTHIK.r@test.COM        2015-12-15 11:51:57  

Answer 1

此处使用聚合的替代方法是使用子查询，该子查询标识每个 id/manager_id 对的最新记录:

SELECT *
FROM yourTable t1
WHERE created_date = (SELECT MAX(created_date) FROM yourTable t2
                      WHERE t1.id = t2.id AND t1.manager_id = t2.manager_id);

但实际上我会使用GROUP BY:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT id, manager_id, MAX(created_date) AS max_created_date
    FROM yourTable
    GROUP BY id, manager_id
) t2
    ON t1.id = t2.id AND
       t1.manager_id = t2.manager_id AND
       t1.created_date = t2.max_created_date;