请问这个 SQL 怎么写

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

MySQL 5.5 Community Server

MySQL 5.6 Community Server

Percona Configuration Wizard

XtraBackup 搭建主从复制

Great Sites on MySQL

Percona

MySQL Performance Blog

Severalnines

推荐管理工具

Sequel Pro

phpMyAdmin

推荐书目

MySQL Cookbook

MySQL 相关项目

MariaDB

Drizzle

参考文档

http://mysql-python.sourceforge.net/MySQLdb.html

这是一个创建于 2125 天前的主题，其中的信息可能已经有所发展或是发生改变。

客户 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 感激不尽~

SUM

SQL

业务员

客户

18 条回复 2020-02-13 17:12:17 +08:00

eq06

2020-02-12 11:40:49 +08:00

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

wps353

2020-02-12 11:42:13 +08:00

google 搜索 MySQL 分组排名

gz911122

2020-02-12 11:45:09 +08:00

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

newtype0092

2020-02-12 11:45:53 +08:00

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

mmdsun

2020-02-12 11:46:23 +08:00

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

fancy111

2020-02-12 11:47:38 +08:00

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

xe2vherd

2020-02-12 13:13:12 +08:00

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

2020-02-12 13:24:42 +08:00

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

Peachl

2020-02-12 13:28:37 +08:00

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

Peachl

2020-02-12 13:31:47 +08:00

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

conge

PRO

2020-02-12 13:38:50 +08:00

我试试哈

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

PRO

2020-02-12 13:40:29 +08:00

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

onhao

2020-02-12 15:08:17 +08:00

斗胆献上艺伎
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

2020-02-12 15:17:25 +08:00

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

2020-02-12 16:22:25 +08:00

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

2020-02-12 17:31:58 +08:00

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

aguesuka

2020-02-12 21:58:43 +08:00

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

cocosing

2020-02-13 17:12:17 +08:00

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