数据库查询,一个表如何加减乘除计算结果后挑选出条件相等行?例如我有如下一个表:id year number text1 2011 13 dfsd12 2011 16 dfsd23 2011 14 dfsd34 2011 11 dfsd45 2012 13 dfsd36 2012 15 dfsd47 2012 14 dfsd38 2012 16 dfsd4想取得结果如下：IDA IDB YEARA YEARB NumberA NumberB testA testB1 5 2011 2012 13 13 dfsd1 dfsd32 8 2011 2012 16 16 dfsd2 dfsd43 7 2011 2012 14 14 dfsd3 dfsd3查找条件：Year字段等于Year-1的行的Number与之相同的行合并成上面的结果.例如2011-1=2010,没有year字段等于2010,一直向下查询到2012-1=2011,查询2011所有行里是否有与Number相等的行. 数学
网友回答
【答案】 如果该表的year+number不唯一,则需要提供一个具有唯一值的字段或字段组.
现假设year,number唯一,sql语句写法如下：
select
a.[id] as IDA,b.[id] as IDB,a.[year] as YEARA,b.[year] as YEARB,
a.[number] as NumberA,b.[number] as NumberB,a.[text] as testA,b.[text] as testB
from [tab1] a ,[tab1] b
where a.[year]=(select top 1 x.[year] from [tab1] x where x.[number]=b.[number] and x.[year]>=b.[year]-1 order by x.[year]) and a.[number]=b.[number]
and a.[year]!=b.[year]
order by a.[id]