如何从SQL两表查询结果中按LINENO提取LINEDATA内的指定字段值

解决方案 #

思路说明 #

你的需求属于典型的行转列场景，可直接通过SQL查询实现，也可以在业务代码中遍历处理，两种方案如下：

方案1：SQL直接查询实现 #

首先先修正你原SQL的笔误：你原查询中未定义别名D，所有D的引用应该替换为表BILL.MESSAGE的别名B，且ACTIVITYAS缺少空格，修正为ACTIVITY AS。

核心逻辑是通过CASE语句按LINENO分支提取对应字段值，再用聚合函数按MESSAGENO分组，将5行数据聚合为1行。
以下以MySQL为例的实现代码：

SELECT 
  B.MESSAGENO,
  -- 提取LINENO=1的支票号
  MAX(CASE WHEN B.LINENO = 1 THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(B.LINEDATA, 'CHEQUE NO  : ', -1), ' ', 1)) END) AS checkno,
  -- 提取LINENO=2的金额
  MAX(CASE WHEN B.LINENO = 2 THEN TRIM(SUBSTRING_INDEX(B.LINEDATA, 'AMOUNT    : ', -1)) END) AS amount,
  -- 提取LINENO=3的收款人
  MAX(CASE WHEN B.LINENO = 3 THEN TRIM(SUBSTRING_INDEX(B.LINEDATA, 'PAYEE      : ', -1)) END) AS payee,
  -- 提取LINENO=4的账号
  MAX(CASE WHEN B.LINENO = 4 THEN TRIM(SUBSTRING_INDEX(B.LINEDATA, 'ACCOUNT NO : ', -1)) END) AS 账号,
  -- 提取LINENO=5的用户名
  MAX(CASE WHEN B.LINENO = 5 THEN TRIM(SUBSTRING_INDEX(B.LINEDATA, 'USER       : ', -1)) END) AS 用户名
FROM BILL.MESSAGE AS B, BILL.ACTIVITY AS A
WHERE A.MSGNO = B.MESSAGENO 
  AND A.FUPTEAM = 'DBWB'
  AND A.ACTIVITY = 'STOPPAY' 
  AND A.STATUS = 'WAIT'
  AND A.COMPANY = B.COMPANY
GROUP BY B.MESSAGENO;

如果是其他数据库，只需要调整字符串提取函数即可：

• Oracle：替换SUBSTRING_INDEX为SUBSTR+INSTR组合
• SQL Server：替换SUBSTRING_INDEX为SUBSTRING+CHARINDEX组合

方案2：业务代码处理 #

如果更习惯在代码中处理逻辑，可先将原始查询结果按MESSAGENO分组，每组对应5条记录，遍历每条记录按LINENO提取对应值即可。以Python为例：

import pandas as pd

# 假设df是原始SQL查询返回的DataFrame
result = []
for msg_no, group in df.groupby('MESSAGENO'):
    item = {'MESSAGENO': msg_no}
    for _, row in group.iterrows():
        lineno = row['LINENO']
        linedata = row['LINEDATA']
        if lineno == 1:
            item['checkno'] = linedata.split('CHEQUE NO  : ')[1].split()[0].strip()
        elif lineno == 2:
            item['amount'] = linedata.split('AMOUNT    : ')[1].strip()
        elif lineno == 3:
            item['payee'] = linedata.split('PAYEE      : ')[1].strip()
        elif lineno == 4:
            item['账号'] = linedata.split('ACCOUNT NO : ')[1].strip()
        elif lineno == 5:
            item['用户名'] = linedata.split('USER       : ')[1].strip()
    result.append(item)

# 最终result就是拼接好的完整记录列表
final_df = pd.DataFrame(result)

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