---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (rec INT,name VARCHAR(4),dept VARCHAR(2),brand INT)
INSERT INTO @T
SELECT 1,'mary','it',5 UNION ALL
SELECT 2,'mary','it',6 UNION ALL
SELECT 3,'jack','it',5 UNION ALL
SELECT 4,'jack','fs',5--SQL查询如下:SELECT *
FROM @T AS A
WHERE NOT EXISTS(SELECT * FROM @T
WHERE name=A.name AND brand>A.brand
OR name=A.name AND brand=A.brand AND rec<A.rec)/*
rec name dept brand
----------- ---- ---- -----------
2 mary it 6
3 jack it 5(2 行受影响)*/
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (rec INT,name VARCHAR(4),dept VARCHAR(2),brand INT)
INSERT INTO @T
SELECT 1,'mary','it',5 UNION ALL
SELECT 2,'mary','it',6 UNION ALL
SELECT 3,'jack','it',5 UNION ALL
SELECT 4,'jack','fs',5--SQL查询如下:SELECT *
FROM @T AS A
WHERE NOT EXISTS(SELECT * FROM @T
WHERE name=A.name AND brand>A.brand
OR name=A.name AND brand=A.brand AND rec<A.rec)/*
rec name dept brand
----------- ---- ---- -----------
2 mary it 6
3 jack it 5(2 行受影响)*/
rec,name,dept,brand
FROM (
SELECT *,rnk=ROW_NUMBER() OVER(PARTITION BY name ORDER BY brand DESC,rec)
FROM tb
) AS A
WHERE rnk=1
drop table tb
go
create table tb([rec] int,[name] varchar(10),[dept] varchar(10),[brand] int)
insert tb select '1','mary','it',5
insert tb select '2','mary','it',6
insert tb select '3','jack','it',5
insert tb select '4','jack','fs',5
goselect *
from tb t
where not exists(select 1 from tb where name=t.name and brand>t.brand)
and not exists(select 1 from tb where brand=t.brand and name=t.name and rec<t.rec)
/*
rec name dept brand
----------- ---------- ---------- -----------
2 mary it 6
3 jack it 5(2 行受影响)
*/
drop table tb
go
create table tb([rec] int,[name] varchar(10),[dept] varchar(10),[brand] int)
insert tb select '1','mary','it',5
insert tb select '2','mary','it',6
insert tb select '3','jack','it',5
insert tb select '4','jack','fs',5
goselect *
from tb t
where rec in(select top 1 rec from tb where name=t.name order by brand desc,rec)
/*
rec name dept brand
----------- ---------- ---------- -----------
2 mary it 6
3 jack it 5(2 行受影响)
*/
(
rec int,
[name] varchar(20),
dept varchar(20),
brand int
)
insert into #EE select 1,'mary','it',5
union all select 2,'mary','it',6
union all select 3,'jack','it',5
union all select 4,'jack','fs',5select * from #EE E where not exists(select 1 from #EE where [name]=E.[name] and brand>E.brand or [name]=E.[name] and brand=E.brand and rec<E.rec)
rec name dept brand
----------- -------------------- -------------------- -----------
2 mary it 6
3 jack it 5(2 行受影响)
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-16 20:32:27
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(rec INT,name NVARCHAR(4),dept NVARCHAR(2),brand INT)
Go
INSERT INTO tb
SELECT 1,'mary','it',5 UNION ALL
SELECT 2,'mary','it',6 UNION ALL
SELECT 3,'jack','it',5 UNION ALL
SELECT 4,'jack','fs',5
GOSELECT * FROM TBselect * from tb t
where not exists(select 1 from tb where (name=t.name and brand>t.brand)
or (name=t.name and brand=t.brand and rec<t.rec)
)rec name dept brand
2 mary it 6
3 jack it 5
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([rec] int,[name] varchar(4),[dept] varchar(2),[brand] int)
insert [tb]
select 1,'mary','it',5 union all
select 2,'mary','it',6 union all
select 3,'jack','it',5 union all
select 4,'jack','fs',5
---查询---
select
rec,
name,
brand
from tb t
where
brand =(select max(brand) from tb where name=t.name)
and
rec=(select min(rec) from tb where name=t.name and brand=t.brand)---结果---
rec name brand
----------- ---- -----------
3 jack 5
2 mary 6(所影响的行数为 2 行)