124124

最新推荐文章于 2024-10-29 16:13:39 发布

原创最新推荐文章于 2024-10-29 16:13:39 发布 · 230 阅读

0 ·

CC 4.0 BY-SA版权

本文深入解析了一段复杂的SQL查询语句，详细介绍了如何从多个表中联接并汇总数据，包括使用子查询、聚合函数和条件表达式来计算合同总额的具体实现方法。

SELECT
  table01.CONTRACT_NO,
  ( table01.cpcauto+table03.extaum) AS TOTAL_AMOUNT
FROM (SELECT
        tc01.CONTRACT_NO,
        tc01.CONTRACT_ID,
        (SELECT
           SUM(PRICE*CNUMBER)
         FROM contract_product_c pc01
         WHERE pc01.CONTRACT_ID = tc01.CONTRACT_ID) AS cpcauto
      FROM contract_c tc01) AS table01,
  (SELECT
     table02.CONTRACT_ID,
     IFNULL(SUM(extaum),'0') AS extaum
   FROM (SELECT
           cpc02.CONTRACT_ID,
           (SELECT
              SUM(PRICE*CNUMBER)
            FROM ext_cproduct_c et02
            WHERE et02.CONTRACT_PRODUCT_ID = cpc02.CONTRACT_PRODUCT_ID) AS extaum
         FROM contract_product_c cpc02
         GROUP BY cpc02.CONTRACT_PRODUCT_ID) AS table02
   GROUP BY table02.CONTRACT_ID) AS table03
WHERE table01.CONTRACT_ID = table03.CONTRACT_ID