mysql组由min [英] mysql group by having min

问题描述

下面是表数据(一小段)，基本上，我想查询的是按accoutn编号分组时具有original_date_ctr最小的行.

Below is the table data (a small piece) basically I'm looking to query just the rows with the minimum original_date_ctr when grouped by the accoutn number.

我尝试使用HAVING(MIN())和where = Min()等运气不好的方法.

I've tried using HAVING(MIN()), and where = Min() and other ways with no luck.

这里的正确结果将给我id_ctr 688、1204和1209

The correct result here would give me id_ctr 688, 1204 and 1209

id_ctr  account_number_cus  original_date_ctr   mrc_ctr  
------  ------------------  -----------------  ----------
   688               20062  2008-05-17             138.97
  1204              151604  2006-08-10           42000.00
  1209              151609  2006-06-29             968.68
  1367               20062  2011-10-27             207.88
  1434              151609  2009-09-10            1469.62
  1524              151604  2009-09-01           36999.99
  1585              151609  2012-05-31            1683.88

推荐答案

您可以通过以下方式进行操作:

You can do this the following way:

select t1.id_ctr,
    t1.account_number_cus,
    t1.original_date_ctr,
    t1.mrc_ctr  
from yourtable t1
inner join
(
    select min(original_date_ctr) as mindate, account_number_cus
    from yourtable
    group by account_number_cus
) t2
    on t1.account_number_cus = t2.account_number_cus
    and t1.original_date_ctr = t2.mindate

请参见带演示的SQL提琴

这篇关于mysql组由min的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！