现在有一个表：表数据如下
销售员 客户 奖金
销售a 客户a 12
销售a 客户b 11
销售a 客户c 19
销售b 客户a 12
销售b 客户b 10
销售c 客户c 12
如果要查询每个销售员所对应的奖金最多的客户该怎么写？如销售a对应奖金最多的客户是客户c
用游标的话该怎么实现？
------解决方案--------------------
- SQL code
--一定要用游标吗？select * from tb awhere not exists(select 1 from tb where 销售员 = a.销售员 and 奖金>a.奖金)
------解决方案--------------------
- SQL code
select * from (select rank() over(partition by 销售员 order by 奖金) no,* from tb)where no=1
------解决方案--------------------
- SQL code
select 销售员,客户,max(奖金) as 奖金 from tablenamegroup by 销售员,客户
------解决方案--------------------
我决定 他的每个帖子我都去蹭蹭分
------解决方案--------------------
- SQL code
select 销售员,客户from tb twhere 奖金 = (select max(奖金) from tb where 销售员 = t.销售员)
------解决方案--------------------
- SQL code
select 销售员,客户 from tb t where 奖金 = (select max(奖金) from tb where 销售员 = t.销售员)
------解决方案--------------------
- SQL code
CREATE TABLE TB(销售员 VARCHAR(20),客户 VARCHAR(20),奖金 INT)INSERT INTO TB(销售员,客户,奖金)SELECT '销售a','客户a',12UNION ALLSELECT '销售a','客户b',11UNION ALLSELECT '销售a','客户c',19UNION ALLSELECT '销售b','客户a',12UNION ALLSELECT '销售b','客户b',10UNION ALLSELECT '销售c','客户c',12select * into #tb from tb where 1<>1--游标实现DECLARE @sales varchar(20),@cus varchar(20),@NUMBER intDECLARE SL CURSOR FOR SELECT * FROM TBOPEN SLFETCH NEXT FROM SLINTO @sales,@cus,@NUMBERWHILE @@FETCH_STATUS=0BEGIN IF not exists(select 1 from #tb where [email protected] and 奖金>@NUMBER) begin delete #tb where [email protected] insert into #tb select @sales,@cus,@NUMBER end FETCH NEXT FROM SL INTO @sales,@cus,@NUMBERENDCLOSE SLDEALLOCATE SL---------select * from #tbDROP TABLE tb,#tb/*销售员 客户 奖金-------------------- -------------------- -----------销售a 客户c 19销售b 客户a 12销售c 客户c 12(3 行受影响)