求一高效查询统计语句

pangjinyu 2011-03-05 02:15:10

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 的

...全文

49 点赞收藏 4

写回复

4 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

fcuandy 2011-03-05

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

回复赞

兔子党党务院院长 2011-03-05



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

回复赞

pangjinyu 2011-03-05

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

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

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

回复赞

Sweet-Tang 2011-03-05







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