gpt4 book ai didi

MySQL 查询 - 对一个相关表求和 - 对另一个相关表进行计数

转载 作者:行者123 更新时间:2023-11-29 06:05:35 26 4
gpt4 key购买 nike

计数在一个相关表中工作得很好,但是当我添加总和时,计数中的结果过度膨胀。

SELECT Orders.kp_OrderID AS `Order #`, 
Orders.t_ServiceLevel AS `Service Level`,
Customers.t_CustCompany AS Company,
Orders.t_JobName AS `Job Name`,
Orders.t_JobStatus AS `Status`,
Orders.d_JobDue AS Due,
Orders.ti_JobDue AS Time,
COUNT(OrderItems.kf_OrderID) AS `Line Item`,
SUM(
`OrderItemComponents`.`n_QuantityPrintMaterial` * (
`OrderItemComponents`.`n_WidthInInches` * `OrderItemComponents`.`n_HeightInInches` / 144
)
) AS SqFt,
Orders.nb_JobFinished AS Finished
FROM Orders
INNER JOIN OrderItems ON Orders.kp_OrderID = OrderItems.kf_OrderID
INNER JOIN Customers ON Orders.kf_CustomerID = Customers.kp_CustomerID
INNER JOIN OrderItemComponents ON OrderItemComponents.kf_OrderID = Orders.kp_OrderID
WHERE Orders.d_JobDue > (
DATE_SUB(CURDATE(), INTERVAL 60 DAY)
)
GROUP BY Orders.kp_OrderID
ORDER BY `Line Item` DESC

最佳答案

将您的 COUNT 行切换为:

COUNT(DISTINCT OrderItems.kf_OrderID) AS `Line Item`

添加行时

INNER JOIN OrderItemComponents 
ON OrderItemComponents.kf_OrderID = Orders.kp_OrderID

您返回了更多行,因为我假设 OrderItem 可以包含多个组件。

更新

如果您的 COUNT 语句符合以下条件,请尝试使用此选项:

(SELECT COUNT(OrderItems.kf_OrderID) 
FROM OrderItems.kf_OrderID
WHERE OrderItems.kf_OrderID=Order.kf_OrderID) AS `Line Item`,

关于MySQL 查询 - 对一个相关表求和 - 对另一个相关表进行计数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/11652394/

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