SQL行转列合并同时间戳多行数据及inner join语法报错求解

1. 报错直接原因 #

SQL语法规定单条查询语句仅允许存在1个WHERE子句，你代码中连续写了3个独立的WHERE，触发语法校验错误。多个过滤条件需要用AND关键字拼接。同时你原有逻辑存在字段筛选错误：你需要匹配的是MODELID等于属性名，而非V字段等于属性名，V是属性对应的存储值。

2. 需求实现方案 #

方案1：修正JOIN写法（适配你原有逻辑） #

使用关联方式实现时，建议用LEFT JOIN替代INNER JOIN，避免某个时间戳T下缺失某类MODELID记录时，整行数据被过滤掉，完整代码如下：

SELECT 
    t_base.T,
    t_lot.V AS LotNumber,
    t_good.V AS GoodStrips,
    t_sproc.V AS StripProc,
    t_sraw.V AS StripRaw,
    t_lproc.V AS LabelProc,
    t_lraw.V AS LabelRaw,
    t_stip.V AS SmallTips,
    t_ltip.V AS LongTips
FROM (SELECT DISTINCT T FROM Rejects945) t_base
LEFT JOIN Rejects945 t_lot ON t_lot.T = t_base.T AND t_lot.MODELID = 'LotNumber'
LEFT JOIN Rejects945 t_good ON t_good.T = t_base.T AND t_good.MODELID = 'GoodStrips'
LEFT JOIN Rejects945 t_sproc ON t_sproc.T = t_base.T AND t_sproc.MODELID = 'StripProc'
LEFT JOIN Rejects945 t_sraw ON t_sraw.T = t_base.T AND t_sraw.MODELID = 'StripRaw'
LEFT JOIN Rejects945 t_lproc ON t_lproc.T = t_base.T AND t_lproc.MODELID = 'LabelProc'
LEFT JOIN Rejects945 t_lraw ON t_lraw.T = t_base.T AND t_lraw.MODELID = 'LabelRaw'
LEFT JOIN Rejects945 t_stip ON t_stip.T = t_base.T AND t_stip.MODELID = 'SmallTips'
LEFT JOIN Rejects945 t_ltip ON t_ltip.T = t_base.T AND t_ltip.MODELID = 'LongTips'

方案2：聚合行转列写法（更高效推荐） #

不需要多次关联表，仅需一次全表扫描即可完成转置，性能远高于多表JOIN，代码更简洁：

SELECT 
    T,
    MAX(CASE WHEN MODELID = 'LotNumber' THEN V END) AS LotNumber,
    MAX(CASE WHEN MODELID = 'GoodStrips' THEN V END) AS GoodStrips,
    MAX(CASE WHEN MODELID = 'StripProc' THEN V END) AS StripProc,
    MAX(CASE WHEN MODELID = 'StripRaw' THEN V END) AS StripRaw,
    MAX(CASE WHEN MODELID = 'LabelProc' THEN V END) AS LabelProc,
    MAX(CASE WHEN MODELID = 'LabelRaw' THEN V END) AS LabelRaw,
    MAX(CASE WHEN MODELID = 'SmallTips' THEN V END) AS SmallTips,
    MAX(CASE WHEN MODELID = 'LongTips' THEN V END) AS LongTips
FROM Rejects945
GROUP BY T

如果你的数据库支持PIVOT语法，也可以用PIVOT函数实现相同效果。

3. 视图/新表生成 #

如果需要生成视图，直接在上述正确的SELECT语句前加上CREATE VIEW 自定义视图名 AS即可；如果需要生成新表，加上INTO 自定义新表名放在FROM关键字之前即可。

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