代码之家 › 专栏 › 技术社区 › spinjector

同一个Select语句中的其他子查询是否可以访问嵌套的SQL子查询。。?

db2-400 db2 sql

spinjector · 技术社区 · 7 年前

我不知道该用什么术语,所以在谷歌上搜索很困难。我试过“子查询”、“派生表”等,但运气不好。我得到了太多无关的搜索结果。在我看来,这个问题显然是一个范围问题:不同的子查询环境不能看到彼此。

说到重点。。。如果一个SQL查询在FROM子句中有子查询,并且其中一个子查询被多次使用,但在某些情况下它被嵌套到更深的子查询中以应用聚合函数,那么更深的子查询是否可以引用较浅的子查询以避免重复。。??

数据表 ORDERS :

ORDDATE  ORDNUM ORDACCT  ORDLOAD 
20180901 1      ABC99    101     
20180901 2      XYZ00    102     
20180901 3      ZZZ12    103

数据表 LOADS

LOADDATE LOADNUM LOADDRIV LOADHLP1 LOADHLP2 RATEDRIV RATEHLP1 RATEHLP2 
20180901 101     57                         1                          
20180901 102     60       71                1        2                 
20180901 103     58       81       85       1        3        3

此SQL可以工作,但所指出的子查询重复了两次:

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE

FROM        CERTODB.ORDERS AS ORDERS

                                      -- THE SUBQUERY BELOW IS THE FIRST DUPLICATION
INNER JOIN  (                         SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                                UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                                UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
            ) AS LOADEMPS   ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                                      -- THE SUBQUERY BELOW IS THE SECOND DUPLICATION
                FROM        (         SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                                UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                                UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
                            ) AS LOADEMPS
                GROUP BY    LOADDATE, LOADNUM
            ) AS LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM

ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

有没有一种方法可以让这一切在一个SQL查询中正常工作,或者类似的东西,这样就不会有重复了。。?例如,没有额外的视图、存储过程或其他对象,但都是在一个大型SQL语句中完成的。。?

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE

FROM        CERTODB.ORDERS AS ORDERS

INNER JOIN  (                         SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                                UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                                UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
            ) AS LOADEMPS   ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                -- BELOW AN ATTEMPT IS MADE TO ACCESS THE PREVIOUS CALL OF THE SUBQUERY 'LOADEMPS', THUS REMOVING THE DUPLICATE
                FROM        LOADEMPS 
                GROUP BY    LOADDATE, LOADNUM
            ) AS LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM

ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

3 回复 | 直到 7 年前

Charles 7 年前

我相信 LATERAL 关键字将有帮助

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE

FROM        CERTODB.ORDERS AS ORDERS

INNER JOIN  (                         SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                                UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                                UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
            ) AS LOADEMPS   ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  LATERAL (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                -- BELOW AN ATTEMPT IS MADE TO ACCESS THE PREVIOUS CALL OF THE SUBQUERY 'LOADEMPS', THUS REMOVING THE DUPLICATE
                FROM        LOADEMPS 
                GROUP BY    LOADDATE, LOADNUM
            ) AS LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM

ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

with loademps as (SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                                UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                                UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
            )
, loadtypes as (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                -- BELOW AN ATTEMPT IS MADE TO ACCESS THE PREVIOUS CALL OF THE SUBQUERY 'LOADEMPS', THUS REMOVING THE DUPLICATE
                FROM        LOADEMPS 
                GROUP BY    LOADDATE, LOADNUM
            )
SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE
FROM        CERTODB.ORDERS AS ORDERS
INNER JOIN  LOADEMPS   ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM
ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

spinjector 7 年前

基于对我的OP的评论,我似乎需要一个公共表表达式(CTE),aka WITH子句: https://modern-sql.com/feature/with

下面是重新设计的SQL,需要删除重复项:

WITH LOADEMPS AS    (         SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
                        UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
                        UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
                    )

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE

FROM        CERTODB.ORDERS  AS ORDERS
INNER JOIN  LOADEMPS        ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                FROM        LOADEMPS 
                GROUP BY    LOADDATE, LOADNUM
            ) AS LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM

ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

Edward 7 年前

使用全局临时表比使用 CTE

它还可以让你使用超快和宽松 SELECT ... INTO 无需提前定义表的语法。

SELECT * 
INTO #Global_Temp 
FROM (
SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0
UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0
UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0
);

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATE

FROM        CERTODB.ORDERS AS ORDERS

                                      -- THE SUBQUERY BELOW IS THE FIRST DUPLICATION
INNER JOIN  (                       SELECT LOADDATE, LOADROLE, EMPID, RATE FROM #Global_Temp 
            ) AS LOADEMPS   ON  ORDERS.ORDDATE = LOADEMPS.LOADDATE
                            AND ORDERS.ORDLOAD = LOADEMPS.LOADNUM
INNER JOIN  (   SELECT      LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE
                                      -- THE SUBQUERY BELOW IS THE SECOND DUPLICATION
                FROM        (       SELECT LOADDATE, LOADROLE, EMPID, RATE FROM #Global_Temp 
                            ) AS LOADEMPS
                GROUP BY    LOADDATE, LOADNUM
            ) AS LOADTYPE   ON  ORDERS.ORDDATE = LOADTYPE.LOADDATE 
                            AND ORDERS.ORDLOAD = LOADTYPE.LOADNUM
ORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE;

DROP TABLE #Global_Temp;