内部联接和联合使用order by

I have this query:

我有这个问题:

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

i need to order by the F.favorite_id but i keep getting incorrect syntax near the keyword union all

我需要通过F.favorite_id订购,但我一直在关键字union all附近得到错误的语法

2 个解决方案

#1

When you use UNION, you can't order the individual queries and expect it to return in the order you want. But you can add an order by at the end. In your case, you are trying to ORDER BY a column that you are not selecting.

当您使用UNION时,您无法对各个查询进行排序,并希望它按您想要的顺序返回。但您可以在最后添加订单。在您的情况下,您尝试按顺序排列您未选择的列。

You can simply add favorite_id to each select, then add an outer query that just returns your two desired columns but order by favorite_id:

您只需将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

#2

In Union All construction you cannot order every statement independently. You must put ORDER BY clause at the end of complete query.

在Union All构造中,您无法单独订购每个声明。您必须在完整查询结束时放置ORDER BY子句。

Here is detailed explanation:

以下是详细说明:

ORDER BY in UNION ALL

UNION ALL中的ORDER BY

So, you must first include F.favourite_id column in your SELECT lists, and then it will be visible in last ORDER BY clause.

因此,您必须首先在SELECT列表中包含F.favourite_id列,然后它将在最后一个ORDER BY子句中可见。

#1