如何在sql查询中添加Order by

update Room set Status = case
                        when Room_Rev.In_DateTime IS NOT NULL and Room_Rev.Out_DateTime IS NULL
                            then 'U'
                        when Room_Rev.In_DateTime IS NOT NULL and Room_Rev.Out_DateTime IS NOT NULL
                            then 'A'     
                        when Room.Status!='R' and Room.Status!='U' and Room.Status!='A'
                            then Room.Status
                         else 'R'
                                end 
                                FROM Room JOIN Room_Rev 
                    ON Room.Room_ID=Room_Rev.Room_ID 
                    and 
                    ((Room_Rev.Start_Date >= '2015-03-22' and Room_Rev.End_Date <= '2015-03-22') 
                                OR 
                    (Room_Rev.Start_Date<= '2015-03-22' and Room_Rev.End_Date> '2015-03-22') 
                                OR 
                    (Room_Rev.Start_Date< '2015-03-22' and Room_Rev.End_Date>= '2015-03-22'))

How to add order by Rev_ID desc in the query? There are two table which is Room and Room_Rev, they are one to many relationship

如何通过Rev_ID desc在查询中添加订单?有两个表是Room和Room_Rev,它们是一对多的关系

The last two row ROM0006 already fill the In_DateTime and Out_DateTime, thus it regard check out, and the last row insert new reservation, the In_DateTime is null thus i need the query return 'R' (Reserved status)

最后两行ROM0006已经填充了In_DateTime和Out_DateTime,因此它看待检出,最后一行插入新的保留,In_DateTime为null因此我需要查询返回'R'(保留状态)

1 个解决方案

#1

As one of the possible solutions I suggest a nested query instead of a join in UPDATE statement. The logic of the update is not completely clear to me, so I leave the final update for OP to correct order of sorting (Note I used top 1 and order by room_ID in the nested SELECT statement). However, this approach allows to handle all usual techniques applicable for a SELECT.

作为可能的解决方案之一,我建议使用嵌套查询而不是UPDATE语句中的连接。更新的逻辑对我来说并不完全清楚,因此我将OP的最终更新保留为正确的排序顺序(注意我在嵌套的SELECT语句中使用了top 1和order_ID的顺序)。但是,这种方法允许处理适用于SELECT的所有常用技术。

update Room set Status = (select TOP 1 case
        when Room_Rev.In_DateTime IS NOT NULL and Room_Rev.Out_DateTime IS NULL
            then 'U'
        when Room_Rev.In_DateTime IS NOT NULL and Room_Rev.Out_DateTime IS NOT NULL
            then 'A'     
        when Room.Status!='R' and Room.Status!='U' and Room.Status!='A'
            then Room.Status
            else 'R'
        end 
    FROM Room_Rev 
    WHERE Room.Room_ID=Room_Rev.Room_ID 
    and 
    ((Room_Rev.Start_Date >= '2015-03-22' and Room_Rev.End_Date <= '2015-03-22') 
                OR 
    (Room_Rev.Start_Date<= '2015-03-22' and Room_Rev.End_Date> '2015-03-22') 
                OR 
    (Room_Rev.Start_Date< '2015-03-22' and Room_Rev.End_Date>= '2015-03-22'))
    ORDER BY Room_Rev.Room_Id
    )

PS. As a piece of advise I still assume that such approach is not valid. It prevents proper normalization of data. You'd rather have this information always queried dynamically when required, instead of writing static value to ROOM.status

PS。作为一条建议,我仍然认为这种方法是无效的。它会阻止数据的正常标准化。您更愿意在需要时动态查询此信息,而不是将静态值写入ROOM.status