SQL进阶技巧 | 如何取多个表中某一字段不为空且时间最新的值？-CSDN博客

本文链接：https://blog.csdn.net/godlovedaniel/article/details/147720371

0 问题描述

问题：如何取多个表中某一字段不为空且时间最新的值？

举个例子，多张表（其中人会有重复）中都有电话号码这个字段，我需要取每个人最新的电话号码。

表1，命名为t1

name	contact_phone	update_time
王二	111	2019
张三	123	2019
李四	642	2020

表2，命名为t2

name	contact_phone	update_time
王二	222	2022
张三	NULL	2021
李四	753	2010

表3，命名为t3

name	contact_phone	update_time
张三	789	2012
李四	864	2021

最后需要的结果为

name	contact_phone	update_time
王二	222	2022
张三	123	2019
李四	864	2021

1 数据准备

-- 创建表 t1
CREATE TABLE t1 (
name STRING,
contact_phone STRING,
update_time INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';  
-- 创建表 t2
CREATE TABLE t2 (
name STRING,
contact_phone STRING,
update_time INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

-- 创建表 t3
CREATE TABLE t3 (
name STRING,
contact_phone STRING,
update_time INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

-- 插入 t1 数据
truncate table t1 ;
INSERT INTO t1 VALUES
('王二',   '111', 2019),
('张三',   '123', 2019),
('李四',   '642', 2020);

-- 插入 t2 数据
truncate table t2 ;
INSERT INTO t2 VALUES
('王二', '222',   2022),
('张三', NULL,    2021),  
('李四', '753',   2010);

-- 插入 t3 数据
INSERT INTO t3 VALUES
('张三', '789', 2012),
('李四', '864', 2021);

2 问题分析

将三张表合并，形成原始数据表

select * from t1union allselect * from t2union allselect * from t3

组内取最新时间点对应的值，我们一般用row_number()或first_value()函数，但如果直接使用分析函数会导致张三这一组数据取到最新的值为NULL，明显是不符合题意的，题目要求为字段不为NULL且最新。

方法1：利用order by nulls last特性求解

with temp as

(

  select

    a.*,

    row_number() over(partition by name order by concat(update_time,contact_phone) desc nulls last) as rn

  from

  (

    select * from t1

    union all

    select * from t2

    union all

    select * from t3

  )a

)

select name

     , contact_phone

     , update_time

from temp where rn=1

方法2：底表直接过滤掉非法数据，然后使用row_number()函数

SELECT  name,  contact_phone,  update_timeFROM (  SELECT    name,    contact_phone,    update_time,    ROW_NUMBER() OVER (      PARTITION BY name      ORDER BY update_time DESC    ) AS rn  FROM (    -- 合并所有表并过滤空值    SELECT name, contact_phone, update_time    FROM t1    WHERE contact_phone IS NOT NULL    UNION ALL    SELECT name, contact_phone, update_time    FROM t2    WHERE contact_phone IS NOT NULL    UNION ALL    SELECT name, contact_phone, update_time    FROM t3    WHERE contact_phone IS NOT NULL  ) combined_data) ranked_dataWHERE rn = 1;

方法3：通过关联形式求解

with data as (    -- 合并所有表并过滤空值    SELECT name, contact_phone, update_time    FROM t1    WHERE contact_phone IS NOT NULL    UNION ALL    SELECT name, contact_phone, update_time    FROM t2    WHERE contact_phone IS NOT NULL    UNION ALL    SELECT name, contact_phone, update_time    FROM t3    WHERE contact_phone IS NOT NULL)select  t1.name      , t1.contact_phone      , t1.update_timefrom data t1    join (select name, max(update_time) update_timefrom datagroup by name ) t2on t1.name = t2.name and t1.update_time = t2.update_time