Sql进阶用法（分组排序|级联删除）

 开窗函数，分组排序

rank()       over(partition by [field] order by [field] [asc/desc]) --同排名不连续    1 1 3
row_number() over(partition by [field] order by [field] [asc/desc]) --不同排名        1 2 3
dense_rank() over(partition by [field] order by [field] [asc/desc]) --同排名连续      1 1 2
count()      over(partition by [field] order by [field] [asc/desc]) --统计
first_value([field])over(partition by [field] order by [field] [asc/desc]) --取第一个值
last_value([field]) over(partition by [field] order by [field] [asc/desc]) --取第一个值
-- max min avg集合函数均可以使用开窗函数

over子句的复用 （window）

WINDOW r AS ( PARTITION BY [field] ORDER BY [field] [asc/desc] ) 

--复用

row_number() over(r) as row_idx,

取条件分组下的第一和倒数第一

• 条件统计，开窗函数分组排序，rank=1；倒数第一，开窗函数倒排，rank=1 或count统计排名数等于总数
• 使用开窗函数first_value()over(),last_value()over()

Mysql8.0之前不支持开窗函数，示例

SELECT
id,
@curRank := @curRank + 1 AS rank
FROM
person p,(SELECT @curRank := 0) r
ORDER BY
age

 条件统计

sum(if([condition],1,0)) --某个条件下的数量

级联删除 

        delete tmfd,tmf,tmd
        from t_model_field_dict tmfd
                 inner join t_model_field tmf using (field_id)
                 inner join t_model_dict tmd using (dict_id)
        where model_id = 1

using(field_id)

• 字段在两张表中都存在,且字段名称和类型一致
• 等同于 on tmfd.field_id=tmf.field_id