SQL(列转行)优化

nuoboxgx nuoboxgx 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
表大概100万数据

由以下的SQL Union而成
实际执行时耗时5分钟以上
及其烦恼
高人帮着优化一下啊?

是不是可以把公共字段提出来不用每次都Union啊 (公共字段F29,F1,F2,F7,F31,F3,F4,F8)


SELECT F29,F1,F2,F7,F31,F3,F4,F8,F9 AS UF9,F19 AS UF19 FROM DMSRC.clhsg2 WHERE (F31='02' OR F31 IS NULL)
UNION ALL
SELECT F29,F1,F2,F7,F31,F3,F4,F8,F10 AS UF9,F20 AS UF19 FROM DMSRC.clhsg2 WHERE (F31='02' OR F31 IS NULL)
UNION ALL
SELECT F29,F1,F2,F7,F31,F3,F4,F8,F11 AS UF9,F21 AS UF19 FROM DMSRC.clhsg2 WHERE (F31='02' OR F31 IS NULL)
UNION ALL
SELECT F29,F1,F2,F7,F31,F3,F4,F8,F12 AS UF9,F22 AS UF19 FROM DMSRC.clhsg2 WHERE (F31='02' OR F31 IS NULL)
UNION ALL
SELECT F29,F1,F2,F7,F31,F3,F4,F8,F13 AS UF9,F23 AS UF19 FROM DMSRC.clhsg2 WHERE (F31='02' OR F31 IS NULL)
UNION ALL
SELECT F29,F1,F2,F7,F31,F3,F4,F8,F14 AS UF9,F24 AS UF19 FROM DMSRC.clhsg2 WHERE (F31='02' OR F31 IS NULL)
UNION ALL
SELECT F29,F1,F2,F7,F31,F3,F4,F8,F15 AS UF9,F25 AS UF19 FROM DMSRC.clhsg2 WHERE (F31='02' OR F31 IS NULL)
UNION ALL
SELECT F29,F1,F2,F7,F31,F3,F4,F8,F16 AS UF9,F26 AS UF19 FROM DMSRC.clhsg2 WHERE (F31='02' OR F31 IS NULL)
UNION ALL
SELECT F29,F1,F2,F7,F31,F3,F4,F8,F17 AS UF9,F27 AS UF19 FROM DMSRC.clhsg2 WHERE (F31='02' OR F31 IS NULL)
UNION ALL
SELECT F29,F1,F2,F7,F31,F3,F4,F8,F18 AS UF9,F28 AS UF19 FROM DMSRC.clhsg2 WHERE (F31='02' OR F31 IS NULL)
...全文

相关推荐

评论 3
收藏 已收藏
分享
返回顶部
nuoboxgx
DB2 5,886 成员 11,654 内容