现在有一个表:表数据如下
销售员 客户 奖金
销售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 t
where not exists (select 1 from tb where 销售员 = t.销售员 and 奖金 > t.奖金)
select a.销售员,a.客户,a.奖金 from (
select 销售员,客户,奖金,row_number()over(partition by 销售员 order by 奖金 desc) as row) as a
where a.row=1
select 销售员,客户
from (
select 销售员,客户,row_number()over(partition by 销售员 order by sum(奖金) desc) as row
from tb
group by 销售员,客户
) as a
where row=1
select 销售员,客户,奖金,row_number()over(partition by 销售员 order by 奖金 desc) as row from tb) as a
where a.row=1
快下班了,哈哈
from (select row_number() over(partition by 销售员 order by 奖金 desc) no,* from tb)
where no=1
SELECT *
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC) NB
FROM TB))A
WHERE NB=1