取某字段最大值所在的的记录
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
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
-------------------------------------------
-- 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
范晨鹏
------------------
软件是一种态度
成功是一种习惯