现在有一个表:表数据如下
销售员 客户 奖金
销售a 客户a 12
销售a 客户b 11
销售a 客户c 19
销售b 客户a 12
销售b 客户b 10
销售c 客户c 12 如果要查询每个销售员所对应的奖金最多的客户该怎么写?如销售a对应奖金最多的客户是客户c
使用游标该怎么实现?
销售员 客户 奖金
销售a 客户a 12
销售a 客户b 11
销售a 客户c 19
销售b 客户a 12
销售b 客户b 10
销售c 客户c 12 如果要查询每个销售员所对应的奖金最多的客户该怎么写?如销售a对应奖金最多的客户是客户c
使用游标该怎么实现?
--一定要用游标吗?
select * from tb a
where not exists
(select 1 from tb where 销售员 = a.销售员 and 奖金>a.奖金)
(select rank() over(partition by 销售员 order by 奖金) no,* from tb)
where no=1
--倒序排列
select * from (select rank() over(partition by 销售员 order by 奖金 desc) no,* from tb) where no=1
select 销售员,客户
from tb t
where 奖金 = (select max(奖金) from tb where 销售员 = t.销售员)
销售员,客户
from
tb t
where
奖金 = (select max(奖金) from tb where 销售员 = t.销售员)
销售员 VARCHAR(20),
客户 VARCHAR(20),
奖金 INT
)
INSERT INTO TB(销售员,客户,奖金)
SELECT '销售a','客户a',12
UNION ALL
SELECT '销售a','客户b',11
UNION ALL
SELECT '销售a','客户c',19
UNION ALL
SELECT '销售b','客户a',12
UNION ALL
SELECT '销售b','客户b',10
UNION ALL
SELECT '销售c','客户c',12select * into #tb from tb where 1<>1
--游标实现
DECLARE @sales varchar(20),@cus varchar(20),@NUMBER int
DECLARE SL CURSOR FOR SELECT * FROM TB
OPEN SL
FETCH NEXT FROM SL
INTO @sales,@cus,@NUMBER
WHILE @@FETCH_STATUS=0
BEGIN
IF not exists(select 1 from #tb where 销售员=@sales and 奖金>@NUMBER)
begin
delete #tb where 销售员=@sales
insert into #tb select @sales,@cus,@NUMBER
end
FETCH NEXT FROM SL
INTO @sales,@cus,@NUMBER
END
CLOSE SL
DEALLOCATE SL---------
select * from #tb
DROP TABLE tb,#tb/*
销售员 客户 奖金
-------------------- -------------------- -----------
销售a 客户c 19
销售b 客户a 12
销售c 客户c 12(3 行受影响)