MySql - 从每列中的最新非空值获取一行

发布于 2024-11-02 16:06:26 字数 346 浏览 0 评论 0原文

我有一个表，我想获取一行，其中包含每列的所有最新非空属性（无需组合每列的单独查询，这对我来说似乎并不优雅）。

示例：

A     B     C     Time
1     a     7     0
NULL  NULL  3     1
3     NULL  4     2
NULL  NULL  6     3

我寻求的结果：

A     B     C
3     a     6

正如我所说，我知道如何分别为每列选择我想要的内容，但我想知道是否有更好的方法来做到这一点。如果不需要的话，无需对糟糕的数据库征税。

原文

I have a table and I would like to get a row containing all the latest non-null attributes for each column (without combining separate queries for each column, which doesn't seem elegant to me).

Example:

A     B     C     Time
1     a     7     0
NULL  NULL  3     1
3     NULL  4     2
NULL  NULL  6     3

Result I seek:

A     B     C
3     a     6

As I said, I know how to select what I want for each column separately, but I was wondering if there's a better way to do it. No need to tax the poor database if it isn't needed.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

把梦留给海 2024-11-09 16:06:26

可能比这更好的方法，但现在是星期一，我还不太清楚：

select @a:=null, @b:=null, @c:=null;
select A,B,C from (
    select @a:=coalesce(A,@a) as A, @b:=coalesce(B,@b) as B, @c:=coalesce(C,@) as C time
    from yourtable
    order by time asc
) as y order by time desc limit 1;

基本上，迭代数据库中的每一行并建立“最新”值，然后反转结果集并仅选择一个具有最高时间价值

Probably a better way than this, but it's Monday and I'm not quite conscious yet:

select @a:=null, @b:=null, @c:=null;
select A,B,C from (
    select @a:=coalesce(A,@a) as A, @b:=coalesce(B,@b) as B, @c:=coalesce(C,@) as C time
    from yourtable
    order by time asc
) as y order by time desc limit 1;

Basically, iterate over each row in the database and build up the "latest" value as you go, then reverse the result set and select only the one with the highest time value

回复收藏 0 原文

~没有更多了~