求一高效查询统计语句

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
回复

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

是这样的吗?
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-03-05 02:15
社区公告
暂无公告