晴风

1.T1表内所有数据是否存在T2表中

SELECT * FROM table_T1 t1 WHERE NOT EXISTS (
    SELECT 1 FROM table_T2 t2 WHERE t1.key1=t2.key1 AND t1.key2=t2.key2
)

2.如果T2中存在的数据包含在T1内,则删除T1的对应数据

DELETE FROM table_T1 t1 WHERE EXISTS (
    SELECT 1 FROM table_T2 t2 WHERE t1.key1=t2.key1 AND t1.key2=t2.key2
)

3.辅助筛选
有一张表,存机器登录的记录,关键字段如下:

CREATE TABLE Login_detailed(
id int,                     -- 机器id
user string,             -- 用户名
create_time TIMESTAMP,     -- 登录时间
...
)

保留某一机器id最后登录的user在这台机器上的所有登录记录:

SELECT * FROM Login_detailed t1 WHERE EXISTS(
    SELECT 1 FROM (
        SELECT id,user,create_time,ROW_NUMBER() OVER(PARTITION BY id ORDER BY create_time DESC) rn FROM Login_detailed
    ) t2 WHERE rn=1 AND t1.id=t2.id AND t1.user=t2.user
)