检索MySQL中每个record_id的每一列的最后一个非空记录 [英] Retrieve last non-null record of every column for each record_id in MySQL

问题描述

我有一个名为records的MySQL表.以下是其内容.

I have this MySQL table named records. Below is its contents.

id  record_id   Data1   Data2   Time 
1   1           null    1       1/1/16
2   1           1       null    1/3/16
3   1           2       null    1/4/16
4   1           null    3       1/5/16
5   2           1       null    2/1/16
6   2           1       null    2/3/16
7   2           7       null    2/4/16
8   2           null    5       2/5/16

我想要一个MySQL查询来检索每个record_id每列的最后一个非空记录.结果看起来像；

I would like to have a MySQL query to retrieve the last non-null record of each column for each record_id. The result would look something like;

record_id   Data1   Data2   Time 
1           2       3       1/5/16
2           7       5       2/5/16

此问题的棘手部分是涉及多个列.

The tricky part to this problem is that multiple columns are involved.

推荐答案

这可能解决您的问题:

select 
  record_id,
  substring_index(group_concat(Data1 order by Time desc), ',', 1) Data1,
  substring_index(group_concat(Data2 order by Time desc), ',', 1) Data2,
  substring_index(group_concat(Time  order by Time desc), ',', 1) Time
from records
group by record_id
;

它可能没有其他答案快，但是是另一种版本...尝试一下.如果表中有Data3列，则可以复制/粘贴Data1列，只需将该列的所有引用更改为新的引用即可.

It may not be as fast as other answers, but is another version... give it a try. If you have a Data3 column in your table, you can copy/paste the Data1 column and just change all references of this column to the new one.

仅说明其工作原理:group_concat函数用分隔符(默认为,)连接列的所有非空值.您可以在串联之前对列进行排序.它的工作原理有点像Oracle，Postgre和其他工具中的窗口函数... substring_index只是获得第一个串联值，因为列表是按时间降序排列的.

Just to explain how this works: the group_concat function concatenates all non-null values of a column with a separator (, by default). You can order the column before the concatenation. It works a bit like a window function in Oracle, Postgre, and others... The substring_index is just getting the first concatenated value, as the list is in a descending order of time.

这篇关于检索MySQL中每个record_id的每一列的最后一个非空记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！