求一高效查询统计语句

[pangjinyu]

orderID orgion name total
1 A ZHANGSAN 1000.00
2 A ZHANGSAN 1344.00
3 A RRRR 2344.00
4 A RRRR 2344.00
5 B LISI 1000.00
6 B WANGWU 3000.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

要查上表每个orgion 中 sum(total)最小的 name
一个语句，实现不难但求精炼！
另不排除一个orgion中两个人的TOTAL合计是一样的，及个别NAME是跨orgion 的

[Sweet-Tang]

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))

是这样的吗？

[pangjinyu]

谢谢1楼，可能我没表达清楚
结果显示是这样的：

orgion,name,total合计
A ZHANGSAN 2344
B LISI 1000
C YYYY 5003

即按每个ORGION统计，显示每个ORGION中销售合计最小的NAME及TOTAL合计结果

[AcHerat]

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 行受影响)

[fcuandy]

http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html