유니온으로 주문하는 방법

유니온으로 주문하는 방법

---

많은 선택에서 데이터를 가져올 때 주문할 수 있습니까?

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"

이 쿼리를 이름별로 어떻게 주문할 수 있습니까?

일부는 당신이 이런 식으로 쿼리 할 수 ​​있다고 말했습니다.

Select id,name,age
From Student
Where age < 15 or name like "%a%"
Order by name

그러나이 경우 나는 그 해결책을 무시합니다.

미리 감사드립니다.

---

그냥 써

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"
Order by name

order by는 전체 결과 집합에 적용됩니다

---

Select id,name,age
from
(
   Select id,name,age
   From Student
   Where age < 15
  Union
   Select id,name,age
   From Student
   Where Name like "%a%"
) results
order by name

---

정렬을 UNION의 첫 번째 명령문에만 적용하려면 UNION ALL을 사용하여 부속 선택에이를 정렬 할 수 있습니다 (둘 다 Oracle에서 필요로 표시됨).

Select id,name,age FROM 
(    
 Select id,name,age
 From Student
 Where age < 15
 Order by name
)
UNION ALL
Select id,name,age
From Student
Where Name like "%a%"

또는 (Nicholas Carey의 의견을 다루고 있습니다) 최상위 SELECT가 주문되고 결과가 아래쪽 SELECT 위에 다음과 같이 표시되도록 할 수 있습니다.

Select id,name,age, 1 as rowOrder
From Student
Where age < 15
UNION
Select id,name,age, 2 as rowOrder
From Student
Where Name like "%a%"
Order by rowOrder, name

---

다른 답변은 모두 정확하지만, 내가 붙어있는 곳은 별칭으로 주문해야한다는 것을 깨닫지 못하고 별칭이 두 선택 모두에 대해 동일한 지 확인해야한다는 점에 주목할 가치가 있다고 생각했습니다.

select 'foo'
union
select item as `foo`
from myTable
order by `foo`

notice that I'm using single quotes in the first select but backticks for the others.

That will get you the sorting you need.

---

Order By is applied after union, so just add an order by clause at the end of the statements:

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like '%a%'
Order By name

---

If I want the sort to be applied to only one of the UNION if use Union all:

Select id,name,age
From Student
Where age < 15
Union all
Select id,name,age
From 
(
Select id,name,age
From Student
Where Name like "%a%"
Order by name
)

---

As other answers stated , 'Order by' after LAST Union should apply to both datasets joined by union.

I was having two data sets but using different tables but same columns. 'Order by' after LAST Union didn't still worked. Using ALIAS for column used in 'order by' did the trick.

Select Name, Address for Employee 
Union
Select Customer_Name, Address from Customer
order by customer_name;   --Won't work

So solution is use Alias 'User_Name' :

Select Name as User_Name, Address for Employee 
Union
Select Customer_Name as User_Name, Address from Customer
order by User_Name; 

---

Can use this:

Select id,name,age
From Student
Where age < 15
Union ALL
SELECT * FROM (Select id,name,age
From Student
Where Name like "%a%")

참고URL : https://stackoverflow.com/questions/4715820/how-to-order-by-with-union