select * from ( select b.name "姓名",b.year "变更年度",b.sal "变更工资",a.year "原年度",a.sal "原工资" from test_whq a right join test_whq b on a.name=b.name and a.year=b.year-1 and a.sal<>b.sal ) where "原年度" is not null order by "变更年度" desc
select b.name "姓名", b.year "变更年度", b.sal "变更工资", a.year "原年度", a.sal "原工资" from mytable a, mytable b where a.name = b.name and a.year = b.year - 1 and a.sal <> b.sal order by a.year desc
终于明白楼主的意思了,下次加点描述吧select a.姓名, a.年度 变更年度, a.工资 变更工资, b.年度 原年度, b.工资 原工资 from t a, t b where a.姓名 = b.姓名 and a.年度 > b.年度 and a.工资 != b.工资 and not exists (select 1 from t where 姓名 = a.姓名 and 年度 < a.年度 and 年度 > b.年度) order by 1,2 desc 和3楼的意思差不多
select b.name "姓名",b.year "变更年度",b.sal "变更工资",a.year "原年度",a.sal "原工资" from test_whq a
right join test_whq b on a.name=b.name and a.year=b.year-1 and a.sal<>b.sal
) where "原年度" is not null order by "变更年度" desc
b.year "变更年度",
b.sal "变更工资",
a.year "原年度",
a.sal "原工资"
from mytable a, mytable b
where a.name = b.name
and a.year = b.year - 1
and a.sal <> b.sal
order by a.year desc
a.年度 变更年度,
a.工资 变更工资,
b.年度 原年度,
b.工资 原工资
from t a, t b
where a.姓名 = b.姓名
and a.年度 > b.年度
and a.工资 != b.工资
and not exists (select 1
from t
where 姓名 = a.姓名
and 年度 < a.年度
and 年度 > b.年度)
order by 1,2 desc
和3楼的意思差不多