sql - 此声明更短/排序

Question

我可以把这个语句写得更短吗？

Select S_name 
from 
  Supplier
  JOIN Nation ON Supplier.S_nationkey = Nation.N_nationkey 
  JOIN Region on Nation.n_regionkey = region.R_regionkey 
Where
  Region.r_name = 'AFRICA' 
Union
Select C_name
from 
  Customer 
  JOIN Nation ON Customer.C_Nationkey = Nation.N_nationkey 
  JOIN Region on Nation.N_regionkey = Region.R_regionkey 
Where
  Region.R_name = 'AFRICA'

我想按名称对我的输出进行排序，但我不知道为什么，因为我确实有 C_name 和 S_Name 作为输出？!

谢谢

Answer 1

如果您想要同一列中的所有数据，那么您可以在其周围放置一个SELECT，然后执行一个ORDER BY。

    Select S_name As Names
    from Supplier 
    JOIN Nation 
        ON Supplier.S_nationkey = Nation.N_nationkey 
    JOIN Region 
        on Nation.n_regionkey = region.R_regionkey 
    Where Region.r_name = 'AFRICA' 
    Union
    Select C_name As Names
    from Customer 
    JOIN Nation 
        ON Customer.C_Nationkey = Nation.N_nationkey 
    JOIN Region 
        on Nation.N_regionkey = Region.R_regionkey 
    Where Region.R_name = 'AFRICA'
    ORDER BY Names

如果您不需要同一列中的数据，那么您可以这样做:

Select S_name, c.C_name
from Supplier 
JOIN Nation 
    ON Supplier.S_nationkey = Nation.N_nationkey 
JOIN Region 
    on Nation.n_regionkey = region.R_regionkey 
JOIN Customer c
    on Nation.N_nationkey = c.C_Nationkey
Where Region.r_name = 'AFRICA' 
ORDER BY S_name, c.c_name