求一条SQL语句

[mynamelj]

一张表tb结构如下:

编号 品种 跟单员 接单数量
-----------------------------------
1 XXX A 300
2 XXX A 200
3 XXX B 500
4 XXX B 600
5 XXX C 400
6 XXX C 300
7 XXX D 350
8 XXX D 480


需求:
1.找出接单数（合计）最大的一个跟单员
2.找出接单数（合计）超过800的跟单员
3.用一条SQL语句来完成

[liangCK]

1.
select top 1 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
order by sum(接单数量) desc

2.
select 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
having sum(接单数量)>800

[-狙击手-]

1.
select top 1 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
order by sum(接单数量) desc

2.
select 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
having sum(接单数量)>800

[leo_lesley]

select max(sum(接单数量))
from tb
group by 跟单员


select sum(接单数量)
from tb
group by 跟单员
having sum(接单数量)>800

[kk19840210]

1.找出接单数（合计）最大的一个跟单员

select top 1 跟单员 from (select 跟单员,接单数量=sum(接单数量) from tb  group by 跟单员)a

2.找出接单数（合计）超过800的跟单员
select 跟单员 from (select 跟单员,接单数量=sum(接单数量) from tb  group by 跟单员) a where a.接单数量>=800

[wzy_love_sly]

declare @tb table(编号 int,品种 varchar(10),跟单员 varchar(10),接单数量 int)
insert into @tb select 1,'xxx','a',300
insert into @tb select 2,'xxx','a',200
insert into @tb select 3,'xxx','b',500
insert into @tb select 4,'xxx','b',600
insert into @tb select 5,'xxx','c',400
insert into @tb select 6,'xxx','c',300
insert into @tb select 7,'xxx','d',300
insert into @tb select 8,'xxx','d',480

--1
select  top 1 跟单员,sum(接单数量) from @tb group by 跟单员 order by sum(接单数量) desc
--2
select 跟单员,sum(接单数量) from @tb group by 跟单员 having sum(接单数量)>700

跟单员 (无列名)
b 1100

跟单员 (无列名)
b 1100
d 780

[leo_lesley]

create table tb(编号 int, 品种 varchar(10), 跟单员 varchar(10),  接单数量 int)
insert tb
select 1             ,'XXX',           'A',               300 
union select 2             ,'XXX',             'A',               200 
union select 3             ,'XXX',           'B',               500 
union select 4             ,'XXX',           'B',               600 
union select 5             ,'XXX',           'C',               400 
union select 6             ,'XXX',           'C',               300 
union select 7             ,'XXX',           'D',               350 
union select 8            ,'XXX',           'D',               480 



select top 1 跟单员,cnt=sum(接单数量)
from tb
group by 跟单员
having sum(接单数量)>800
order by cnt desc


drop table tb

[wzy_love_sly]

哈 没人回答3？

[liangCK]

leo的..聚合函数..不能这样哦.

[mynamelj]

我是楼住，把第一个需求改一下。

不是找出最大数的一个跟单员，而是找出跟单总数最大的跟单员，例如有N个最大数，这种情况怎样写呢？

[-狙击手-]

create table tb(编号 int, 品种 varchar(10), 跟单员 varchar(10),  接单数量 int)
insert tb
select 1             ,'XXX',           'A',               300 
union select 2             ,'XXX',             'A',               200 
union select 3             ,'XXX',           'B',               500 
union select 4             ,'XXX',           'B',               600 
union select 5             ,'XXX',           'C',               400 
union select 6             ,'XXX',           'C',               300 
union select 7             ,'XXX',           'D',               350 
union select 8            ,'XXX',           'D',               480 



select top 1 跟单员,cnt=count(1)
from tb
group by 跟单员
order by cnt desc


drop table tb

[昵称被占用了]

--1

select 跟单员,sum(接单数量) 合计
from tb 
group by 跟单员
having sum(接单数量)=(select top 1 sum(接单数量) from tb group by 跟单员 order by sum(接单数量) desc)

[mynamelj]

谢谢大家的回答，第一个需要改下：
不是找出最大数的一个跟单员，而是找出跟单总数最大的跟单员，例如有N个最大数，这种情况怎样写呢？

[昵称被占用了]

declare @tb table (
编号 int, 品种 varchar(10), 跟单员 varchar(10),  接单数量 int)

insert @tb
select 1             ,'XXX',           'A',               300 
union select 2             ,'XXX',             'A',               200 
union select 3             ,'XXX',           'B',               500 
union select 4             ,'XXX',           'B',               600 
union select 5             ,'XXX',           'C',               400 
union select 6             ,'XXX',           'C',               700 
union select 7             ,'XXX',           'D',               350 
union select 8            ,'XXX',           'D',               480 



select 跟单员,sum(接单数量) 合计
from @tb 
group by 跟单员
having sum(接单数量)=(select top 1 sum(接单数量) from @tb group by 跟单员 order by sum(接单数量) desc)


--结果
跟单员        合计          
---------- ----------- 
B          1100
C          1100

（所影响的行数为 2 行）

[-狙击手-]

create table tb(编号 int, 品种 varchar(10), 跟单员 varchar(10),  接单数量 int)
insert tb
select 1             ,'XXX',           'A',               300 
union select 2             ,'XXX',             'A',               200 
union select 3             ,'XXX',           'B',               500 
union select 4             ,'XXX',           'B',               600 
union select 5             ,'XXX',           'C',               400 
union select 6             ,'XXX',           'E',               300 
union select 7             ,'XXX',           'D',               350 
union select 8            ,'XXX',           'D',               480 


select a.* 
from(
select  跟单员,cnt=count(1)
from tb
group by 跟单员) a
right join (select top 1 跟单员,count(1) as id from  tb group by 跟单员) b
on a.cnt = b.id
/*
跟单员        合计          
---------- ----------- 
A          2
B          2
D          2

（所影响的行数为 3 行）
*/

select 跟单员, count(1) 合计
from tb 
group by 跟单员
having  count(1)=(select top 1 count(1) from tb group by 跟单员 order by sum(接单数量) desc)

/*
跟单员        合计          
---------- ----------- 
A          2
B          2
D          2

（所影响的行数为 3 行）
*/



drop table tb

[wzy_love_sly]

没卡懂

[kk19840210]

select top 1 跟单员 from (select 跟单员,接单数量=sum(接单数量) from tb  group by 跟单员 desc)a

[-狙击手-]

看错了

create table tb(编号 int, 品种 varchar(10), 跟单员 varchar(10),  接单数量 int)
insert tb
select 1             ,'XXX',           'A',               300 
union select 2             ,'XXX',             'A',               800 
union select 3             ,'XXX',           'B',               500 
union select 4             ,'XXX',           'B',               600 
union select 5             ,'XXX',           'C',               400 
union select 6             ,'XXX',           'E',               300 
union select 7             ,'XXX',           'D',               350 
union select 8            ,'XXX',           'D',               480 


select a.* 
from(
select  跟单员,cnt=sum(接单数量)
from tb
group by 跟单员) a
right join (select top 1 跟单员,sum(接单数量) as id from  tb group by 跟单员 order by sum(接单数量) desc) b
on a.cnt = b.id
/*

跟单员        cnt         
---------- ----------- 
A          1100
B          1100

（所影响的行数为 2 行）
*/




drop table tb

[utpcb]

1.
select top 1 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
order by sum(接单数量) desc

2.
select 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
having sum(接单数量)>800