对 union 结果进行排序

# 第一种排序      
  select msgId ,body ,[from] ,[to] ,timetag ,attach ,convType ,type as msgType
        from MessageRecord 
        where [from] = 'aaa' and [to] = 'bbb'  and timetag < '1625220012990'
        UNION
        select msgId ,body ,[from] ,[to] ,timetag ,attach ,convType ,type as msgType
        from MessageRecord
        where [to] = 'bbb' and [from] = 'aaa' and timetag < '1625220012990'
        order by timetag DESC;
                
# 第二种排序
    select  row_number() over (order by timetag DESC) row_num, * FROM
    (
                (select msgId ,body ,[from] ,[to] ,timetag ,attach ,convType ,type as msgType
                    from MessageRecord 
                    where [from] = 'aaa' and [to] = 'bbb'  and timetag < '1625220012990'
                )
                    UNION
                (select msgId ,body ,[from] ,[to] ,timetag ,attach ,convType ,type as msgType
                    from MessageRecord
                    where [to] = 'bbb' and [from] = 'aaa' and timetag < '1625220012990'
                )
    )temp;

 

 
posted @ 2021-07-02 18:01  明天,你好啊  阅读(2794)  评论(0编辑  收藏  举报