--> 测试数据: #tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb (PHARTG varchar(4),PHDCTO varchar(2),PHDOCO int,PHOTOT int,APALIM bigint,APRPER int) insert into #tb select 'SSAE','OP',88905902,204250,100000,20034 union all select 'SSAE','OP',88905902,204250,250000,20026 union all select 'SSAE','OP',88905902,204250,99999999999,20030 union all select 'SSAE','OP',88905903,4250,100000,20034 union all select 'SSAE','OP',88905903,4250,250000,20026 union all select 'SSAE','OP',88905903,4250,99999999999,20030select * from #tb t where APALIM=(select min(APALIM) from #tb where PHOTOT =t.PHOTOT and APALIM>t.PHOTOT)
--1、另一种算法 --> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([PHARTG] varchar(4),[PHDCTO] varchar(2),[PHDOCO] int,[PHOTOT] int,[APALIM] bigint,[APRPER] int) insert [TB] select 'SSAE','OP',88905902,204250,100000,20034 union all select 'SSAE','OP',88905902,204250,250000,20026 union all select 'SSAE','OP',88905902,204250,99999999999,20030 union all select 'SSAE','OP',88905903,4250,100000,20034 union all select 'SSAE','OP',88905903,4250,250000,20026 union all select 'SSAE','OP',88905903,4250,99999999999,20030select * from [TB]WITH TT AS( SELECT A.*,ROW_NUMBER() OVER (PARTITION BY [PHOTOT] ORDER BY [PHOTOT]) AS [NUM] FROM TB A WHERE NOT EXISTS(SELECT * FROM TB WHERE A.[PHOTOT] = [PHOTOT] AND A.[APALIM]<[PHOTOT]))SELECT * FROM TT WHERE num = 1/* PHARTG PHDCTO PHDOCO PHOTOT APALIM APRPER NUM ------ ------ ----------- ----------- -------------------- ----------- -------------------- SSAE OP 88905903 4250 100000 20034 1 SSAE OP 88905902 204250 250000 20026 1(2 行受影响)*/
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (PHARTG varchar(4),PHDCTO varchar(2),PHDOCO int,PHOTOT int,APALIM bigint,APRPER int)
insert into #tb
select 'SSAE','OP',88905902,204250,100000,20034 union all
select 'SSAE','OP',88905902,204250,250000,20026 union all
select 'SSAE','OP',88905902,204250,99999999999,20030 union all
select 'SSAE','OP',88905903,4250,100000,20034 union all
select 'SSAE','OP',88905903,4250,250000,20026 union all
select 'SSAE','OP',88905903,4250,99999999999,20030select * from #tb t
where APALIM=(select min(APALIM) from #tb where PHOTOT =t.PHOTOT and APALIM>t.PHOTOT)
PHARTG PHDCTO PHDOCO PHOTOT APALIM APRPER
------ ------ ----------- ----------- -------------------- -----------
SSAE OP 88905902 204250 250000 20026
SSAE OP 88905903 4250 100000 20034(2 行受影响)
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([PHARTG] varchar(4),[PHDCTO] varchar(2),[PHDOCO] int,[PHOTOT] int,[APALIM] bigint,[APRPER] int)
insert [TB]
select 'SSAE','OP',88905902,204250,100000,20034 union all
select 'SSAE','OP',88905902,204250,250000,20026 union all
select 'SSAE','OP',88905902,204250,99999999999,20030 union all
select 'SSAE','OP',88905903,4250,100000,20034 union all
select 'SSAE','OP',88905903,4250,250000,20026 union all
select 'SSAE','OP',88905903,4250,99999999999,20030select * from [TB]WITH TT
AS(
SELECT A.*,ROW_NUMBER() OVER (PARTITION BY [PHOTOT] ORDER BY [PHOTOT]) AS [NUM]
FROM TB A
WHERE NOT EXISTS(SELECT * FROM TB WHERE A.[PHOTOT] = [PHOTOT] AND A.[APALIM]<[PHOTOT]))SELECT * FROM TT WHERE num = 1/*
PHARTG PHDCTO PHDOCO PHOTOT APALIM APRPER NUM
------ ------ ----------- ----------- -------------------- ----------- --------------------
SSAE OP 88905903 4250 100000 20034 1
SSAE OP 88905902 204250 250000 20026 1(2 行受影响)*/