时间戳

首页 > 解决方案 > SQL 查询中的 Orderby 子句错误

sql - SQL 查询中的 Orderby 子句错误

问题描述

执行以下查询时出现错误：

INSERT Into dbo.[DRC_76_A-05 Deposits SBP Coding] 
SELECT '20180228' as BusinessDate,z.* 
FROM ( SELECT IND.L_SBP_CODE, TDEPO.Type_of_Deposit, 
              ISNULL(SUM(AC.WORKING_BALANCE),0) AS AMOUNT 
       FROM InsightSource.BS.ACCOUNT AC 
       LEFT JOIN InsightSource.BS.CUSTOMER CUS ON AC.CUSTOMER = CUS.[@ID] 
       INNER JOIN KMBL.DBO.TYPE_OF_DEPOSITS TDEPO ON AC.CATEGORY = TDEPO.Cat_ID 
       LEFT JOIN InsightSource.BS.INDUSTRY_LocalRef IND ON CUS.INDUSTRY = IND.[@Id] 
       GROUP BY IND.L_SBP_CODE, TDEPO.Type_of_Deposit 
       ORDER BY TDEPO.Type_of_Deposit ASC ) Z

错误：

ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP、OFFSET 或 FOR XML

标签： sqlsql-server-2012

解决方案

您应该将 ORDER BY 移到子查询之外 

  INSERT Into dbo.[DRC_76_A-05 Deposits SBP Coding] 
  Select '20180228' as BusinessDate,z.* 
  From ( 
      SELECT IND.L_SBP_CODE
            , TDEPO.Type_of_Deposit
            , ISNULL(SUM(AC.WORKING_BALANCE),0) AS AMOUNT 
      FROM InsightSource.BS.ACCOUNT AC 
      LEFT JOIN InsightSource.BS.CUSTOMER CUS ON AC.CUSTOMER = CUS.[@ID] 
      INNER JOIN KMBL.DBO.TYPE_OF_DEPOSITS TDEPO ON AC.CATEGORY = TDEPO.Cat_ID 
      LEFT JOIN InsightSource.BS.INDUSTRY_LocalRef IND ON CUS.INDUSTRY = IND.[@Id] 
      GROUP BY IND.L_SBP_CODE, TDEPO.Type_of_Deposit 
 ) Z
  ORDER BY Z.Type_of_Deposit ASC

推荐阅读