今天改了 bug，看到了一个 sql，顿时惊了。。。。。 这个是新项目，还没上线这部分内容。。

[luxinfl]

SELECT
a.*
FROM
(
SELECT
a.*
FROM
(
SELECT
a.*
FROM
(
SELECT
a.*
FROM
(
SELECT
a.*
FROM
(
SELECT
PLATFORM_REQUEST_CODE AS platformRequestCode,
AGGTEGATE_REQUEST_CODE AS aggtegateRequestCode,
CREATE_TIME AS createTime,
STATUS AS payStatus,
MERCHANT_GENERATE_CODE AS platformCode,
USER_CODE AS buyCode,
CHANNEL_NO AS channelNo
FROM
tb_aggtegate_payment_request
WHERE
STATUS != '00'
AND DEL_FLAG = 0
AND IS_VALID = 0
ORDER BY
CREATE_TIME DESC
) a
LEFT JOIN ( SELECT CHANNEL_REQUEST_CODE AS channelOrderCode, AGGTEGATE_REQUEST_CODE AS channelaggtegateRequestCode FROM tb_channel_send_report WHERE DEL_FLAG = 0 ) b ON a.aggtegateRequestCode = b.channelaggtegateRequestCode
) a
LEFT JOIN ( SELECT MERCHANT_INFO_CODE, MERCHANT_INFO_NAME AS platformName FROM tb_merchant_info WHERE DEL_FLAG = 0 ) c ON c.MERCHANT_INFO_CODE = a.platformCode
) a
LEFT JOIN ( SELECT PLATFORM_USER_CODE, MERCHANT_NAME AS merchantName FROM tb_user_info WHERE DEL_FLAG = 0 ) b ON a.buyCode = b.PLATFORM_USER_CODE
) a
LEFT JOIN ( SELECT CHANNEL_CODE, CHANNEL_NAME AS channelName FROM tb_channel_info WHERE DEL_FLAG = 0 ) b ON a.channelNo = b.CHANNEL_CODE
) a
LEFT JOIN ( SELECT AGGTEGATE_REQUEST_CODE, TIME_END AS payTime FROM tb_wxpay_order_business WHERE DEL_FLAG = 0 AND IS_VALID = 0 ) b ON a.aggtegateRequestCode = b.AGGTEGATE_REQUEST_CODE
WHERE
1 = 1

[luxinfl]

@vxlol 没见识过。我感觉正经线上的项目，都不会出现这么多的连接

[luxinfl]

@xxlee 其实不太一样，多了一些字段，有三个表是为了用 code 查 name

[magicdu]

太年轻了，见过格式化都格式化不出来的 SQL 吗

[luxinfl]

@Xusually 这样子执行效率不是很低么

[luxinfl]

@magicdu 我是说本站文本框的格式化不会搞，搞不好

[leonardyang]

@luxinfl 没看懂你的想法，所谓的匹配塞值不还是要遍历数据吗，莫非你是觉得自己用代码实现的“left join”比现代数据库的查询优化强？互联网公司要求少用 join 是用冗余字段实现的，不是靠在代码里再遍历一遍这种自欺欺人的方式。。。

[Jrue0011]

看起来中间有几个表目的是为了获取 xxx_name 给前端用于显示，如果在 name 允许用户修改即会改变的情况下，有什么好的做法吗。。。

[nutting]

sql 就这德性，很恶心的

[winglight2016]

这种子查询嵌套，我们一般用来人工减慢响应时间的，方便以后优化。。。

哈哈，开玩笑了，不过，子查询是需要尽量避免的吧，实在不行搞个动态视图也好呀。

[Xusually]

@luxinfl 光你贴出来的这个 sql 看没有明显的问题。
执行效率高还是低，肯定还是得看表结构以及索引的安排啊。
你可以实际执行一下，并且也查看一下这条 sql 在 db 的执行计划，看看情况。
光看 sql 本身没办法看出来效率有多低

[est]

这么多人点赞。怕了怕了。 😂

[x66]

我觉得还好吧，做互联网业务的人跟做 ERP/银行 /保险业务的人平常遇到到的业务复杂度不是同一个级别的。

[LuciferGo]

真这么写吧，要是每个关联都是命中索引问题倒也不大，丑是丑了点

[594duck]

@laminux29 你这样喷 DBA 良心不会痛么？ DBA 是受过专业训练的好么

[Alexisused]

之前用的报表系统一个报表只能写一个 sql, 最大的那个报表 SQL 写出来压缩之后还有 8K 字符，varchar2 存不下，后来没办法分成了 2 个报表..

[zypy333]

看到长 sql 就头大,楼主能贴出来优化后的 sql 吗

[liprais]

要喷也要看了执行计划做了 profile 再喷
阿里那手册看看就算了,毕竟 mysql 就那样

[ReinerShir]

一般是业务复杂，开发时间少，而且对性能没啥要求的后台功能才这么写

[markyangd]

见过 3000 行 sp 的人表示，你这个 sql 语句算小菜。

[WytheHuang]

这样格式化，能看了~
SELECT
a.*
FROM
(
SELECT
a.*
FROM
(
SELECT
a.*
FROM
(
SELECT
a.*
FROM
(
SELECT
a.*
FROM
(
SELECT
PLATFORM_REQUEST_CODE AS platformRequestCode,
AGGTEGATE_REQUEST_CODE AS aggtegateRequestCode,
CREATE_TIME AS createTime,
STATUS AS payStatus,
MERCHANT_GENERATE_CODE AS platformCode,
USER_CODE AS buyCode,
CHANNEL_NO AS channelNo
FROM
tb_aggtegate_payment_request
WHERE
STATUS != '00'
AND DEL_FLAG = 0
AND IS_VALID = 0
ORDER BY
CREATE_TIME DESC
) a
LEFT JOIN ( SELECT CHANNEL_REQUEST_CODE AS channelOrderCode, AGGTEGATE_REQUEST_CODE AS channelaggtegateRequestCode FROM tb_channel_send_report WHERE DEL_FLAG = 0 ) b ON a.aggtegateRequestCode = b.channelaggtegateRequestCode
) a
LEFT JOIN ( SELECT MERCHANT_INFO_CODE, MERCHANT_INFO_NAME AS platformName FROM tb_merchant_info WHERE DEL_FLAG = 0 ) c ON c.MERCHANT_INFO_CODE = a.platformCode
) a
LEFT JOIN ( SELECT PLATFORM_USER_CODE, MERCHANT_NAME AS merchantName FROM tb_user_info WHERE DEL_FLAG = 0 ) b ON a.buyCode = b.PLATFORM_USER_CODE
) a
LEFT JOIN ( SELECT CHANNEL_CODE, CHANNEL_NAME AS channelName FROM tb_channel_info WHERE DEL_FLAG = 0 ) b ON a.channelNo = b.CHANNEL_CODE
) a
LEFT JOIN ( SELECT AGGTEGATE_REQUEST_CODE, TIME_END AS payTime FROM tb_wxpay_order_business WHERE DEL_FLAG = 0 AND IS_VALID = 0 ) b ON a.aggtegateRequestCode = b.AGGTEGATE_REQUEST_CODE
WHERE
1 = 1