将 MS Access 中的查询转换为 SQL Server

Question

我在 SQL 方面的经验有限，但被要求将下面的查询语句从 MS Access 转换。

TRANSFORM SUM(weekpace_crosstab.wgt) AS SumOfwgt
    SELECT 
        Products.[Product Type], SUM(weekpace_crosstab.wgt) AS DeliveryTotal    
    FROM 
        (weekpace_crosstab 
    LEFT JOIN 
        Customer ON weekpace_crosstab.Customer = Customer.Customer)  LEFT JOIN Products ON weekpace_crosstab.Product = Products.[Product Code]
WHERE (((Customer.[Customer Group])="Sainsbury"))
GROUP BY Products.[Product Type]
PIVOT weekpace_crosstab.Date;

这是结果...

通过谷歌和此论坛上的其他问题，我已经编写了如下语句。然而，我无法使交货总数像上面的图片一样显示。

SELECT * FROM 
(
SELECT P.[Product Type], wc.Date, sum (wc.wgt) AS DeliveryTotal

FROM weekpace_crosstab AS wc LEFT JOIN Customer AS C ON wc.Customer = C.Customer LEFT JOIN Products as p 

ON wc.Product = P.[Product Code]

WHERE C.[Customer Group]='Co-op'

GROUP BY  p.[Product Type], wc.Date, wc.wgt
) AS s
PIVOT 
(
SUM (DeliveryTotal)
FOR [Date] in ([2017-01-23],[2017-01-24],[2017-01-25],[2017-01-26],[2017-01-27],[2017-01-28],[2017-01-29])
)AS pvt
ORDER BY [Product Type]

以下是此查询的结果...

请问如何添加交付总计列？

感谢您的关注。

- Somerville

---

请为您要转换的 SQL 表单添加一个标签，表示您要转换 到 哪种数据库（例如 MySQL、SQL-Server 等）。 - toonice

Answer 1

你需要将DeliveryTotal作为第二列添加进去，因为第一列已被数据透视表使用，只会显示透视后的结果。

尝试：

SELECT * FROM 
(
SELECT P.[Product Type], wc.Date, sum (wc.wgt) AS DeliveryTotal, w.wgt
FROM weekpace_crosstab AS wc LEFT JOIN Customer AS C ON wc.Customer = 
C.Customer LEFT JOIN Products as p 

ON wc.Product = P.[Product Code]
LEFT JOIN (
SELECT SUM(wgt)wgt, Product 
FROM weekpace_crosstab 
WHERE Date IN ('2017-01-23', '2017-01-24', '2017-01-25', '2017-01-26', '2017-01-
27', '2017-01-28', '2017-01-29')
GROUP BY product) w ON w.Product = wc.product

WHERE C.[Customer Group]='Co-op'

GROUP BY  p.[Product Type], wc.Date, w.wgt
) AS s
PIVOT 
(
SUM (DeliveryTotal)
FOR [Date] in ([2017-01-23],[2017-01-24],[2017-01-25],[2017-01-26],[2017-01-
27],[2017-01-28],[2017-01-29])
)AS pvt
ORDER BY [Product Type]

- David Waldron

---

当我运行这个程序时，我确实得到了DeliveryTotal列，但是它失去了按产品类型分组的功能。 - Somerville

刚刚做了一个快速编辑，在其中添加了一个新的连接，应该可以解决这个问题。 - David Waldron