一张表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语句来完成
-----------------------------------
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语句来完成
解决方案 »
- 触发器问题---------------------------------------只有这些分了-----
- Microsoft SQL Server 2012 Service Pack 1 安装失败
- 我是新手!!!请教各位大侠~~如何将整个文件存储在sql数据表的一个字段中??
- 我在SQLSERVER 2005附加数据库时出错:此数据库为655版本,而服务器仅支持611版本或更低级版本。我该怎么办?升级吗?好像不可以。
- 请教在存储过程中如何使用循环语句?
- 资料游标
- 我想在sql server在full text search中去查询多国的语言,请问怎么办?
- SQL Server 的 SQLServerAgent 服务不能启动啊,请教啊?
- 请问如何在paradox7里写case语句??急急啊!!
- 消息 217,级别 16,状态 1,过程 SearchClass1,第 19 行 超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)。
- 送分!!!
- 一条update语句
select top 1 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
order by sum(接单数量) desc2.
select 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
having sum(接单数量)>800
select top 1 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
order by sum(接单数量) desc2.
select 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
having sum(接单数量)>800
from tb
group by 跟单员
select sum(接单数量)
from tb
group by 跟单员
having sum(接单数量)>800
1.找出接单数(合计)最大的一个跟单员select top 1 跟单员 from (select 跟单员,接单数量=sum(接单数量) from tb group by 跟单员)a2.找出接单数(合计)超过800的跟单员
select 跟单员 from (select 跟单员,接单数量=sum(接单数量) from tb group by 跟单员) a where a.接单数量>=800
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
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
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
--1select 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
having sum(接单数量)=(select top 1 sum(接单数量) from tb group by 跟单员 order by sum(接单数量) desc)
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 行)
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
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
select top 1 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
order by sum(接单数量) desc2.
select 跟单员,sum(接单数量) 合计
from tb
group by 跟单员
having sum(接单数量)>800