今天改了 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

[xhf1024]

请问你们是基于自己的快速平台开发的嘛

[kiracyan]

看着恐怖而已 逻辑挺简单的

[protectione055]

@leonardyang ？？原来学校教的那套范式都没用的吗？刚被数据库这套东西折磨完

[lwlizhe]

我是个客户端开发，接触的数据库查询没到这个量上

但是，之前有时被拉去开后端事故总结会的时候，时常听到子查询导致的种种慢查询……（我个人感觉子查询本身没啥问题，应该归属于子查询的查询索引问题吧，不知道这么说对不对）

记得后端老大还总结了一下数据库查询的几个注意点……可惜现在都忘了……

[hello826]

这个代码评审能过吗

[xxlee]

@luxinfl 你再好好看看，left join 没有 where 条件，是会保留左侧子查询的所有数据的

[KasonPasser]

@loading #7
拼接 sql 其实可以写得再简单点 1 就可以了。
不过还是看着感觉不好。

[loading]

@KasonPasser 用 1=1 可以满足洁癖在最后替换掉，你用 1 不好做。

[xemtof]

@soulzz 我们之前公司的支付部分逻辑全部在数据库写的存储过程做的。

[Qusic]

这叫为未来预留优化空间（

[angryfish]

@luxinfl #11 不要被阿里什么规范洗脑，适合自己业务的才是最好的。

[rockyou12]

虽然这个 sql 本身就很屎，但 mysql 不支持 with 让这个 sql 更屎了。希望大家以后都不要再用 mysql，pg 真的好用很多……

[gouflv]

@est 有画面感了

[rainysia]

以前有个 oracle 的师傅...不写代码， 就直接写 SQL 给我。 大概平均一个 select 从开始到结束有个几千行吧。 中间各种生成， 变换， 计算， 叠加。

跟着写了两年。 各种黑魔法学的盆满钵满

[justin2018]


[NoString]

哈哈哈 这种在我们组大概会被枪毙

[levelworm]

话说如果是 BI 的话可以考虑用 dbt 这个工具

[cozof]

以前写存储过程或者看别人的存储过程很多都是上百行，不过这个 abcd 的表别名不怎么样。

[collery]

hive sql 经常这么写。连表查

[lovecy]

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