如何在SQL中结合复合主键实现ACCOUNTS与AMOUNTS表的关联查询

需求说明 #

现有两张数据表：
ACCOUNTS表（复合主键：BANK_ID、BRANCH_ID、ACCOUNT_NUM）：

AMOUNTS表（主键：ACCOUNT_REC，值为空格分隔的三个主键字段组合）：

需要将两表合并为包含BANK_ID、BRANCH_ID、ACCOUNT_NUM、CURRENCY、AMOUNT的结果表。

实现方法 #

核心思路是拆分AMOUNTS.ACCOUNT_REC中的三个字段值，再与ACCOUNTS表的复合主键关联，以下是主流数据库的具体写法：

1. MySQL（8.0+） #

利用SUBSTRING_INDEX函数拆分空格分隔的字符串：

SELECT 
    a.BANK_ID,
    a.BRANCH_ID,
    a.ACCOUNT_NUM,
    a.CURRENCY,
    m.AMOUNT
FROM ACCOUNTS a
JOIN AMOUNTS m ON 
    a.BANK_ID = SUBSTRING_INDEX(m.ACCOUNT_REC, ' ', 1)
    AND a.BRANCH_ID = SUBSTRING_INDEX(SUBSTRING_INDEX(m.ACCOUNT_REC, ' ', 2), ' ', -1)
    AND a.ACCOUNT_NUM = SUBSTRING_INDEX(m.ACCOUNT_REC, ' ', -1);

2. PostgreSQL #

使用STRING_TO_ARRAY将字符串转为数组，再取对应位置的元素：

SELECT 
    a.BANK_ID,
    a.BRANCH_ID,
    a.ACCOUNT_NUM,
    a.CURRENCY,
    m.AMOUNT
FROM ACCOUNTS a
JOIN AMOUNTS m ON 
    a.BANK_ID = (STRING_TO_ARRAY(m.ACCOUNT_REC, ' '))[1]::INT
    AND a.BRANCH_ID = (STRING_TO_ARRAY(m.ACCOUNT_REC, ' '))[2]::INT
    AND a.ACCOUNT_NUM = (STRING_TO_ARRAY(m.ACCOUNT_REC, ' '))[3]::INT;

注：根据字段实际类型调整::INT的类型转换，若为字符串类型可去掉转换。

3. SQL Server #

方法一：STRING_SPLIT + ROW_NUMBER（2016+）

WITH split_account AS (
    SELECT 
        ACCOUNT_REC,
        AMOUNT,
        value,
        ROW_NUMBER() OVER (PARTITION BY ACCOUNT_REC ORDER BY (SELECT NULL)) AS rn
    FROM AMOUNTS
    CROSS APPLY STRING_SPLIT(ACCOUNT_REC, ' ')
)
SELECT 
    a.BANK_ID,
    a.BRANCH_ID,
    a.ACCOUNT_NUM,
    a.CURRENCY,
    s.AMOUNT
FROM ACCOUNTS a
JOIN (
    SELECT 
        ACCOUNT_REC,
        AMOUNT,
        MAX(CASE WHEN rn=1 THEN value END) AS BANK_ID,
        MAX(CASE WHEN rn=2 THEN value END) AS BRANCH_ID,
        MAX(CASE WHEN rn=3 THEN value END) AS ACCOUNT_NUM
    FROM split_account
    GROUP BY ACCOUNT_REC, AMOUNT
) s ON 
    a.BANK_ID = s.BANK_ID
    AND a.BRANCH_ID = s.BRANCH_ID
    AND a.ACCOUNT_NUM = s.ACCOUNT_NUM;

方法二：SUBSTRING + CHARINDEX

SELECT 
    a.BANK_ID,
    a.BRANCH_ID,
    a.ACCOUNT_NUM,
    a.CURRENCY,
    m.AMOUNT
FROM ACCOUNTS a
JOIN AMOUNTS m ON 
    a.BANK_ID = SUBSTRING(m.ACCOUNT_REC, 1, CHARINDEX(' ', m.ACCOUNT_REC)-1)
    AND a.BRANCH_ID = SUBSTRING(
        m.ACCOUNT_REC, 
        CHARINDEX(' ', m.ACCOUNT_REC)+1, 
        CHARINDEX(' ', m.ACCOUNT_REC, CHARINDEX(' ', m.ACCOUNT_REC)+1) - CHARINDEX(' ', m.ACCOUNT_REC)-1
    )
    AND a.ACCOUNT_NUM = SUBSTRING(m.ACCOUNT_REC, CHARINDEX(' ', m.ACCOUNT_REC, CHARINDEX(' ', m.ACCOUNT_REC)+1)+1, LEN(m.ACCOUNT_REC));

4. 通用优化建议 #

如果数据量较大，建议修改AMOUNTS表结构，将ACCOUNT_REC拆分为BANK_ID、BRANCH_ID、ACCOUNT_NUM三个单独字段并建立外键关联，可大幅提升查询效率，避免每次查询都执行字符串拆分操作。

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