SQL Notes | ComputerScience

[marchboy]

https://marchboy.github.io/2020/08/30/sql-notes/

窗口函数【over partition by】窗口函数功能1）同时具有分组和排序的功能 2）不减少原表的行数 3）语法如下：

[marchboy]

第三步取出Top 1这条语句，可以使用WITH AS短语写为：

with t2 AS(
    with t1 as(SELECT id, city, count(*) AS ctime FROM travel GROUP BY id, city)
        select t1.id, t1.city, ctime, ROW_NUMBER() over ( PARTITION BY t1.id ORDER BY ctime DESC ) AS rank FROM t1
)
    select t2.id, t2.city from t2 where t2.rank = 1