我不能在 SQL 查询中将 DISTINCT 与 SUM 一起使用

Question

我想用 SUM的SQL中的不同表收集数据。我想乘以产品的piece和price并添加旁边的cargoprice，但是我必须使用其中之一，因为cargoprice在多行上。但是我无法使用DISTINCT如下：

SELECT 
SUM((tbl_Product.price * piece) + DISTINCT(tbl_Cargo.cargoprice)) AS total 
FROM tbl_Order 
LEFT JOIN tbl_Product ON tbl_Product.productid = tbl_Order.productid 
LEFT JOIN tbl_Cargo on tbl_Cargo.cargoid = tbl_Order.cargoid 
WHERE userid = '1'

Answer 1

您可以在查询中使用"可以使用"组。示例代码在下面；

select SUM(price*piece)+cargoPrice,cargoPrice from (
select 5 as price,2 as piece,1 as cargoPrice
union all
select 5 as price,2 as piece,1 as cargoPrice
union all
select 5 as price,2 as piece,2 as cargoPrice
union all
select 5 as price,3 as piece,2 as cargoPrice
union all
select 5 as price,4 as piece,3 as cargoPrice
)A
group by cargoPrice

Answer 2

一辆货物可以包含多个订单。因此，您可能有货物＃1和＃2的订单，货物＃100，订单＃3和＃4运送了货物＃200。因此，您需要两个订单的总和，但在此示例中只需要两个嘉戈斯。

要解决此问题，请按货物进行分组：

select sum(per_cargo.pricesum + tbl_cargo.cargoprice) as total
from
(
  select o.cargoid, sum(p.price * p.piece) as pricesum
  from tbl_order o
  join tbl_product p on p.productid = o.productid
  where o.userid = 1
  group by o.cargoid
) per_cargo
join tbl_cargo on tbl_cargo.cargoid = per_cargo.cargoid ;

Answer 3

我解决了此代码的问题： SELECT DISTINCT(tbl_Cargo.cargoprice) + SUM(tbl_Product.price * piece) AS total FROM tbl_Order LEFT JOIN tbl_Product ON tbl_Product.productid = tbl_Order.productid LEFT JOIN tbl_Cargo on tbl_Cargo.cargoid = tbl_Order.cargoid WHERE userid = '1' GROUP BY tbl_Cargo.cargoprice