时间戳

首页 > 解决方案 > 在交叉连接方面需要帮助

sql-server - 在交叉连接方面需要帮助

问题描述

SELECT * 
FROM 
    (SELECT 
         SUM(CASE WHEN LOCAL_CCY_AMT < 0 THEN LOCAL_CCY_AMT END) AS DR 
     FROM 
         INSIGHTSOURCE.BS.RE_CRF_GL 
     WHERE 
         CONSOL_KEY10 = 'CUR' 
     GROUP BY 
         Consol_Key10

     CROSS JOIN

     SELECT 
         SUM(CASE WHEN LOCAL_CCY_AMT < 0 THEN LOCAL_CCY_AMT END) AS DR 
     FROM 
         INSIGHTSOURCE.BS.RE_CRF_GL 
     WHERE 
         CONSOL_KEY10 IN ('REG','WCL') 
     GROUP BY 
         Consol_Key10

     CROSS JOIN

     SELECT 
         SUM(CASE WHEN LOCAL_CCY_AMT < 0 THEN LOCAL_CCY_AMT END) AS DR 
     FROM 
         INSIGHTSOURCE.BS.RE_CRF_GL 
     WHERE 
         CONSOL_KEY10 IN ('OAM', 'SUB') 
     GROUP BY 
         Consol_Key10

     CROSS JOIN

     SELECT 
         SUM(CASE WHEN LOCAL_CCY_AMT < 0 THEN LOCAL_CCY_AMT END) AS DR 
     FROM 
         INSIGHTSOURCE.BS.RE_CRF_GL 
     WHERE 
         CONSOL_KEY10 IN ('DBT', 'LOS') 
     GROUP BY 
         Consol_Key10

     CROSS JOIN

     SELECT 
         SUM(CASE WHEN LOCAL_CCY_AMT < 0 THEN LOCAL_CCY_AMT END) AS DR 
     FROM 
         INSIGHTSOURCE.BS.RE_CRF_GL 
     WHERE 
         CONSOL_KEY10 IN ('CHO') 
     GROUP BY 
         Consol_Key10
)

我收到这些错误：

消息 156，级别 15，状态 1，第 4 行
关键字“CROSS”附近的语法不正确。

消息 156，级别 15，状态 1，第 6 行
关键字“CROSS”附近的语法不正确。

消息 156，级别 15，状态 1，第 8 行
关键字“CROSS”附近的语法不正确。

消息 156，级别 15，状态 1，第 10 行
关键字“CROSS”附近的语法不正确。

消息 102，级别 15，状态 1，第 12 行
')' 附近的语法不正确。

标签： sql-serverselectsubquery

解决方案

为每个子查询使用别名和括号

SELECT * FROM (
(SELECT SUM(CASE WHEN LOCAL_CCY_AMT < 0 THEN LOCAL_CCY_AMT END) AS DR FROM INSIGHTSOURCE.BS.RE_CRF_GL WHERE CONSOL_KEY10 = 'CUR' GROUP BY Consol_Key10) AS t1
CROSS JOIN
(SELECT SUM(CASE WHEN LOCAL_CCY_AMT < 0 THEN LOCAL_CCY_AMT END) AS DR FROM INSIGHTSOURCE.BS.RE_CRF_GL WHERE CONSOL_KEY10 IN ('REG','WCL') GROUP BY Consol_Key10) AS t2
CROSS JOIN
(SELECT SUM(CASE WHEN LOCAL_CCY_AMT < 0 THEN LOCAL_CCY_AMT END) AS DR FROM INSIGHTSOURCE.BS.RE_CRF_GL WHERE CONSOL_KEY10 IN ('OAM','SUB') GROUP BY Consol_Key10) AS t3
CROSS JOIN
(SELECT SUM(CASE WHEN LOCAL_CCY_AMT < 0 THEN LOCAL_CCY_AMT END) AS DR FROM INSIGHTSOURCE.BS.RE_CRF_GL WHERE CONSOL_KEY10 IN ('DBT','LOS') GROUP BY Consol_Key10) AS t4
CROSS JOIN
(SELECT SUM(CASE WHEN LOCAL_CCY_AMT < 0 THEN LOCAL_CCY_AMT END) AS DR FROM INSIGHTSOURCE.BS.RE_CRF_GL WHERE CONSOL_KEY10 IN ('CHO') GROUP BY Consol_Key10) AS t5
)

推荐阅读