sql语句数据筛选,得出结果

写SQL语句。
设关系数据库有到访城市记录表visit,表结构为:
字段名称 字段类型 字段含义
name varchar 姓名
date date 到访日期
city varchar 到访城市

该表每人每天生成一条记录,示例数据如:
name date city
张三 2022-10-01 广州
李四 2022-10-01 广州
王五 2022-10-01 深圳
张三 2022-10-02 广州
张三 2022-10-03 广州
张三 2022-10-04 深圳
张三 2022-10-05 东莞
张三 2022-10-06 东莞
张三 2022-10-07 广州
李四 2022-10-02 广州

请使用一条SQL语句,整理表数据,对于同一个人连续多日在同一个城市停留,仅保留第一天和最后一天两条记录,如果仅在该城市停留一天,则仅保留当天一条记录。例如,对于上述示例数据,您的SQL语句运行后输出结果应该是:
name date city
张三 2022-10-01 广州
张三 2022-10-03 广州
张三 2022-10-04 深圳
张三 2022-10-05 东莞
张三 2022-10-06 东莞
张三 2022-10-07 广州
李四 2022-10-01 广州
李四 2022-10-02 广州
王五 2022-10-01 深圳

1、PGSQL

    WITH visit AS (
            SELECT * 
              FROM ( VALUES ( '张三', '2022-10-01', '广州' ), ( '李四', '2022-10-01', '广州' ), ( '王五', '2022-10-01', '深圳' ), ( '张三', '2022-10-02', '广州' ), ( '张三', '2022-10-03', '广州' )
                          , ( '张三', '2022-10-04', '深圳' ), ( '张三', '2022-10-05', '东莞' ), ( '张三', '2022-10-06', '东莞' ), ( '张三', '2022-10-07', '广州' ), ( '李四', '2022-10-02', '广州' )
                          , ( '张三', '2022-10-08', '广州' ), ( '张三', '2022-10-09', '广州' ) ) AS visit( name, date, city ) )
        , t1 AS (
            SELECT *
                  , LAG( city ) OVER( PARTITION BY name ORDER BY date ) city_prev
                  , LEAD( city ) OVER( PARTITION BY name ORDER BY date ) city_next
              FROM visit )
    SELECT name, date, city FROM t1 WHERE COALESCE( city_prev, '1' ) != COALESCE( city_next, '2' )


2、Oracle(类似)

WITH visit AS (
            SELECT '张三' "name", '2022-10-01' "date", '广州' city FROM dual UNION ALL
            SELECT '李四', '2022-10-01', '广州' FROM dual UNION ALL
            SELECT '王五', '2022-10-01', '深圳' FROM dual UNION ALL
            SELECT '张三', '2022-10-02', '广州' FROM dual UNION ALL
            SELECT '张三', '2022-10-03', '广州' FROM dual UNION ALL
            SELECT '张三', '2022-10-04', '深圳' FROM dual UNION ALL
            SELECT '张三', '2022-10-05', '东莞' FROM dual UNION ALL
            SELECT '张三', '2022-10-06', '东莞' FROM dual UNION ALL
            SELECT '张三', '2022-10-07', '广州' FROM dual UNION ALL
            SELECT '李四', '2022-10-02', '广州' FROM dual UNION ALL
            SELECT '张三', '2022-10-08', '广州' FROM dual UNION ALL
            SELECT '张三', '2022-10-09', '广州' FROM dual )
        , t1 AS (
            SELECT "name", "date", city
                  , LAG( city ) OVER( PARTITION BY "name" ORDER BY "date" ) city_prev
                  , LEAD( city ) OVER( PARTITION BY "name" ORDER BY "date" ) city_next
              FROM visit )
    SELECT "name", "date", city FROM t1 WHERE COALESCE( city_prev, '1' ) != COALESCE( city_next, '2' )


楼上窗口函数玩的比较6,又让我温习了下这俩函数,附上链接供题主参考
https://tanxg.blog.csdn.net/article/details/114747524?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-114747524-blog-115403347.pc_relevant_layerdownloadsortv1&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-114747524-blog-115403347.pc_relevant_layerdownloadsortv1&utm_relevant_index=1