sql - 内部连接和联合都使用 order by

Question

我有这个查询:

SELECT B.IMAGE_ID as image_id_fav,I.Image_Path as image_path_fav 
FROM Buddies B
INNER JOIN @favDT F ON F.favorite_id = B.Reg_ID and b.favorite_id=@regID 
INNER JOIN Images I ON I.Image_ID = B.Image_ID
where b.Image_ID >0 
union all
select I.image_id as image_id_fav,I.Image_Path as image_path_fav FROM Buddies B
inner join @favDT F ON F.favorite_id = B.Reg_ID and b.favorite_id=@regID
inner join registration  R on R.Reg_ID = F.favorite_id
INNER JOIN Images I ON R.Default_Image = I.Image_ID
WHERE B.Image_ID=0
union all
SELECT I.IMAGE_ID as image_id_fav, I.IMAGE_PATH as image_path_fav FROM @favDT F
 LEFT OUTER JOIN Buddies B ON B.Reg_ID = F.favorite_id and b.favorite_id=@regID
INNER JOIN registration  R on R.Reg_ID =F.favorite_id
     INNER JOIN Images I ON R.Default_Image = I.Image_ID
     WHERE B.Reg_ID IS NULL

我需要按 F.favorite_id 进行排序，但我在关键字 union all 附近不断收到错误的语法

Answer 1

当您使用 UNION 时，您不能对单个查询进行排序并期望它以您想要的顺序返回。但是你可以在最后添加一个订单。
在您的情况下，您正在尝试按未选择的列排序。

您可以简单地将 favorite_id 添加到每个选择中，然后添加一个外部查询，该查询只返回您想要的两个列，但按 favorite_id 排序:

SELECT image_id_fav,image_path_fav
FROM (
select B.IMAGE_ID as image_id_fav, I.Image_Path as image_path_fav, 
       F.favorite_id as FavID
from Buddies B
inner join @favDT F on F.favorite_id = B.Reg_ID and b.favorite_id = @regID
inner join Images I on I.Image_ID = B.Image_ID
where b.Image_ID > 0
union all
select I.image_id as image_id_fav, I.Image_Path as image_path_fav, 
       F.favorite_id
from Buddies B
inner join @favDT F on F.favorite_id = B.Reg_ID and b.favorite_id = @regID
inner join registration R on R.Reg_ID = F.favorite_id
inner join Images I on R.Default_Image = I.Image_ID
where B.Image_ID = 0
union all
select I.IMAGE_ID as image_id_fav, I.IMAGE_PATH as image_path_fav, 
       F.favorite_id
from @favDT F
left outer join Buddies B on B.Reg_ID = F.favorite_id and b.favorite_id = @regID
inner join registration R on R.Reg_ID = F.favorite_id
inner join Images I on R.Default_Image = I.Image_ID
where B.Reg_ID is null
)
ORDER BY FavID