mysql显示列特殊处理_比较特殊的mysql行转列问题

select

A.device_id,

(case

when exists (select date from A as B where B.device_id=A.device_id and B.date<=A.date and B.主键id<>A.主键id)

then (select date from A as B where B.device_id=A.device_id and B.date<=A.date and B.主键id<>A.主键id order by B.date desc limit 1,1)

else A.date

end ) as last_Date,

A.date as current_Date,

( case

when exists (select val from A as B where B.device_id=A.device_id and B.date<=A.date and B.主键id<>A.主键id)

then (select val from A as B where B.device_id=A.device_id and B.date<=A.date and B.主键id<>A.主键id order by B.date desc limit 1,1)

else A.val

end ) as last_Date_value,

A.val as current_Date_value,

from A

注：

(1)sql没有运行过，可能有语法错误，原理上应该是可以的，不知道是否满足你的需求；

(2)case when 主要是处理第一期的时候，上期和本期应该是同一条记录，此时去查询上期应该是查不到记录的;

(3)你的这个表不会没有主键id吧？