如何将同一CODE对应的两条记录合并为带序号后缀字段的单条查询结果

假设你的原始数据表名为ticker_data，以下提供两种主流场景的查询写法：

支持窗口函数的数据库方案（MySQL8.0+、PostgreSQL、Oracle等通用） #

-- 先给同CODE分组下的每条记录按TICKER排序加行号
WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY TICKER) AS row_num
    FROM ticker_data
)
-- 关联同CODE下的第1、2条记录，拼接为单行
SELECT 
    a.CODE,
    a.TICKER AS TICKER_1,
    a.SCORE AS SCORE_1,
    a.PRICE AS PRICE_1,
    a.PCF AS PCF_1,
    b.TICKER AS TICKER_2,
    b.SCORE AS SCORE_2,
    b.PRICE AS PRICE_2,
    b.PCF AS PCF_2
FROM ranked_data a
INNER JOIN ranked_data b 
    ON a.CODE = b.CODE 
    AND a.row_num = 1 
    AND b.row_num = 2;

低版本MySQL（不支持窗口函数）适配方案 #

SELECT 
    CODE,
    SUBSTRING_INDEX(GROUP_CONCAT(TICKER ORDER BY TICKER), ',', 1) AS TICKER_1,
    SUBSTRING_INDEX(GROUP_CONCAT(SCORE ORDER BY TICKER), ',', 1) AS SCORE_1,
    SUBSTRING_INDEX(GROUP_CONCAT(PRICE ORDER BY TICKER), ',', 1) + 0 AS PRICE_1,
    SUBSTRING_INDEX(GROUP_CONCAT(PCF ORDER BY TICKER), ',', 1) + 0 AS PCF_1,
    SUBSTRING_INDEX(GROUP_CONCAT(TICKER ORDER BY TICKER), ',', -1) AS TICKER_2,
    SUBSTRING_INDEX(GROUP_CONCAT(SCORE ORDER BY TICKER), ',', -1) AS SCORE_2,
    SUBSTRING_INDEX(GROUP_CONCAT(PRICE ORDER BY TICKER), ',', -1) + 0 AS PRICE_2,
    SUBSTRING_INDEX(GROUP_CONCAT(PCF ORDER BY TICKER), ',', -1) + 0 AS PCF_2
FROM ticker_data
GROUP BY CODE;

如果需要直接导出为CSV格式，在对应数据库的查询语句中追加CSV导出相关参数即可。

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