sql - 合并两个具有不同 ORDER BY 的 SELECT 查询

Question

我有一个 Story 表，我需要以下查询:- 前 6 行按距离排序(我计算的)- 下一行按 TIME 属性降序排序

declare @profileID int
set @profileID = 6

declare @longitude float
set @longitude = 17.6009169090776

declare @latitude float
set @latitude = 46.9548404806283

SELECT
        first.*
        FROM
            (
                SELECT top 6
                    [dbo].[Story].*,
                    SQRT( POWER(@Longitude - [dbo].[Story].[Longitude], 2) + POWER(@Latitude - [dbo].[Story].[Latitude], 2)) as [DistanceFromUser]
                FROM
                    [dbo].[Follow]
                LEFT JOIN
                    [dbo].[Story]
                    ON
                    [dbo].[Follow].[Followed] = [dbo].[Story].[ProfileID]
                WHERE
                    [dbo].[Follow].[Follower] = @ProfileID and
                    [dbo].[Story].IsDraft = 0
                ORDER BY 
                    [DistanceFromUser] asc
            ) first

        UNION ALL

        SELECT
            last.*
        FROM
            (
                SELECT TOP 100 PERCENT
                    [dbo].[Story].*,
                    SQRT( POWER(@Longitude - [dbo].[Story].[Longitude], 2) + POWER(@Latitude - [dbo].[Story].[Latitude], 2)) as [DistanceFromUser]
                FROM
                    [dbo].[Follow]
                LEFT JOIN
                    [dbo].[Story]
                    ON
                    [dbo].[Follow].[Followed] = [dbo].[Story].[ProfileID]
                WHERE
                    [dbo].[Follow].[Follower] = @ProfileID and
                    [dbo].[Story].IsDraft = 0
                **ORDER BY 
                    Time desc**
            ) last

我的问题是第二个查询。它不会按 TIME 属性对第 6 行之后的记录进行降序排序，而是按升序排序。

谢谢

Answer 1

试试这个

SELECT
        first.*
        FROM
            (
                SELECT top 6
                    [dbo].[Story].*,
                    SQRT( POWER(@Longitude - [dbo].[Story].[Longitude], 2) + POWER(@Latitude - [dbo].[Story].[Latitude], 2)) as [DistanceFromUser]
               ,1 as ord
                FROM
                    [dbo].[Follow]
                LEFT JOIN
                    [dbo].[Story]
                    ON
                    [dbo].[Follow].[Followed] = [dbo].[Story].[ProfileID]
                WHERE
                    [dbo].[Follow].[Follower] = @ProfileID and
                    [dbo].[Story].IsDraft = 0
                ORDER BY 
                    [DistanceFromUser] asc
            ) first

        UNION ALL

        SELECT
            last.*
        FROM
            (
                SELECT TOP 100 PERCENT
                    [dbo].[Story].*,
                    SQRT( POWER(@Longitude - [dbo].[Story].[Longitude], 2) + POWER(@Latitude - [dbo].[Story].[Latitude], 2)) as [DistanceFromUser]
               ,row_number() over(order by Time desc) as ord
                FROM
                    [dbo].[Follow]
                LEFT JOIN
                    [dbo].[Story]
                    ON
                    [dbo].[Follow].[Followed] = [dbo].[Story].[ProfileID]
                WHERE
                    [dbo].[Follow].[Follower] = @ProfileID and
                    [dbo].[Story].IsDraft = 0
                **ORDER BY 
                    Time desc**
            ) last

我的尝试(例子)

declare @ta as table 
(
    id int
    ,na varchar(100)
    ,sal numeric(18,2)
)

insert into @ta( id,na,sal) values (1,'aa',10)
insert into @ta( id,na,sal) values (3,'bb',100)
insert into @ta( id,na,sal) values (2,'c',5)
insert into @ta( id,na,sal) values (4,'dd',50)

select * from
(select top 2 * , 1 as ord from @ta order by id) as f
union all
select * from (select top 100 percent * , row_number() over(order by sal desc) ord from @ta order by sal desc
) as tt