如何根据日期在MySQL中过滤重复的行? [英] how to filter repeated rows based on date in Mysql?

问题描述

我有两个桌子

表1名称: depot_tracking

     -------------------------------------------------
    depot_tracking_id | cont_details_id |    date    |
    -------------------------------------------------
           1          |       4         | 05-02-2015 |
           2          |       1         | 25-03-2015 |
           3          |       3         | 05-04-2015 |
           4          |       2         | 15-02-2015 |
           5          |       3         | 05-05-2015 |
           6          |       2         | 23-03-2015 |
           7          |       1         | 15-05-2015 |
    --------------------------------------------------

第二个表名称: cont_details

      -----------------------------------
          cont_no      | cont_details_id | 
       -----------------------------------
           aa          |       1         |
           bb          |       2         |
           cc          |       3         |
           dd          |       4         | 
           ee          |       5         |
           ff          |       6         |
           gg          |       7         |
    --------------------------------------

现在我要合并这两个表并过滤重复的行，而最重要的一件事是我必须过滤具有较旧日期的行.这正是我所期望的输出

now i want to merge this two tables and filter repeated rows and one more most important thing is i have to filter the rows which has OLDER dates. This is what exactly i am expecting output

     ---------------------------------------------------------
    depot_tracking_id | cont_details_id |    date    | cont_no
    ----------------------------------------------------------
           1          |       4         | 05-02-2015 |  dd
           5          |       3         | 05-05-2015 |  cc
           6          |       2         | 23-03-2015 |  bb
           7          |       1         | 15-05-2015 |  aa
    ---------------------------------------------------------

请有人帮助我过滤重复的行(过滤具有旧日期行的行)谢谢

please some one help me to filter repeated rows ( filter the rows which has old date rows) thanks

ORDER BY (str_to_date(date,'%d-%m-%Y')) DESC LIMIT 1 

通过使用此代码，我可以将日期列(DD-MM-YYYY)更改为mysql格式的日期

by using this code i can change date column (DD-MM-YYYY) to mysql format date

但是不知道如何根据日期过滤重复的行

but dont dont know how to filter repeated rows based on dates

推荐答案

由于可以使用聚合函数可靠地确定所有字段值，因此只需要一个简单的聚合查询即可.

Because all of your field values can be reliably determined by using aggregate functions, you only need a simple aggregation query.

select max(d.depot_tracking_id), c.cont_details_id, max(d.date), c.cont_no
  from depot_tracking d
    inner join cont_details c
      on c.cont_details_id = d.cont_details_id
  group by c.cont_details_id

要使其正常工作，您的日期字段必须是实际的日期类型，而不是字符串类型.或者，如果它们是字符串类型，则必须为"yyyy-mm-dd"格式，而不是"dd-mm-yyyy".

In order for this to work, your date fields have to be an actual date type, not a string type. or if they ARE a string type, they need to be 'yyyy-mm-dd' format, not 'dd-mm-yyyy'.

在此处演示

更新

由于您似乎无法使用日期格式，因此此查询将起作用:

Since it seems you are stuck using the broken date format - this query will work:

select max(d.depot_tracking_id), c.cont_details_id, max(str_to_date(d.date,'%d-%m-%Y')), c.cont_no
  from depot_tracking d
    inner join cont_details c
      on c.cont_details_id = d.cont_details_id
  group by c.cont_details_id

演示

这篇关于如何根据日期在MySQL中过滤重复的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！