--> 生成测试数据: @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 行受影响)*/
if object_id('tb') is not null 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 行受影响) */不好意思,没注意看题。改正
if object_id('tb') is not null 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 行受影响) */再一种方法。
create table #EE ( 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 行受影响)
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
-- 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 行受影响)*/
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 行受影响)