SQL joint query | SQL cross query | the order by clause is invalid in views, nested functions, derived tables, and subqueries unless top is specified at the same time.

Select * from (
Select shengname, ID as Sid, 1 as ord from szsheng
Union all
Select shiname, shengid, 2 as ord from szshi)
Order by A. Sid ASC, ord ASC

You can get the above results, but directly use this statement to manually create a view in the T-SQL without a prompt
The order by clause is invalid in views, nested functions, derived tables, and subqueries unless top is specified at the same time.

Put this statement in the Enterprise Manager to correct the error. The correct statement is as follows:
Select Top 100 percent *
From (select shengname, ID as Sid, 1 as ORD
From szsheng
Union
Select shiname, shengid, 2 as ORD
From szshi)
Order by SID, ORD

The following table structure and expected results

 

Table 1
1 Beijing
2 Shanghai
3 Guangdong

Table 2
1 Beijing zone 1
2 Beijing Zone 2
3 Shanghai zone 1
4 Shanghai Zone 2
5 Guangzhou
6 Shenzhen

Then you want the query result to be as follows:
Beijing
Beijing zone 1
Beijing Region 2
Shanghai
Shanghai zone 1
Shanghai Region 2
Guangdong
Guangzhou
Shenzhen