Oracle SQL查询根据优先级将值分配到不同的行

I want to write SQL query which distribute or adds a value into different rows based on priority.

我想编写SQL查询,根据优先级将值分配或添加到不同的行。

Here is my scenario.

这是我的情景。

I have table called M_FLIGHT with below data.

我有一个名为M_FLIGHT的表,其中包含以下数据。

DEPARTURE_DATE  FLIGHT_NO  FAIR_TYPE          PRIORITY  AVAILABLE_SEATS  MAX_CAPACITY  RETURN_SEAT
==============  =========  =================  ========  ===============  ============  ===========
05-DEC-14       SC-917     Normal Fair           1         7                10             4
05-DEC-14       SC-917     Maharaja Standard     2         8                10             0
05-DEC-14       SC-917     Maharaja Special      3         9                10             0

A flight can have different fair types( i.e Normal Fair, Maharaja Standard, Maharaja Delhi Special so on) with priorities 1, 2 and 3 respectively.

航班可以有不同的公平类型(即师范博览会,大君标准,大君德里特等),优先级分别为1,2和3。

If user cancels/returns the booked seats then I need to add RETURN_SEAT value to the AVAILABLE_SEATS value such that it should not exceed the MAX_CAPACITY value for that fair type.

如果用户取消/返回预订的座位,那么我需要将RETURN_SEAT值添加到AVAILABLE_SEATS值,使其不应超过该公平类型的MAX_CAPACITY值。

If it exceeds then add remaining value to next priority(i.e to next fair type).

如果超过,则将剩余价值添加到下一个优先级(即下一个公平类型)。

So, the final result should be as below

因此,最终结果应如下所示

DEPARTURE_DATE  FLIGHT_NO  FAIR_TYPE          PRIORITY  AVAILABLE_SEATS  MAX_CAPACITY  RETURN_SEAT
==============  =========  =================  ========  ===============  ============  ===========
05-DEC-14       SC-917     Normal Fair           1         10               10             4
05-DEC-14       SC-917     Maharaja Standard     2         9                10             0
05-DEC-14       SC-917     Maharaja Special      3         9                10             0

2 个解决方案

#1

You can calculate the occupied space using a cumulative sum:

您可以使用累计总和来计算占用的空间:

select f.*, sum(max_capacity-available_seats) as occupied,
        sum(max_capacity-available_seats) over (order by priority) as cumeocc
from m_flight f;

With this information, you can allocate v_Num new seats:

有了这些信息,您可以分配v_Num新席位:

select f.*,
       (case when v_Num >= cumeocc - occupied
             then greatest(v_num - (cumeocc - occupied), occupied)
             else occupied
        end) as new_occupied
from (select f.*, (max_capacity-available_seats) as occupied,
              sum(max_capacity-available_seats) over (order by priority) as cumeocc
      from m_flight f
     ) f;

Then the next step is to merge this information back. That is a bit hard to express, because you don't have an obvious key on each row. I would suggest you add a single-column primary key to the table.

然后下一步是将这些信息合并。这有点难以表达,因为你在每一行都没有明显的键。我建议你在表中添加一个单列主键。

#2

I wrote a update query for this business.

我为这个行业写了一个更新查询。

update M_FLIGHT d
   set d.AVAILABLE_SEATS =
       (select case
                 when (f.available_seats + Case
                        when ((select sum(s.return_seat) from M_FLIGHT s) -
                             nvl((select sum(s.max_capacity - s.available_seats)
                                    from M_FLIGHT s
                                   where s.priority < f.priority),
                                  0)) > 0 THEN
                         ((select sum(s.return_seat) from M_FLIGHT s) -
                         nvl((select sum(s.max_capacity - s.available_seats)
                                from M_FLIGHT s
                               where s.priority < f.priority),
                              0))
                        Else
                         0
                      END) >= f.max_capacity then
                  f.max_capacity
                 else
                  (f.available_seats + Case
                    when ((select sum(s.return_seat) from M_FLIGHT s) -
                         nvl((select sum(s.max_capacity - s.available_seats)
                                from M_FLIGHT s
                               where s.priority < f.priority),
                              0)) > 0 THEN
                     ((select sum(s.return_seat) from M_FLIGHT s) -
                     nvl((select sum(s.max_capacity - s.available_seats)
                            from M_FLIGHT s
                           where s.priority < f.priority),
                          0))
                    Else
                     0
                  END)
               end as avail_new
          from M_FLIGHT f
         where f.priority = d.priority);

I know that's to hard to read this query, but it works. Maybe you need to add some filters for sub queries (like DEPARTURE_DATE,FLIGHT_NO,...).

我知道这很难阅读这个查询,但它确实有效。也许您需要为子查询添加一些过滤器(例如DEPARTURE_DATE,FLIGHT_NO,...)。

Thank you for Bring up some issue that needs to think a lot. I liked it.

感谢您提出一些需要思考的问题。我喜欢它。

#1