数据库查询,一个表如何加减乘除计算结果后挑选出条件相等行?例如我有如下一个表:idyearnumbertext1201113dfsd12201116dfsd23201114dfsd34201111dfsd45201213dfsd36201215dfsd47201214dfsd38201216dfsd4

Question

数据库查询,一个表如何加减乘除计算结果后挑选出条件相等行?
例如我有如下一个表:
id year number text
1 2011 13 dfsd1
2 2011 16 dfsd2
3 2011 14 dfsd3
4 2011 11 dfsd4
5 2012 13 dfsd3
6 2012 15 dfsd4
7 2012 14 dfsd3
8 2012 16 dfsd4
想取得结果如下：
IDA IDB YEARA YEARB NumberA NumberB testA testB
1 5 2011 2012 13 13 dfsd1 dfsd3
2 8 2011 2012 16 16 dfsd2 dfsd4
3 7 2011 2012 14 14 dfsd3 dfsd3
查找条件：
Year字段等于Year-1的行的Number与之相同的行合并成上面的结果.
例如2011-1=2010,没有year字段等于2010,一直向下查询到2012-1=2011,查询2011所有行里是否有与Number相等的行.

Answer 1

如果该表的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]