IT博客汇 | 【联想实习笔记】如何优雅地处理数据表中一对多的重复记录

【联想实习笔记】如何优雅地处理数据表中一对多的重复记录

Pil0tXia发表于 2023-06-09 06:34:50

语句已做脱敏处理。方言基于 SAP HANA。

如果数据库表中存在一对多的记录，这段 SQL 会报错 SAP DBTech JDBC: [305]: single-row query returns more than one row。

SELECT
(
SELECT
ID
FROM
SYS_MENU smps
WHERE
NAME = 'My Report Name'
AND HREF = '/path/to/my/report'
) AS MENU_ID,
3 AS "TYPE",
(
SELECT
ID
FROM
SYS_USER sus
WHERE
USER_NAME = 'myUsername'
) AS OWNER
FROM
DUMMY;

用 LIMIT 提取第一行

如果我们只需要第一条记录，一对多的其它数据都无所谓，只需要在子查询中加上 LIMIT 1 即可。

SELECT
(
SELECT
ID
FROM
SYS_MENU smps
WHERE
NAME = 'My Report Name'
AND HREF = '/path/to/my/report'
    LIMIT 1) AS MENU_ID,
3 AS "TYPE",
(
SELECT
ID
FROM
SYS_USER sus
WHERE
USER_NAME = 'myUsername'
LIMIT 1) AS OWNER
FROM
DUMMY;

用 ROW_NUMBER () 提取任意行

如果是我们需要对多行结果集中的每一条单条记录分别进行处理的场景，可以使用 ROW_NUMBER() 和 OVER() 窗口函数分步处理。

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS row_num
  FROM your_table
) AS subquery
WHERE row_num = 1;

但这往往没有 Java 或 Mybatis 中的 foreach 更方便。

用 LEFT JOIN 替代子查询

对于数据库表中有一对多的记录，而我们需要一次性对所有数据进行处理的场景，要想办法避免无法接受多行结果集的函数或用法。

修改前，子查询是在一个列的上下文中执行的，并且被用作列表达式的一部分。在这种情况下，每个子查询必须返回单个值，因为它们被设计为用于标量子查询（scalar subquery）。如果子查询返回多个行，那么在这个位置上就无法解析这个子查询，因为每个行都需要一个值来填充 MENU_ID 这一列。换句话说，子查询应该返回一个标量值（单个值），而不是一个结果集（多个行）。

修改后，即使表中有一对多的记录，也可以将所有记录统一返回，而不是报错。

SELECT
smps.ID AS MENU_ID,
3 AS "TYPE",
sus.ID AS OWNER
FROM
SYS_MENU smps
LEFT JOIN SYS_USER sus ON
sus.USER_NAME = 'myUsername'
WHERE
smps.NAME = 'My Report Name'
AND smps.HREF = '/path/to/my/report';

然而，我需要进一步的为 smps.ID 和 sus.ID 添加 COALESCE 函数，当 smps.ID 或 sus.ID 为 null 时，可以使用 USER_NAME 或 HREF 作为替代值。使用 LEFT JOIN 后，当右侧条件 WHERE LOWER(USER_NAME) = LOWER('myUsername') 匹配不到结果时，sus_ID 可以显示替代值，当左侧条件 WHERE NAME = 'My Report Name' 在表中没有任何匹配结果时，整个查询只会返回空结果集，就无法用 COALESCE 函数对 MENU_ID 插入替代值了。即使使用 FULL JOIN 或笛卡尔积 CROSS JOIN 也无法解决任何一边的结果集为空的情况。

用 UNION ALL 确保不返回空

为了解决这个问题，可以用 LEFT JOIN 将两个子查询连接在一起，并且 ON 条件始终为 true。然后在每个子查询的末尾添加一个 UNION ALL 子句，如果没有检查到匹配结果，就会通过 DUMMY 表添加一行带有 NULL 值的记录。这样即可确保即使 URL 没有匹配结果，仍然会返回带有替代值的结果。

SELECT
    COALESCE(TO_CHAR(smps.ID), '/path/to/my/report') AS MENU_ID,
    COALESCE(TO_CHAR(sus.ID), 'myUsername') AS OWNER
FROM
    (SELECT ID
    FROM SYS_MENU
    WHERE NAME = 'My Report Name'
        AND HREF = '/path/to/my/report'
    UNION ALL
    SELECT NULL AS ID
    FROM DUMMY
    WHERE NOT EXISTS (
        SELECT ID
        FROM SYS_MENU
        WHERE NAME = 'My Report Name'
            AND HREF = '/path/to/my/report'
    )) AS smps
LEFT JOIN
    (SELECT ID
    FROM SYS_USER
    WHERE LOWER(USER_NAME) = LOWER('myUsername')
    UNION ALL
    SELECT NULL AS ID
    FROM DUMMY
    WHERE NOT EXISTS (
        SELECT ID
        FROM SYS_USER
        WHERE LOWER(USER_NAME) = LOWER('myUsername')
    )) AS sus
ON 1 = 1;

最终我使用的 SQL 语句如下，再结合 Excel 公式和 SQL 转义，它完美地帮我将一份报表名称与URL重复/不对应、用户名不准确/大小写混淆、存在隐性的/无法匹配的Unicode字符的 xlsx 表格维护的 MENU 与 USER 的关系导入到了数据库中：上万数据一遍跑通，新数据更新旧数据，哪里匹配不上就留下问题定位信息，一对多记录也一并插入，还可以重复导入，最后手工纠正一下替代值，甚是畅快。

MERGE
INTO
SYS_MENU_USER AS target
USING (
SELECT
COALESCE(TO_CHAR(smps.ID),
'/path/to/my/report') AS MENU_ID,
3 AS "TYPE",
COALESCE(TO_CHAR(sus.ID),
'myUsername') AS OWNER
FROM
(
SELECT
ID
FROM
SYS_MENU
WHERE
NAME = 'My Report Name'
AND HREF = '/path/to/my/report'
UNION ALL
SELECT
NULL AS ID
FROM
DUMMY
WHERE
NOT EXISTS (
SELECT
ID
FROM
SYS_MENU
WHERE
NAME = 'My Report Name'
AND HREF = '/path/to/my/report'
    )) AS smps
LEFT JOIN
    (
SELECT
ID
FROM
SYS_USER
WHERE
LOWER(USER_NAME) = LOWER('myUsername')
UNION ALL
SELECT
NULL AS ID
FROM
DUMMY
WHERE
NOT EXISTS (
SELECT
ID
FROM
SYS_USER
WHERE
LOWER(USER_NAME) = LOWER('myUsername')
    )) AS sus
ON
1 = 1) AS SOURCE
ON
target.MENU_ID = source.MENU_ID
AND target."TYPE" = source."TYPE"
WHEN MATCHED THEN
    UPDATE
SET
OWNER = source.OWNER
WHEN NOT MATCHED THEN
INSERT
(MENU_ID,
"TYPE",
OWNER)
VALUES (source.MENU_ID,
source."TYPE",
source.OWNER);