我有一个表,其中包含已在电子商务网站上购买的订单和第二个表,详细说明了每个订单的产品(主要是行项目)。我的职责是计算订购的每个产品的总成本和总重量,按产品分组。店主希望能够识别出按重量或按价值销售最多的产品。
这两个表的结构如下:
Order
id
orderDate
OrderLine
id
orderId
productNodeId
productName
quantity
duration
frequency
subscription
unitPrice
unitWeight
虽然一些订单行是简单的一次性产品(因此很容易计算总数,即总权重将是quantity * unitWeight
,总成本将是quantity * unitCost
),但其他订单行是订阅,其中总权重将由(duration/frequency) * unitWeight
计算,总成本将是(duration/frequency) * unitCost
。
我有以下T-SQL语句,它使用CASE返回总值(在SQL Server 2008R2 Express中运行):
SELECT
productNodeId AS id,
productName AS name,
quantity,
duration,
frequency,
subscription,
unitPrice,
CASE
WHEN subscription = 1 THEN unitWeight*(duration/frequency)
ELSE
unitWeight*quantity
END AS weight,
CASE
WHEN subscription = 1 THEN unitPrice*(duration/frequency)
ELSE
unitPrice*quantity
END AS total
FROM Order INNER JOIN OrderLine ON Order.id = OrderLine.orderId WHERE orderStatusId = 3
--GROUP BY productNodeId
ORDER BY total DESC
这在一定程度上是可行的。它输出以下值:
id | name | quantity | duration | frequency | subscription | unitPrice | weight | total
1105 Option C 1 24 1 1 2.50 18000 60.00
1105 Option C 1 24 2 1 2.50 9000 30.00
1104 Option B 1 24 2 1 2.50 9000 30.00
1105 Option C 1 24 2 1 2.50 10800 30.00
1105 Option C 1 4 1 1 2.50 2000 10.00
1107 Option A 1 0 0 0 2.80 600 2.80
1107 Option A 1 0 0 0 2.80 600 2.80
1121 Option D 1 0 0 0 2.80 400 2.80
不过,最后一部分是对总数进行求和。但是,我不能只实现上面代码中注释掉的GROUP BY productNodeId
语句,因为它需要聚合subscription
字段,这将否定条件weight
和total
逻辑。
我想知道是否有人可以建议如何让这个工作,以便我可以返回正确的总重量和价格?
非常感谢。
发布于 2018-06-08 17:40:13
根据@Anthony Hancock的评论,最简单的解决方案是将CASE
条件包装在SUM
聚合器中,如下所示:
SELECT
productNodeId AS id,
MAX(productName) AS name,
SUM(CASE
WHEN subscription = 1 THEN unitWeight*(duration/frequency)
ELSE
unitWeight*quantity
END) AS weight,
SUM(CASE
WHEN subscription = 1 THEN unitPrice*(duration/frequency)
ELSE
unitPrice*quantity
END) AS total
FROM Order INNER JOIN OrderLine ON Order.id = OrderLine.orderId WHERE orderStatusId = 3
GROUP BY productNodeId
ORDER BY total DESC
发布于 2018-06-08 04:19:41
我无法对此进行测试,但您应该能够对原始查询进行子查询,并将group by逻辑应用于所有字段,从而保持条件条件的完整性。
select id,name,quantity,duration,frequency,subscription,unitprice,weight,sum(total) as sumedtotal
from (
SELECT
productNodeId AS id,
productName AS name,
quantity,
duration,
frequency,
subscription,
unitPrice,
CASE
WHEN subscription = 1 THEN unitWeight*(duration/frequency)
ELSE
unitWeight*quantity
END AS weight,
CASE
WHEN subscription = 1 THEN unitPrice*(duration/frequency)
ELSE
unitPrice*quantity
END AS total
FROM Order INNER JOIN OrderLine ON Order.id = OrderLine.orderId WHERE orderStatusId = 3
--GROUP BY productNodeId
) as x
group by id,name,quantity,duration,frequency,subscription,unitprice,weight
https://stackoverflow.com/questions/50746596
复制相似问题