在集合函数里面写子查询
0
悬赏园豆：20
[已关闭问题]
SUM(oi.GiveMasterMoney + ((o.FuelAppendFee+o.AirDromeBuildFee)/ (SELECT COUNT(dbo.OrderItems.OrderID) AS TicketCount FROM dbo.Orders INNER JOIN dbo.OrderItems
ON dbo.Orders.OrderID = dbo.OrderItems.OrderID
WHERE dbo.OrderItems. OrderID = 50097
AND dbo.OrderItems.TicketState NOT IN (7)
GROUP BY dbo.OrderItems.OrderID ) ))
请问高手这个怎么实现 ？
其他回答(2)
0
你自己写的这个是什么 ？ 一个算总价格的 SQL吗？
请把你的需求说出来 ，有哪几个表 的 哪个几个字段需要 求和
0
楼主解释一下需求
0
select SUM(oi.GiveMasterMoney + ((o.FuelAppendFee+ o.AirDromeBuildFee)/oin.num) as amount
from orders as o
INNER JOIN order_items as oi on o.order_id = oi.order_id
inner join (select count(*) as num, order_id from order_items where order_id=1400002082 group by order_id) as oin
on o.order_id = oin.order_id
WHERE oi.order_id = 1400002082 AND oi.TicketState not in (7)
GROUP BY oi.order_id
楼主自己把表名、字段名替换一下就好了。
这里挺怪的哈 油费+机场建设费/机票数量 ？ 先把订单的总量除以订单项的数，然后又加起来，那不还是原来的值么。。
还是说要过滤掉其中的某些订单项，比如这个条件？ dbo.OrderItems.TicketState NOT IN (7)
