1. GROUP_CONCAT : 分组联合
SELECT
a.city_office_id,
a.city_office_name,
a.area,
a.province,
GROUP_CONCAT(b.alias_name SEPARATOR ' | ') AS alias_name
FROM
bd_city_office a
LEFT JOIN bd_city_office_alias b ON a.city_office_id = b.city_office_id
GROUP BY a.city_office_id,a.created_time ORDER BY a.created_time desc
37 大连办事处 北区 辽宁 大连办事处 | 大连 | 大连办事处-技服
36 辽宁代表处 北区 辽宁 辽宁 | 辽宁代表处-技服 | 沈阳 | 沈阳办事处
35 内蒙古代表处 北区 内蒙古 呼和浩特 | 呼和浩特办事处 | 内蒙古 | 内蒙古代表处-技服
2. Row_Number:按分数排名
SELECT score, (SELECT count(DISTINCT s.score) FROM t_score s WHERE s.score >= t.score) rank
FROM t_score t ORDER BY t.score DESC
4.2 1
4.2 1
3.5 2
3.5 2
3.2 3
3.2 3
3. 分组取前3名
select t.name, t.salary, t.departmentid
from t_salary t where 3 > (select count(*) from t_salary where departmentid = t.departmentid and salary > t.salary ) order BY departmentid, salary desc;
| Id | Name | Salary | DepartmentId |
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| DepartmentId | Employee | Salary |
| 1 | Max | 90000 |
| 1 | Randy | 85000 |
|1 | Joe | 70000 |
| 2 | Henry | 80000 |
| 2 | Sam | 60000 |