sql查询,结合group by和case when ,子查询查询按照时间轴排序,某字段的标签新旧变化对比情况
遇到这样一个业务,需要按照时间轴的顺序展示,标签的新旧变化情况,
表结构如下:
sql语句如下:
SELECT
tagName,
CASE
WHEN b.tagTime > (SELECT min(tagTime) FROM IP_gene_info c WHERE c.ip = b.ip AND c.tagName = b.tagName)
THEN
(
SELECT
SUBSTRING_INDEX(GROUP_CONCAT(a.tagValue ORDER BY tagTime DESC),',',2)
as tagValue
FROM IP_gene_info a
WHERE a.ip = b.ip
AND a.tagName = b.tagName
AND a.tagTime <=b.tagTime
GROUP BY b.tagName
)
ELSE
max(tagValue)
END AS tagValue
FROM
IP_gene_info b
WHERE
b.ip = '117.18.237.29'
GROUP BY
b.tagName,
b.tagTime
ORDER BY
tagTime