有这样一个表:
人名 年 工资
a 2001 45544
a 2002 45455
a 2003 54566
b 2001 25455
b 2002 15455
b 2003 54566
.
.
.
怎么把2002年与2001的工资差额大于1000的记录查出来??
人名 年 工资
a 2001 45544
a 2002 45455
a 2003 54566
b 2001 25455
b 2002 15455
b 2003 54566
.
.
.
怎么把2002年与2001的工资差额大于1000的记录查出来??
from yourtable a, yourtable b
where a.人名 = b.人名
and a.年 = :Year1
and b.年 = :Year2
and b.工资 - a.工资 > 1000
order by a.人名你试试先。
sqlserver:
select A.name
(select name,sum(gz) a from table1 where year(ny)=2002 group by name) A,
(select name,sum(gz) b from table1 where year(ny)=2001 group by name) B
where A.name=B.name and (A.a-B.b)>1000注意每个name的2002和2001都要有记录,否则出错
select A.name from
(select name,sum(gz) a from table1 where year(ny)=2002 group by name) A,
(select name,sum(gz) b from table1 where year(ny)=2001 group by name) B
where A.name=B.name and (A.a-B.b)>1000
from yourtable a
where a.year = 2001
and exists (select year from yourtable b
where b.year = 2002 and b.人名 = a.人名
and (b.工资-a.工资)>1000)
如果记录太多的话用exists比join速度可能会快些
select a.人名, a.年, a.工资,b.年, b.工资
from yourtable a, yourtable b
where a.人名 = b.人名
and a.年 = 2002
and b.年 = 2001
and abs(b.工资 - a.工资) > 1000
order by a.人名
select name from ta1,ta2 where (ta1.name=ta2.name) and ((ta2.gz-ta1.gz)>1000)
試試吧我還沒有調試過, 只是想象的不知道行不行