SQL 2008 VS 2012错误：关键字'COMPUTE'附近的语法不正确

My friend sent me the commands that he wrote in server 2008 and they worked with no problems, mine however from a copy and past did not work with 2012. Is there any reason why? Here is the code:

我的朋友给我发了他在服务器2008中写的命令，他们没有任何问题，但我从副本和过去的工作没有与2012年一起工作。有什么理由吗？这是代码：

        Use Kudler_Database
        SELECT  AccountNumber, [Description], ShortDescription,Balance
        FROM Chart_of_Accounts 
        ORDER BY left (AccountNumber, 2)
        COMPUTE SUM(Balance) BY left (AccountNumber, 2)
        COMPUTE SUM(Balance);

Here is the error :

这是错误：

Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'COMPUTE'.

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

3 个解决方案

#1

COMPUTE is no longer available in SQL server 2012, thats why you are getting that error. See this page:

SQL Server 2012中不再提供COMPUTE，这就是您收到该错误的原因。看这个页面：

Discontinued Database Engine Functionality in SQL Server 2012
SQL Server 2012中已停止的数据库引擎功能

It said that:

它说：

This topic describes the Database Engine features that are no longer available in SQL Server 2012:

本主题描述SQL Server 2012中不再提供的数据库引擎功能：

*Transact-SQL syntax | COMPUTE / COMPUTE BY *

* Transact-SQL语法|计算/计算*

#2

A kind of hack with RollUp since Compute By is deprecated in SQL Server 2012 - (see "SQL SERVER – Use ROLL UP Clause instead of COMPUTE BY")

在SQL Server 2012中不推荐使用自Compute By以来的RollUp一种黑客攻击 - （请参阅“SQL SERVER - 使用ROLL UP子句而不是COMPUTE BY”）

DECLARE @t TABLE(AccountNumber VARCHAR(10),[Description] VARCHAR(100),ShortDescription VARCHAR(100),Balance INT)
INSERT INTO @t SELECT '1234567890','Some Description for 1st Account','Short Description for 1st Account',2000 Union All
SELECT '2345678901','Some Description for 2nd Account','Short Description for 2nd Account',3000 Union All
SELECT '1234567890','Some Description for 1st Account','Short Description for 1st Account',4000

SELECT  
    AccountNumber   
    ,Balance
    ,Total = SUM(Balance)
FROM @t 
GROUP BY AccountNumber,Balance
WITH ROLLUP

Result

结果

AccountNumber   Balance  total
1234567890      2000     2000
1234567890      4000     4000
1234567890      NULL     6000
2345678901      3000     3000
2345678901      NULL     3000
NULL            NULL     9000

要么

you can use the below

你可以使用下面的

DECLARE @t TABLE(AccountNumber VARCHAR(10),[Description] VARCHAR(100),ShortDescription VARCHAR(100),Balance INT)
INSERT INTO @t SELECT '1234567890','Some Description for 1st Account','Short Description for 1st Account',2000 Union All
SELECT '2345678901','Some Description for 2nd Account','Short Description for 2nd Account',3000 Union All
SELECT '1234567890','Some Description for 1st Account','Short Description for 1st Account',4000

;With CTE AS
(
SELECT 
    AccountNumber
    ,[Description]
    ,ShortDescription
    ,Balance
    ,SubTotal = SUM(Balance) OVER (PARTITION BY AccountNumber ORDER BY LEFT (AccountNumber, 2))
    ,Rn = ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY LEFT (AccountNumber, 2))
FROM @t)
SELECT 
    AccountNumber
    ,[Description]
    ,ShortDescription
    ,Balance  = CAST(Balance AS VARCHAR(10))
    ,SubTotal = CASE  WHEN Rn != 1 THEN NULL ELSE SubTotal END
FROM CTE
UNION ALL
SELECT
    ' ', ' ',' ' ,'Total Amount' , SUM(Balance) FROM CTE

The output being

输出是

AccountNumber   Description                         ShortDescription                  Balance   SubTotal
1234567890      Some Description for 1st Account    Short Description for 1st Account   2000    6000
1234567890      Some Description for 1st Account    Short Description for 1st Account   4000    NULL
2345678901      Some Description for 2nd Account    Short Description for 2nd Account   3000    3000
                                                                                    Total Amount  9000

#3

You can create something similar with GROUPING SETS but it all comes in one resultset, eg something like:

您可以使用GROUPING SETS创建类似的东西，但它们都包含在一个结果集中，例如：

SELECT AcGroup, AccountNumber, [Description], ShortDescription, SUM( Balance ) Balance,  GROUPING_ID(AcGroup, AccountNumber)
FROM ( SELECT LEFT( AccountNumber, 2 ) AcGroup, * FROM Chart_of_Accounts ) x
GROUP BY GROUPING SETS( (AcGroup), ( AccountNumber, [Description], ShortDescription ), () )

SELECT AcGroup, SUM( Balance ) Balance
FROM ( SELECT LEFT( AccountNumber, 2 ) AcGroup, * FROM Chart_of_Accounts ) x
GROUP BY GROUPING SETS( AcGroup, () )

SELECT AcGroup, SUM( Balance ) Balance
FROM ( SELECT LEFT( AccountNumber, 2 ) AcGroup, * FROM Chart_of_Accounts ) x
GROUP BY AcGroup WITH CUBE

I've added GROUPING_ID() which makes it easier to work out if the source is an original, summary to total row.

我已经添加了GROUPING_ID（），如果源是原始的，总计行的摘要，它可以更容易地计算出来。

I always wondered how you would consume something like that as the multiple resultsets make it difficult to handle. You can't pass it to another stored procedure, you can't copy it paste it directly to Excel (without messing around), passing to a .net client would be awkward. How did you consume the previous code?

我总是想知道你会如何使用这样的东西，因为多个结果集使得它很难处理。你不能将它传递给另一个存储过程，你不能将它复制粘贴到Excel（没有搞乱），传递给.net客户端会很尴尬。你是如何使用以前的代码的？

HTH

#1