mysql高级查询示例

分区查询 row_number() over（partition by )

SELECT
    * 
FROM
    ( SELECT *, ROW_NUMBER() over ( PARTITION BY client_id ORDER BY create_time DESC ) num FROM app_room_memer WHERE room_id = 169979776294064129 ) temp 
WHERE
    temp.num = 1;

mysql在8.0.1版本加入了cte递归查询

表结构： ||  id  ||  pid  ||  name ||

查询id为1的节点以及他所有的子孙节点

with recursive cte as (
    select * from tree where id = 1
    union all 
    select t.* from tree as t inner join cte on t.pid = cte.id
) select * from cte;