有以下情形,数据库保存在time age name hobby 等字段,插入有以下 123,22,li,篮球; 124,null,li,足球; 125,null,null,排球; 126,null,null,null; 写一个sql查询time<126时,各个字段最近不为空时数据,age不为空对应 123,22,li,篮球, name不为空对应124,null,li,足球; hobby不为空对应125,null,null,排球;分别满足条件然后汇总, 写一条sql满足要求,该如何写
时间: 2023-10-04 17:08:30 浏览: 25
可以使用以下 SQL 查询语句来查询符合要求的数据并进行汇总:
```
SELECT MAX(CASE WHEN age IS NOT NULL THEN time END) AS age_time,
MAX(CASE WHEN age IS NOT NULL THEN age END) AS age,
MAX(CASE WHEN name IS NOT NULL THEN time END) AS name_time,
MAX(CASE WHEN name IS NOT NULL THEN name END) AS name,
MAX(CASE WHEN hobby IS NOT NULL THEN time END) AS hobby_time,
MAX(CASE WHEN hobby IS NOT NULL THEN hobby END) AS hobby
FROM your_table
WHERE time < 126
AND (age IS NOT NULL OR name IS NOT NULL OR hobby IS NOT NULL)
AND (
(age IS NOT NULL AND name IS NOT NULL AND hobby IS NOT NULL)
OR (age IS NOT NULL AND name IS NOT NULL AND hobby IS NULL)
OR (age IS NOT NULL AND name IS NULL AND hobby IS NULL)
OR (age IS NULL AND name IS NOT NULL AND hobby IS NOT NULL)
OR (age IS NULL AND name IS NULL AND hobby IS NOT NULL)
)
```
这个查询语句会返回符合要求的数据,并按照每个字段的最近不为空的时间进行汇总。如果某个字段在某个时间段内都没有值,则对应的时间字段为 `NULL`。
在这个查询语句中,我们使用了多个 `MAX(CASE ...)` 子句来进行条件判断和字段选择,从而得到我们想要的结果。注意,这里的 `MAX` 函数用于选择时间最晚的记录。