请问这个 SQL 怎么写

[mmdsun]

客户 id | 业务员 id | 金额
:-: | :-: | :-:
A | B | 10
A | B | 20
C | Z | 10
D | Z | 10

查询每个业务员的前 10 名的客户 这个 SQL 怎么写呀. (是按金额 sum 的).

SELECT 客户 id, sum(金额) as sum from t
where 业务 id = ''
GROUP BY 客户 id
ORDER BY sum desc
limit 10

我只会拆分多个 sql 这样写,但达不到要求.. 求指点 SQL 感激不尽~

[eq06]

一个 客户 id | 业务员 id 的 pair 有唯一一条数据吗

[wps353]

google 搜索 MySQL 分组排名

[gz911122]

Google 搜 mysql top n
如果是 mysql 8.0 的话比较简单,有 window 函数

[newtype0092]

每个业务员的前 10 名，这相当于每个业务员的数据都需要一次独立的排序，一条语句无法做到吧。

[mmdsun]

@eq06 谢谢回复. 这个表是打款记录表. 每个客户有多次打款记录. 一个客户 只属于一个业务员.

[fancy111]

秘诀：当你不知道一条 SQL 语句怎么写的时候，你就分开写两句，两句不行的话就三句。总会行的

[zmxnv123]

select employee_id, customer_id from (
select employee_id, customer_id, (select count(*)+1 from t t2 where t2.money > t1.money and t2.employee_id = t1.employee_id ) AS od FROM t t1) AS t3 where t3.od <= 10

[mmdsun]

@zmxnv123 where t2.money > t1.money 这样 好像 不行列. 我这边 金额 是 根据 客户 id 分组 sum 聚合得到的. where 里面 没办法用 聚合函数.

[Peachl]

select 业务,客户,sum(金额) from t group by 业务,客户 order by sum(金额) limit 10 这样？

[Peachl]

哦 好像是每个业务员的前十 那你还要用 having 一下 然后条件写每个业务员的 id 不想写 having 那就用外连接一次员工表 这样可以求全部的

[conge]

我试试哈

SELECT *
FROM
(
SELECT *,
RANK() OVER (PARTITION BY 业务员 id ORDER BY sum DESC) AS rank,

from (SELECT 客户 id, 业务员 id , sum(金额) as sum)
)
WHERE
rank > 11

[conge]

不好意思，我写的有错，最后不是 rank > 11，而应该是 rank < 11

[onhao]

斗胆献上艺伎
select group_concat(select 客户 id from (SELECT 客户 id, sum(金额) as sum from t
where 业务 id = ''
GROUP BY 客户 id
ORDER BY sum desc
limit 10)tt) from t group by 业务员 id

@mmdsun

保证满足你的要求，如有问题，可以先尝试解决，希望你顺利解决^_^！

[onhao]

select group_concat(select 客户 id from (SELECT 客户 id, sum(金额) as sum from t
where 业务 id =t.业务 id
GROUP BY 客户 id
ORDER BY sum desc
limit 10)tt) from t group by 业务员 id
可能还有其他需要注意的地方，多识几次

[wangqianwei]

CREATE TABLE `DEMO`(
`b_id` INT NOT NULL COMMENT '业务员 ID',
`c_id` INT NOT NULL COMMENT '客户 ID',
`sale` INT NOT NULL COMMENT '价格'
);

INSERT INTO `DEMO` (`b_id`, `c_id`, `sale`)
VALUES (1, 2, 1),
(1, 2, 1),
(1, 3, 1),
(1, 3, 1),
(1, 4, 1),
(1, 4, 1),
(1, 5, 1),
(1, 5, 1),
(1, 6, 1),
(2, 1, 3),
(2, 1, 1),
(2, 2, 3),
(2, 2, 1),
(2, 4, 3),
(2, 5, 1),
(2, 6, 3),
(2, 6, 1),
(3, 1, 3),
(3, 1, 1),
(3, 2, 3),
(3, 2, 1),
(3, 4, 3),
(3, 5, 1),
(3, 6, 3),
(3, 6, 1);

SELECT * FROM `DEMO`;

+------+------+------+
| b_id | c_id | sale |
+------+------+------+
| 1 | 2 | 1 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 1 | 3 | 1 |
| 1 | 4 | 1 |
| 1 | 4 | 1 |
| 1 | 5 | 1 |
| 1 | 5 | 1 |
| 1 | 6 | 1 |
| 2 | 1 | 3 |
| 2 | 1 | 1 |
| 2 | 2 | 3 |
| 2 | 2 | 1 |
| 2 | 4 | 3 |
| 2 | 5 | 1 |
| 2 | 6 | 3 |
| 2 | 6 | 1 |
| 3 | 1 | 3 |
| 3 | 1 | 1 |
| 3 | 2 | 3 |
| 3 | 2 | 1 |
| 3 | 4 | 3 |
| 3 | 5 | 1 |
| 3 | 6 | 3 |
| 3 | 6 | 1 |
+------+------+------+

SELECT `b_id`, `c_id`, `sales`
FROM (SELECT `b_id`, `c_id`, `sales`, RANK() OVER(PARTITION BY `b_id` ORDER BY `sales` DESC) as level
FROM (SELECT `b_id`, `c_id`, SUM(`sale`) AS `sales` FROM DEMO GROUP BY `b_id`, `c_id`) SUM_DEMO
order by `b_id`) LEVEL_DEMO
WHERE level <= 10;

+------+------+-------+
| b_id | c_id | sales |
+------+------+-------+
| 1 | 2 | 2 |
| 1 | 3 | 2 |
| 1 | 4 | 2 |
| 1 | 5 | 2 |
| 1 | 6 | 1 |
| 2 | 1 | 4 |
| 2 | 2 | 4 |
| 2 | 6 | 4 |
| 2 | 4 | 3 |
| 2 | 5 | 1 |
| 3 | 1 | 4 |
| 3 | 2 | 4 |
| 3 | 6 | 4 |
| 3 | 4 | 3 |
| 3 | 5 | 1 |
+------+------+-------+

[dovme]

https://www.cnblogs.com/niniya/p/9046449.html

[aguesuka]

oracle 有专门的函数，rownumb over partition，mysql 应该也有

[cocosing]

select emp_id, custom_id, sum
from (
select emp_id, custom_id, sum(money) as sum
from orders
group by emp_id, custom_id
) as o1
where (
select count(distinct o2.emp_id, o2.custom_id)
from (
select emp_id, custom_id, sum(money) as sum
from orders
group by emp_id, custom_id
) as o2
where o2.emp_id = o1.emp_id
and o2.sum > o1.sum
) < 10
group by emp_id, custom_id
order by emp_id, sum;
这个使用派生表进行然后取每个业务员的 top10 应该满足你的需求
@mmdsun