SQL Server执行查询报Msg 156 'as'关键字附近语法错误咨询

你遇到的Incorrect syntax near the keyword 'as'错误由以下3处语法问题直接导致：

• GROUP BY子句中不支持使用AS定义字段别名。你写的ar_invoice_date AS [Invoice Date]不符合语法规则：字段别名是在SELECT执行阶段生成的，GROUP BY执行顺序早于SELECT，无法识别别名，也不允许在分组子句里重复给字段起别名，这里直接写原始字段名即可。
• GROUP BY字段列表末尾多了一个多余的尾逗号（即[PO Date],后面的逗号），后续没有其他分组字段，会直接打断SQL语法解析。
• 最外层括号包裹的派生表（子查询）没有定义别名。SQL Server要求所有FROM后跟随的子查询派生表必须指定别名，否则解析到语句末尾也会抛出语法错误。

除此之外你的逻辑还存在一个隐性问题：外层SELECT *会查询出内层的grand_total、tax_pph、amount三个字段，但你原来的GROUP BY列表里没有包含这三个字段，在SQL Server默认开启的全组校验规则下，修正前面的语法错误后还会报「选择列表中的字段无效，因为该列没有包含在聚合函数或 GROUP BY 子句中」的错误。

SELECT *
FROM
  (SELECT 
     v_payment.ID,
     v_payment.[Invoice No.],
     v_payment.[Invoice Date],
     v_payment.[Due Date],
     v_payment.[Po No.],
     v_payment.[PO Date],
     v_payment.[Amount],
     v_payment.[Tax PPh],
     v_payment.[Current Paid],
     ISNULL(
       (SELECT sum(amount)
        FROM v_open_incoming_ar_detail
        WHERE inc_ar_id <> 4817
          AND ar_invoice_no = v_payment.[Invoice No.]),0) AS [Have Paid]
   FROM
     (SELECT ID,
             ar_invoice_no AS [Invoice No.],
             ar_invoice_date AS [Invoice Date],
             due_date AS [Due Date],
             po_no AS [Po No.],
             po_date AS [PO Date],
             grand_total AS [Amount],
             tax_pph AS [Tax PPh],
             amount AS [Current Paid]
      FROM v_open_incoming_ar_detail
      WHERE inc_ar_id = 4817 ) v_payment
   GROUP BY 
     v_payment.ID,
     v_payment.[Invoice No.],
     v_payment.ar_invoice_date,
     v_payment.[Due Date],
     v_payment.[Po No.],
     v_payment.[PO Date],
     v_payment.[Amount],
     v_payment.[Tax PPh],
     v_payment.[Current Paid]
  ) AS ar_result -- 给外层派生表补必填别名

如果内层查询的ID是v_open_incoming_ar_detail表的唯一主键，你写的GROUP BY完全是冗余操作，而且相关子查询会逐行执行计算，性能较差。可以改成LEFT JOIN预聚合的写法，逻辑等价且执行效率更高：

SELECT 
  v_payment.*,
  ISNULL(paid_agg.total_paid, 0) AS [Have Paid]
FROM
  (SELECT ID,
          ar_invoice_no AS [Invoice No.],
          ar_invoice_date AS [Invoice Date],
          due_date AS [Due Date],
          po_no AS [Po No.],
          po_date AS [PO Date],
          grand_total AS [Amount],
          tax_pph AS [Tax PPh],
          amount AS [Current Paid]
   FROM v_open_incoming_ar_detail
   WHERE inc_ar_id = 4817 ) v_payment
LEFT JOIN
  (SELECT ar_invoice_no, SUM(amount) AS total_paid
   FROM v_open_incoming_ar_detail
   WHERE inc_ar_id <> 4817
   GROUP BY ar_invoice_no) paid_agg
ON v_payment.[Invoice No.] = paid_agg.ar_invoice_no

内容的提问来源于stack exchange，提问作者Monocrows