问题描述
我的任务是:选择所有有利可图的航班. transportation是有关航班的一般信息. ticket包含有关许多票证及其价格的信息.因此，如何串联UPDATE和SELECT表以便仅查找profit大于0的那些行.UPDATE表应该先行，因为profit字段为空
My task is: Choose all profitable flights. transportation is a general information about the flight. ticket includes information about a number of tickets and their cost. So, how to concatenate UPDATE and SELECT table in order to find only those rows with profit greater than 0.UPDATE table should go first because profit field is empty
UPDATE transportations
set profit =((transportations.ticket.cost*
(transportations.ticket.sold_q+transportations.ticket.booked_q))-(0.2*
transportations.plan_oil))
WHERE profit is null
SELECT profit FROM transportations
WHERE profit >0
ORDER BY profit
推荐答案
通常不建议保存计算的数据.您似乎正在使用多值字段.如何保存每张票的利润计算?为每个航班记录节省利润将需要汇总机票计算.使用UPDATE保存聚合数据将导致不可更新"错误.
Saving calculated data is usually not advisable. And you appear to be using a multi-value field. How can you save each ticket profit calc? Saving profit for each flight record would require aggregating the ticket calcs. Saving aggregate data with an UPDATE will result in a 'non-updatable' error.
如果您想返回每张彩票的利润计算，并且仅返回大于0的利润计算，请尝试:
If you want to return each ticket's profit calc and only those greater than 0, try:
SELECT *, (ticket.cost * (ticket.sold_q + ticket.booked_q))-(0.2* plan_oil) AS Profit
FROM transportations
WHERE (ticket.cost * (ticket.sold_q + ticket.booked_q))-(0.2* plan_oil) > 0;
如果要考虑每个航班的总利润，请尝试:
If you want to consider the aggregate profit for each flight, try:
SELECT FlightID, Sum((ticket.cost * (ticket.sold_q + ticket.booked_q))-(0.2* plan_oil)) AS Profit
FROM transportations
GROUP BY FlightID
HAVING Sum((ticket.cost * (ticket.sold_q + ticket.booked_q))-(0.2* plan_oil)) > 0;
这篇关于UPDATE和SELECT表串联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！