gpt4 book ai didi

sql - 将查询从 MS Access 转换为 SQL Server

转载 作者:行者123 更新时间:2023-12-04 19:26:00 25 4
gpt4 key购买 nike

我对 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]

这是查询的结果...

谁能建议如何添加交货总计列?

感谢您的关注。

最佳答案

您需要将 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]

关于sql - 将查询从 MS Access 转换为 SQL Server,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43891742/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com