Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

mariadb - SQL - Return amalgamated row of most recent content from matching rows

I can't figure this out - I guess it's simple but I can't find a suitable method.

I believe it's called the 'Last non-null puzzle', but I can't follow the solutions online! At the moment I'm using PHP to parse through a returned array until variables are all full, but I'd prefer to do it in SQL. Using MariaDB 8 on Ubuntu.

ID increments as records are added. It could be a datetime.

If I have the following:

ID | Data1 | Data2 | Data3
1  | NULL  | Book  | NULL
2  | Pink  | NULL  | Cat
3  | NULL  | Book  | NULL
4  | Blue  | NULL  | NULL
5  | NULL  | Stool | Cat
6  | White | NULL  | NULL
7  | NULL  | NULL  | Bull

How would I return: White | Stool | Bull (the most 'recent' non-NULLs)?

Is there a way to select on id = '4' and return Blue | Book | Cat?

I've been trying for hours! I've seen some posts on amalgamation but those don't seem to apply. Sorry of this is trivial - I assume it is but I can't figure it out.

question from:https://stackoverflow.com/questions/65878382/sql-return-amalgamated-row-of-most-recent-content-from-matching-rows

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

please check the following code.

SELECT * FROM

(SELECT Data1 FROM test t2
 WHERE Data1 IS NOT NULL
 ORDER BY ID DESC LIMIT 1
) Data1,

(SELECT Data2 FROM test t2
 WHERE Data2 IS NOT NULL
 ORDER BY ID DESC LIMIT 1
) Data2,

(SELECT Data3 FROM test t3
 WHERE Data3 IS NOT NULL
 ORDER BY ID DESC LIMIT 1
) Data3

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...