SQL查询：用testtable随机值替换Cash字段NULL值

现有某SQL查询返回如下结果，其中Cash字段均为NULL：

|SEDOL |   ISIN      | Cash |
=============================
|ZZZ0072|GB00B7JYLW09| null |
-----------------------------
|CASH   |GB00B7JYLW09| null |
-----------------------------
|ZZZ0072|GB00B7JYLW09| null |
-----------------------------
|ZZZ0009|GB00B7JYLW09| null |
-----------------------------

需要将Cash字段的NULL替换为testtable表中IsCash字段的任意随机值，目标结果示例如下：

|SEDOL |   ISIN      | Cash |
=============================
|ZZZ0072|GB00B7JYLW09|150146|
-----------------------------
|CASH   |GB00B7JYLW09| 182  |
-----------------------------
|ZZZ0072|GB00B7JYLW09| 1190 |
-----------------------------
|ZZZ0009|GB00B7JYLW09| 2000 |
-----------------------------

testtable表结构及数据如下：

|    IsCash  |
==============
|     36     |
--------------
|   150146   |
--------------
|    182     |
--------------
|   2000     |
--------------
|   200952   |
--------------
|   200000   |
--------------
|   350000   |
--------------
|   150000   |
--------------
|    1190    |
--------------

解决方案 #

根据不同数据库类型，提供两种常用实现方式：

方式1：每行随机取独立值（允许重复） #

这种写法会让结果里的每行Cash都随机从testtable中选取一个IsCash值，可能出现重复。

SQL Server

SELECT 
    SEDOL,
    ISIN,
    ISNULL(Cash, (SELECT TOP 1 IsCash FROM testtable ORDER BY NEWID())) AS Cash
FROM 原查询结果表 -- 替换为你的原查询或源表名

MySQL

SELECT 
    SEDOL,
    ISIN,
    IFNULL(Cash, (SELECT IsCash FROM testtable ORDER BY RAND() LIMIT 1)) AS Cash
FROM 原查询结果表 -- 替换为你的原查询或源表名

PostgreSQL

SELECT 
    SEDOL,
    ISIN,
    COALESCE(Cash, (SELECT IsCash FROM testtable ORDER BY RANDOM() LIMIT 1)) AS Cash
FROM 原查询结果表 -- 替换为你的原查询或源表名

方式2：匹配不重复随机值（需源表行数≤testtable行数） #

如果希望每行取不同的IsCash值（前提是原结果表行数不超过testtable的行数），可以通过随机行号关联实现：

SQL Server

SELECT 
    t.SEDOL,
    t.ISIN,
    ISNULL(t.Cash, tt.IsCash) AS Cash
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
    FROM 原查询结果表 -- 替换为你的原查询或源表名
) t
CROSS APPLY (
    SELECT IsCash, ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
    FROM testtable
) tt
WHERE t.rn = tt.rn

MySQL

SELECT 
    t.SEDOL,
    t.ISIN,
    IFNULL(t.Cash, tt.IsCash) AS Cash
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) AS rn
    FROM 原查询结果表 -- 替换为你的原查询或源表名
) t
JOIN (
    SELECT IsCash, ROW_NUMBER() OVER (ORDER BY RAND()) AS rn
    FROM testtable
) tt ON t.rn = tt.rn

PostgreSQL

SELECT 
    t.SEDOL,
    t.ISIN,
    COALESCE(t.Cash, tt.IsCash) AS Cash
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM 原查询结果表 -- 替换为你的原查询或源表名
) t
JOIN (
    SELECT IsCash, ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM testtable
) tt ON t.rn = tt.rn

内容的提问来源于stack exchange，提问作者Patterson