22,300
社区成员




create table tb
(
orderid int,
orgion varchar(10),
[name] varchar(100),
total numeric(10,2)
)
insert into tb
select '1','A','ZHANGSAN','1000.00' union all
select '2','A','ZHANGSAN','1344.00' union all
select '3','A','RRRR','2344.00' union all
select '4','B','RRRR','2344.00' union all
select '5','B','LISI','3000.00' union all
select '6','B','WANGWU','1000.00' union all
select '7','C','XXX','4000.00' union all
select '8','C','XXX','4000.00' union all
select '9','C','XXX','1000.00' union all
select '10','C','YYYY','4000.00' union all
select '11','C','YYYY','1003.00'
go
with cte as
(
select orgion,[name],sum(total)total
from tb
group by orgion,[name]
)
select *
from cte t
where not exists (select 1 from cte where orgion = t.orgion and total > t.total)
drop table tb
/*
orgion name total
---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------
A RRRR 2344.00
A ZHANGSAN 2344.00
B LISI 3000.00
C XXX 9000.00
(4 行受影响)
create table TT
(
orderID int,
orgion char(1),
name varchar(100),
total numeric(10,2)
)
go
insert into TT(orderID,orgion,name,total)
values
('1','A','ZHANGSAN','1000.00'),
('2','A','ZHANGSAN','1344.00'),
('3','A','RRRR','2344.00'),
('4','B','RRRR','2344.00'),
('5','B','LISI','3000.00'),
('6','B','WANGWU','1000.00'),
('7','C','XXX','4000.00'),
('8','C','XXX','4000.00'),
('9','C','XXX','1000.00'),
('10','C','YYYY','4000.00'),
('11','C','YYYY','1003.00')
GO
select orderID,orgion,name,total from TT where name=( select top 1 name totalSUM from TT group by name order by SUM(total))