sql-server - 中间有标量变量的 SELECT 语句

Question

所以我在 SQLZoo 网站https://sqlzoo.net/wiki/AdventureWorks_easy_questions上玩这个脚本， 它工作正常。

SELECT
    t.CountryRegion,
    t.CompanyName,
    t.Total
FROM
    (SELECT
         t.*,
         @counter := CASE
                        WHEN @CountryRegion = t.CountyRegion
                           THEN @counter + 1
                           ELSE 1
                     END AS counter,
         @CountryRegion := t.CountyRegion AS CountryRegion
     FROM
         (SELECT
              @counter := 0,
              @CountryRegion := 0) AS initvar,
         (SELECT
              Address.CountyRegion,
              Customer.CompanyName,
              SUM(SubTotal) AS 'Total'
          FROM
              SalesOrderHeader
          JOIN
              Customer ON SalesOrderHeader.CustomerID = Customer.CustomerID
          JOIN
              CustomerAddress ON Customer.CustomerID = CustomerAddress.CustomerID
          JOIN
              Address ON CustomerAddress.AddressID = Address.AddressID
          GROUP BY
              Customer.CompanyName, Address.CountyRegion
          ORDER BY 
              Address.CountyRegion, SUM(SubTotal) DESC) AS t
    ) AS t
WHERE
    t.counter = 1;

然后我对我的本地 SQL Server 2017AdventureWorks数据库（使用正确的数据库名称）运行它，我收到了这个错误：

必须声明标量变量

我做错了什么？

标签： sql-server

Answer 1

您的脚本是为 MySQL 编写的，而不是为 SQL Server 编写的，并且是获得每组最大 n 的一种方式。使用 SQL Server，您可以使用ROW_NUMBER() 每个组来获得最大的 n，例如

SELECT
  t.CountryRegion,
  t.CompanyName,
  t.Total
FROM 
    (
        SELECT
          a.CountyRegion,
          c.CompanyName,
          SUM(SubTotal) AS Total,
          ROW_NUMBER() OVER(PARTITION BY a.CountryRegion 
                            ORDER BY SUM(SubTotal)) AS Counter
        FROM SalesOrderHeader AS soh
          JOIN Customer AS c
            ON soh.CustomerID = c.CustomerID
          JOIN CustomerAddress AS ca
            ON c.CustomerID = ca.CustomerID
          JOIN Address AS a
            ON ca.AddressID = a.AddressID
        GROUP BY c.CompanyName, a.CountyRegion
      ) AS t
WHERE t.Counter = 1;