如何组合两个单元格以查找重复项，然后仅列出最新的单元格? [英] How to combine two cells to find find duplicates and then list only the latest one?

问题描述

我有一个名为 element_location 的表.
该表有 4 列:obj_nr、el_nr、location &date_scanned

I have a table named element_location.
The table have 4 columns: obj_nr, el_nr, location & date_scanned

表格可能如下所示:

id    obj_nr    el_nr    location    date_scanned
1      1234      1        A           2019-01-01
2      1234      2        A           2019-01-02
3      1234      1        A           2019-01-03

现在我想做一个 SELECT ，我将 obj_nr &el_nr 查找我的重复项.(第 1 行和第 3 行)在 SELECT 中，我只想打印最新的行.我通过 date_scanned 列获得最新信息.

Now I'd like to make a SELECT where I combine obj_nr & el_nr to find my duplicates. (Row 1 & 3) In the SELECT I only want to print the latest row. I get the latest by the column date_scanned.

如何进行选择以提供第 2 行的所有数据 &3.(离开第 1 行是因为第 3 行具有相同的 obj_nr & el_nr 和更新的日期?

How can I make a select that gives me all data from row 2 & 3. (Leaving row 1 because row 3 has same obj_nr & el_nr and a newer date?

我试过了:

$query = "SELECT DISTINCT CONCAT(obj_nr, el_nr), obj_nr, el_nr, location, date_scanned 
    FROM element_location 
    WHERE obj_nr = :obj_nr 
    ORDER BY date_scanned DESC";

推荐答案

查看您的示例似乎您需要

looking to your sample seems you need

select max(id) id , obj_nr, el_nr, location, max(date_scanned)
from  my_table 
group by  obj_nr,  el_nr, location 

或者如果您不介意位置，而只介意最大 date_scanned

or if you dont't mind for location but only for max date_scanned

select  m2.id, m2.obj_nr, m2.el_nr ,  m2.location,   m2.date_scanned
from  my_table m2
from  (
  select  obj_nr,  el_nr, max(date_scanned) max_date 
  from my_table  
  group by  obj_nr,    el_nr
) t ON  t.obj_nr = m2.obj_nr and t.el_nr = m2.el_nr and t. = m2.date_scanned

这篇关于如何组合两个单元格以查找重复项，然后仅列出最新的单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！