SQL 复杂查询解决方案

一、 JOIN 查询

• 需求背景：取出一对多数据中的最后一条做 SQL JOIN 查询
• 例子： 表 project 1 对 n 表 rejected_projects， 外键： project_id
• 描述：通过 GROUP BY 对外键 project_id 分组，按条件取出最后一条数据，做成一个id集合， 然后 JOIN 这个集合取出整条记录的集合， 最后 JOIN 这个记录的集合进行复查查询

SQL 实例:

1. id 集合：

select max(rp.id) as rp_id, rp.project_id as project_id from rejected_projects rp group by rp.project_id

2. JOIN 这个 id 集合取出整条记录的集合

select rp.* from rejected_projects rp
join (select max(rp.id) as rp_id, rp.project_id as project_id from rejected_projects rp group by rp.project_id) rp_dump on rp_dump.rp_id = rp.id

3. JOIN 这个记录集合进行复杂查询

select * from projects JOIN (
  select rp.* from rejected_projects rp
  join (select max(rp.id) as rp_id, rp.project_id as project_id from rejected_projects rp group by rp.project_id) rp_dump on rp_dump.rp_id = rp.id
) rp2 on rp2.project_id = projects.id

二、 子查询

• 需求背景：某些联表查询
• 例子： 表 project 1 对 n 表 rejected_projects， 外键： project_id

1. JOIN SQL

select * from projects p join rejected_projects rp on rp.project_id = p.id where rp.created_at > '2019-12-11 00:00:00'

2. EXISTS SQL

select * from projects where 
exists (select 1 from rejected_projects rp where rp.project_id=projects.id and rp.created_at > '2019-12-11 00:00:00')

适用场景: 被 JOIN 的表很大

三、 开窗函数

ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
row_number() 返回的主要是“行”的信息，并没有排名
【参数】
【说明】Oracle分析函数

主要功能：用于取前几名，或者最后几名等

【示例】 表内容如下：

name | seqno | description
A | 1 | test
A | 2 | test
A | 3 | test
A | 4 | test
B | 1 | test
B | 2 | test
B | 3 | test
B | 4 | test
C | 1 | test
C | 2 | test
C | 3 | test
C | 4 | test

我想有一个sql语句，搜索的结果是

A | 1 | test
A | 2 | test
B | 1 | test
B | 2 | test
C | 1 | test
C | 2 | test

实现:

select name,seqno,description
from(select name,seqno,description,row_number() over (partition by name order by seqno) id
from table_name) where id<=3;

参考：

http://www.shouce.ren/api/oracle/#

---

自定义排序

比如说，我们有这样一个字段： type ( 1, 2, 3 ) 然后有这样一个需求，要按照 2, 1, 3 这样排序 oracle：

select id from projects where id IN (36611, 36612, 36613) order by decode(id, 36612,36613,36611)

mysql:

select id from bid_infos where id in (21692416, 21692672, 21693184) order by field(id, 21692672, 21693184, 21692416) desc

postgres:

SELECT * FROM pj_projects
where id IN (35771, 35772, 35773)
  ORDER BY
  CASE
    WHEN id=35772 THEN 1
    WHEN id=35771 THEN 2
    WHEN id=35773 THEN 3
  END asc