求一高效查询统计语句

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 的
...全文
124 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
fcuandy 2011-03-05
  • 打赏
  • 举报
回复
http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html
AcHerat 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))

是这样的吗?

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧