使用 Oracle 和 SUM 查询联接 2 个表

Question

我有 2 个查询

问1：

SELECT tc_ghy07,tc_ghy12
FROM tc_ghy_file
WHERE tc_ghy02 = 'DNF-000414'
      AND tc_ghy01=TO_CHAR(YEAR(CURRENT_DATE)) 

结果：

+----------+----------+
| TC_GHY07 | TC_GHY12 |
+----------+----------+
|       16 |        0 |
+----------+----------+

问2：

SELECT ( SUM(DECODE(tc_soa38,NULL,0,tc_soa38)+ decode(tc_soa39,NULL,0,tc_soa39*1.5) 
+ DECODE(tc_soa40,NULL,0,tc_soa40 * 1.6) + decode(tc_soa41,NULL, 0,tc_soa41 * 2) +  
    decode(tc_soa42,NULL,0,tc_soa42
* 2.1) + DECODE(tc_soa43, NULL, 0,tc_soa43 * 2.7) 
+ DECODE(tc_soa44,NULL, 0,tc_soa44 * 3) + DECODE(tc_soa45, NULL,0,tc_soa45 *
3.9)) ) / 8 AS result
FROM tc_soa_file
WHERE tc_soa33 = 'BU'
      AND tc_soa04 = 'DNF-000414'
      AND year(tc_soa07) = year(current_date);

结果：

+--------+
| RESULT |
+--------+
|     20 |
+--------+

如何将这两个表合并为一个查询？

我已经尝试过全部联合或交叉加入，但这是不对的。感谢您的帮助

我想在一个查询中查询 2 个这样的查询，结果会像这样

+-----------+----------+--------+
|  TC_GHY07 | TC_GHY12 | RESULT |
+-----------+----------+--------+
|        16 |        0 |     20 |
+-----------+----------+--------+

编辑：连接条件为tc_ghy02=tc_soa04

Answer 1

您可以尝试使用此查询

SELECT a.tc_ghy07,
       a.tc_ghy12,
       r.result
  FROM tc_ghy_file a
 INNER JOIN (select b.tc_soa04,
                    (SUM(DECODE(b.tc_soa38, NULL, 0, b.tc_soa38) +
                         decode(b.tc_soa39, NULL, 0, b.tc_soa39 * 1.5) +
                         DECODE(b.tc_soa40, NULL, 0, b.tc_soa40 * 1.6) +
                         decode(b.tc_soa41, NULL, 0, b.tc_soa41 * 2) +
                         decode(b.tc_soa42, NULL, 0, b.tc_soa42 * 2.1) +
                         DECODE(b.tc_soa43, NULL, 0, b.tc_soa43 * 2.7) +
                         DECODE(b.tc_soa44, NULL, 0, b.tc_soa44 * 3) +
                         DECODE(b.tc_soa45, NULL, 0, b.tc_soa45 * 3.9))) / 8 AS result 
             from tc_soa_file b
             where b.tc_soa33 = 'BU'
                   and year(b.tc_soa07) = year(current_date)
             Group by tc_soa04
             ) r on r.tc_soa04 = a.tc_ghy02
 WHERE a.tc_ghy02 = 'DNF-000414'
   AND a.tc_ghy01 = TO_CHAR(YEAR(CURRENT_DATE))