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' )