not exist 可用于根据某字段的字值范围来筛选记录。其原理是:在已经选出的记录中查找某个字段“没有比它更大/小/一样”的记录 作为最后的结果。
例:
v取code 相同的记录中 values 最大的记录 ,如果 values 值有相同的 取 id 大的一条
id code values
1 a 2
2 a 3
3 a 3
4 b 1
5 b 4
6 b 4
显示结果
id code values
3 a 3
6 b 4
代码
--
-----------------------------------------
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-04 20:32:39
-- -----------------------------------------
-- > 生成测试数据: @T
DECLARE @T TABLE (id INT ,code VARCHAR ( 1 ), [ values ] INT )
INSERT INTO @T
SELECT 1 , ' a ' , 2 UNION ALL
SELECT 2 , ' a ' , 3 UNION ALL
SELECT 3 , ' a ' , 3 UNION ALL
SELECT 4 , ' b ' , 1 UNION ALL
SELECT 5 , ' b ' , 4 UNION ALL
SELECT 6 , ' b ' , 4 UNION ALL
SELECT 7 , ' c ' , 1
-- SQL查询如下:
-- 1
SELECT * FROM @T AS A
WHERE NOT EXISTS ( SELECT * FROM @T WHERE code = A.code AND [ values ] > A. [ values ] )
AND NOT EXISTS ( SELECT * FROM @T
WHERE code = A.code AND [ values ] = A. [ values ] AND id > A.id);
-- 2
SELECT id,code, [ values ]
FROM ( SELECT rowid = ROW_NUMBER() OVER (PARTITION BY code ORDER BY [ values ] DESC ,id DESC ), *
FROM @T ) AS T
WHERE rowid = 1 ;
/*
id code values
----------- ---- -----------
3 a 3
6 b 4
7 c 1
(3 row(s) affected)
id code values
----------- ---- -----------
3 a 3
6 b 4
7 c 1
(3 row(s) affected)
*/
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-04 20:32:39
-- -----------------------------------------
-- > 生成测试数据: @T
DECLARE @T TABLE (id INT ,code VARCHAR ( 1 ), [ values ] INT )
INSERT INTO @T
SELECT 1 , ' a ' , 2 UNION ALL
SELECT 2 , ' a ' , 3 UNION ALL
SELECT 3 , ' a ' , 3 UNION ALL
SELECT 4 , ' b ' , 1 UNION ALL
SELECT 5 , ' b ' , 4 UNION ALL
SELECT 6 , ' b ' , 4 UNION ALL
SELECT 7 , ' c ' , 1
-- SQL查询如下:
-- 1
SELECT * FROM @T AS A
WHERE NOT EXISTS ( SELECT * FROM @T WHERE code = A.code AND [ values ] > A. [ values ] )
AND NOT EXISTS ( SELECT * FROM @T
WHERE code = A.code AND [ values ] = A. [ values ] AND id > A.id);
-- 2
SELECT id,code, [ values ]
FROM ( SELECT rowid = ROW_NUMBER() OVER (PARTITION BY code ORDER BY [ values ] DESC ,id DESC ), *
FROM @T ) AS T
WHERE rowid = 1 ;
/*
id code values
----------- ---- -----------
3 a 3
6 b 4
7 c 1
(3 row(s) affected)
id code values
----------- ---- -----------
3 a 3
6 b 4
7 c 1
(3 row(s) affected)
*/
来源网址:
http://topic.csdn.net/u/20090904/20/70d86812-0ae8-412e-93b9-98dfe5d220e1.html