表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)
------解决思路----------------------
他这个就是一条语句啊
;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)
------解决思路----------------------
照着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)