“多部分标识符无法绑定”错误

Question

我正在使用Server 2005。我知道这个错误：

*不能绑定多部分标识符"ms.MOP_desc“。

我测试了这两个select查询中的每一个，它们各自工作得很好，但是当我合并这些查询时，我得到了错误。有人能告诉我这个查询出了什么问题吗？谢谢。

SELECT SUM(Amount) AS TotalAmount, ms.MOP_desc
FROM 
(
    SELECT SUM(hd.delivery_value) AS Amount, ms.MOP_desc
    FROM TRANSACTION_HEADER AS th 
        INNER JOIN TRANSACTION_DETAIL AS td 
            ON th.transaction_number = td.transaction_number 
        LEFT JOIN hose_delivery hd 
            ON td.delivery_id = hd.delivery_id 
        LEFT JOIN product pr 
            ON pr.product_id = td.product_id
        INNER JOIN MOP_Setting AS ms 
            ON hd.MOP_ID = ms.MOP_ID  
    WHERE hd.delivery_value > 0 
        AND (th.USER_PERIOD_ID IN (13))
        AND (hd.MOP_ID  IN (1))
        AND hd.Cleared_By != '0'
    GROUP BY ms.MOP_desc

    UNION ALL

    SELECT  SUM(td.quantity * td.price_sold) AS Amount, ms.MOP_desc 
    FROM TRANSACTION_HEADER AS th 
        INNER JOIN TRANSACTION_DETAIL AS td 
            ON th.transaction_number = td.transaction_number 
        INNER JOIN MOP_Setting AS ms 
            ON th.MOP_ID = ms.MOP_ID  
    WHERE (th.USER_PERIOD_ID IN (13))
        AND (th.MOP_ID  IN (1))
    GROUP BY ms.MOP_desc
)t

Answer 1

因为UNIONed查询的结果是一个行集，您已经将别名t给了-- ms别名不再适用：

SELECT SUM(Amount) AS TotalAmount, t.MOP_desc
FROM 
(
...
)t

如果这确实是第二个SUM步骤，那么还需要一个外部GROUP BY子句。

Answer 2

当您从子select而不是表中选择时，子select的列名不会暴露给外部select。

使用

SELECT SUM(Amount) AS TotalAmount, mop
FROM 
(
  SELECT SUM(hd.delivery_value) AS Amount, ms.MOP_desc as mop
  FROM TRANSACTION_HEADER AS th 
      INNER JOIN TRANSACTION_DETAIL AS td 
...