某列的查询结果算上下两行的差值是否等于某个数值的问题..

[午夜恋曲]

表STUDENT中

ID AA BB

1 15 GZ
2 14 AZ
3 13 CZ
4 13 GZ
6 10 TZ
7 12 GZ
8 11 BZ
9 10 GZ
.....

我想判断BB列中等于GZ的所有AA列数值(从大到小排列)相邻的两个数值是否有等于2的
该怎么写？

通过
SELECT * FROM STUDENT
WHERE (BB = 'GZ')
ORDER BY AA DESC
得出
ID AA BB

1 15 GZ
3 13 CZ
4 13 GZ
7 12 GZ
9 10 GZ

我想判断通过此查询得出的AA列相邻上下两行的数值之间差是否有等于2的。

请大家交流下查询并判断差值的语句如何写.
谢谢啦


请教大家！

[---涛声依旧---]

DECLARE @t TABLE(id INT,aa INT,bb VARCHAR(20))
INSERT INTO @t
        ( id, aa, bb )
SELECT 1,         15,      'GZ' UNION ALL SELECT
2,         14,      'AZ' UNION ALL SELECT
3,         13,      'CZ' UNION ALL SELECT
4,         13,      'GZ' UNION ALL SELECT
6,         10,      'TZ' UNION ALL SELECT
7,         12,      'GZ' UNION ALL SELECT
8,         11,      'BZ' UNION ALL SELECT
9,         10,      'GZ'

;WITH cte  AS 
(
SELECT id,aa,bb,rn=ROW_NUMBER() OVER(PARTITION BY bb ORDER BY aa DESC) FROM @t
WHERE BB = 'GZ'
)
SELECT * FROM cte t
WHERE EXISTS(SELECT 1 FROM cte t1 WHERE t.rn=t1.rn+1 AND t.aa=t1.aa-2)
OR EXISTS(SELECT 1 FROM cte t1 WHERE t.rn+1=t1.rn AND t.aa-2=t1.aa)

[午夜恋曲]

谢谢楼上,但是我是需要放到asp文件里的,你那个写法太长,能精简成一条语句吗?

[还在加载中灬]

他这个就是一条语句啊

;WITH cte  AS 
(
SELECT id,aa,bb,rn=ROW_NUMBER() OVER(PARTITION BY bb ORDER BY aa DESC) FROM @t
WHERE BB = 'GZ'
)
SELECT * FROM cte t
WHERE EXISTS(SELECT 1 FROM cte t1 WHERE t.rn=t1.rn+1 AND t.aa=t1.aa-2)
OR EXISTS(SELECT 1 FROM cte t1 WHERE t.rn+1=t1.rn AND t.aa-2=t1.aa)

[wtujedp]

照着1楼的改了一下。可以搞成一个视图，看起来就精简了 create TABLE aa(id INT,aa INT,bb VARCHAR(20)) INSERT INTO aa ( id, aa, bb ) SELECT 1, 15, 'GZ' UNION ALL SELECT 2, 14, 'AZ' UNION ALL SELECT 3, 13, 'CZ' UNION ALL SELECT 4, 13, 'GZ' UNION ALL SELECT 6, 10, 'TZ' UNION ALL SELECT 7, 12, 'GZ' UNION ALL SELECT 8, 11, 'BZ' UNION ALL SELECT 9, 10, 'GZ' create view v_AA as WITH cte AS ( SELECT id,aa,bb,rn=ROW_NUMBER() OVER(PARTITION BY bb ORDER BY aa DESC) FROM aa WHERE BB = 'GZ' ) SELECT * FROM cte t WHERE EXISTS(SELECT 1 FROM cte t1 WHERE t.rn=t1.rn+1 AND t.aa=t1.aa-2) OR EXISTS(SELECT 1 FROM cte t1 WHERE t.rn+1=t1.rn AND t.aa-2=t1.aa)