SQL查询在SQL Server中有效，在Excel(Microsoft query)中失败

Question

我有以下查询，它按预期工作：

SELECT
        SERVICE_HISTORY.ServiceMode, SERVICE_HISTORY.CreatedDate,
        SERVICE_HISTORY.CreatedBy, SERVICE_HISTORY.Branch,
        SERVICE_HISTORY.Comments
FROM
        DEBA_US.dbo.SERVICE_HISTORY
JOIN 
        (SELECT MAX(SERVICE_HISTORY.CreatedDate) AS maxDate, CUSTOMER.AccNo
         FROM DEBA_US.dbo.CUSTOMER
         INNER JOIN (DEBA_US.dbo.SERVICE_HISTORY
                     INNER JOIN DEBA_US.dbo.CAR ON SERVICE_HISTORY.ROW_PK = CAR.ROW_PK) ON CUSTOMER.ROW_PK = CAR.ROW_PK
         WHERE
             CUSTOMER.AccNo LIKE 'CUS-1234'
             AND CAR.DateSubmitted IS NULL
         GROUP BY
             CUSTOMER.AccNo) AS testQuery ON testQuery.maxDate = SERVICE_HISTORY.CreatedDate

查询是为了给我给定客户的最新（最大）服务历史日期。

当我在SQL Server中执行查询时，它运行得很好，但当我将相同的查询放入EXCEL 2010（Microsoft query）时，它会给我错误：

没有为"testQuery"的列1指定列名
列名"maxDate"无效
无法准备报表

我无法修复查询以传递错误。有人能告诉我为什么Excel不能处理上述查询吗？感谢

Answer 1

您需要将testQuery和maxDate放入单引号中

SELECT
    SERVICE_HISTORY.ServiceMode, SERVICE_HISTORY.CreatedDate,
    SERVICE_HISTORY.CreatedBy, SERVICE_HISTORY.Branch,
    SERVICE_HISTORY.Comments
FROM
    DEBA_US.dbo.SERVICE_HISTORY
JOIN 
    (SELECT MAX(SERVICE_HISTORY.CreatedDate) AS 'maxDate', CUSTOMER.AccNo
     FROM DEBA_US.dbo.CUSTOMER
     INNER JOIN (DEBA_US.dbo.SERVICE_HISTORY
                 INNER JOIN DEBA_US.dbo.CAR ON SERVICE_HISTORY.ROW_PK = CAR.ROW_PK) ON     CUSTOMER.ROW_PK = CAR.ROW_PK
     WHERE
         CUSTOMER.AccNo LIKE 'CUS-1234'
         AND CAR.DateSubmitted IS NULL
     GROUP BY
         CUSTOMER.AccNo) AS 'testQuery' ON testQuery.maxDate =  SERVICE_HISTORY.CreatedDate

Answer 2

您唯一需要做的就是在maxDate周围添加方括号，如下所示：

SELECT
        SERVICE_HISTORY.ServiceMode, SERVICE_HISTORY.CreatedDate,
        SERVICE_HISTORY.CreatedBy, SERVICE_HISTORY.Branch,
        SERVICE_HISTORY.Comments
FROM
        DEBA_US.dbo.SERVICE_HISTORY
JOIN 
        (SELECT MAX(SERVICE_HISTORY.CreatedDate) AS [maxDate], CUSTOMER.AccNo
         FROM DEBA_US.dbo.CUSTOMER
         INNER JOIN (DEBA_US.dbo.SERVICE_HISTORY
                     INNER JOIN DEBA_US.dbo.CAR ON SERVICE_HISTORY.ROW_PK = CAR.ROW_PK) ON CUSTOMER.ROW_PK = CAR.ROW_PK
         WHERE
             CUSTOMER.AccNo LIKE 'CUS-1234'
             AND CAR.DateSubmitted IS NULL
         GROUP BY
             CUSTOMER.AccNo) AS testQuery ON testQuery.maxDate = SERVICE_HISTORY.CreatedDate