SQL优化（DB2）:表A多字段关联表B唯一字段，查询优化

SELECT
RULE_ID,
(select distinct m.GROUP_NAME from TBL_GRP m where m.GROUP_ID = n.GROUP_ID ) AS GROUP_ID,
SCORE_PARAM,
CASH_PARAM,
(select distinct m.GROUP_NAME from TBL__GRP m where m.= n.GROUP_ID2 ) AS GROUP_ID2,
SCORE_PARAM2,
CASH_PARAM2,
(select distinct m. GROUP_NAME from TBL_GRP m where m.GROUP_ID = n.GROUP_ID3 ) AS GROUP_ID3,
SCORE_PARAM3,
CASH_PARAM3,
(select distinct m.GROUP_NAME from TBL_GRP m where m.GROUP_ID = n.GROUP_ID4 ) AS GROUP_ID4,
SCORE_PARAM4,
CASH_PARAM4,
(select distinct m.GROUP_NAME from TBL_GRP m where m.GROUP_ID = n.GROUP_ID5 ) AS GROUP_ID5,
SCORE_PARAM5,
CASH_PARAM5
FROM
TBL_RULE n

楼主：我的建议是创建第三张表，然后在实时记录时，在第三张表中进行统计

不会的，第三张表只记录统计的数据 ，在产生记录的时候，同时向第三张表中+1，在查询统计数据的时候，查询第三张表就可以。

首先问一句，你这段代码不会报错吗？
那我按照伪代码处理了，主要是思路。首先发现你的代码5次使用了： SELECT DISTINCT M.GROUP_NAME FROM TBL__GRP M。也就是说每次查询一条数据，你就会执行这条sql 5次。优化思路就是把这段代码抽取出来，放到一个临时表中，这样子使用的时候就不会多次查询表。 改写如下：

WITH TMP AS
 (SELECT DISTINCT M.GROUP_NAME FROM TBL__GRP)

SELECT RULE_ID,
       (SELECT * FROM TMP M
         WHERE M.GROUP_ID = N.GROUP_ID) AS GROUP_ID,
       SCORE_PARAM,
       CASH_PARAM,
       (SELECT * FROM TMP M WHERE M. = N.GROUP_ID2) AS GROUP_ID2,
       SCORE_PARAM2,
       CASH_PARAM2,
       (SELECT * FROM TMP
         WHERE M.GROUP_ID = N.GROUP_ID3) AS GROUP_ID3,
       SCORE_PARAM3,
       CASH_PARAM3,
       (SELECT * FROM TMP
         WHERE M.GROUP_ID = N.GROUP_ID4) AS GROUP_ID4,
       SCORE_PARAM4,
       CASH_PARAM4,
       (SELECT * FROM TMP
         WHERE M.GROUP_ID = N.GROUP_ID5) AS GROUP_ID5,
       SCORE_PARAM5,
       CASH_PARAM5
  FROM TBL_RULE N