SQL按不同表的多列排序

Question

我有两张桌子:第一张有栏：

|Name | Company | Job|

的值，例如

(jack apple developer),
(Julian google developer),
(Jim eBay developer),
(jack apple janitor),
(Julian google janitor),
(Jim eBay janitor),
(jack apple manager),
(Julian google manager),
(Jim eBay manager) 

第二个表由名称的排序顺序和带有列的作业组成。

|FilterVal | Name_Sort_Order | Job_Sort_Order|** and Values


(jack,2, NULL),
(Julian, 1, NULL),
(Jim, 3, NULL),
(Janitor, NULL, 1),
(Manager, NULL, 3),
(Developer, NULL, 2)

如何按照第二个表的name_sort_order和job_sort_order对第一个表进行排序？希望得到的结果：

(Julian google janitor),
(Julian google developer),
(Julian google manager),
(jack apple janitor),
(jack apple developer),
(jack apple manager),
(Jim eBay janitor),
(Jim eBay developer),
(Jim eBay manager)

Answer 1

嗯嗯。。。一个方法在order by中使用子查询。

select t1.*
from table1 t1 
order by (select t2.name_sort_order
          from table2 t2
          where t2.filterval = t.name and t2.name_sort_order is not null
         ),
         (select t2.job_sort_order
          from table2 t2
          where t2.filterval = t.job and t2.job_sort_order is not null
         );

Answer 2

您可以连接第二个表两次(基于列'FilterVal')，然后再按顺序排序。这里的关键是对第二张表进行混叠。

WITH A AS(
        SELECT 'jack' Name, 'apple' Company, 'Developer' Job FROM DUAL
        UNION ALL
        SELECT 'Julian' Name, 'google' Company, 'Developer' Job FROM DUAL
        UNION ALL
        SELECT 'Jim' Name, 'eBay' Company, 'Developer' Job FROM DUAL
        UNION ALL
        SELECT 'jack' Name, 'apple' Company, 'Janitor' Job FROM DUAL
        UNION ALL
        SELECT 'Julian' Name, 'google' Company, 'Janitor' Job FROM DUAL
        UNION ALL
        SELECT 'Jim' Name, 'eBay' Company, 'Janitor' Job FROM DUAL
        UNION ALL
        SELECT 'jack' Name, 'apple' Company, 'Manager' Job FROM DUAL
        UNION ALL
        SELECT 'Julian' Name, 'google' Company, 'Manager' Job FROM DUAL
        UNION ALL
        SELECT 'Jim' Name, 'ebay' Company, 'Manager' Job FROM DUAL
        ) , B AS (
        SELECT 'jack' FilterVal, 2 NameSortOrder, NULL Job_Sort_Order FROM DUAL
        UNION ALL
        SELECT 'Julian' FilterVal, 1 NameSortOrder, NULL Job_Sort_Order FROM DUAL
        UNION ALL
        SELECT 'Jim' FilterVal, 3 NameSortOrder, NULL Job_Sort_Order FROM DUAL
        UNION ALL
        SELECT 'Janitor' FilterVal, NULL NameSortOrder, 1 Job_Sort_Order FROM DUAL
        UNION ALL
        SELECT 'Manager' FilterVal, NULL NameSortOrder, 3 Job_Sort_Order FROM DUAL
        UNION ALL
        SELECT 'Developer' FilterVal, NULL NameSortOrder, 2 Job_Sort_Order FROM DUAL
)
SELECT
        A.Name
        ,A.Company
        ,A.Job
FROM
    A LEFT OUTER JOIN B ON A.Name = B.FilterVal AND B.NameSortOrder IS NOT NULL
    LEFT OUTER JOIN B AS BB ON A.Job = BB.FilterVal AND BB.Job_Sort_Order IS NOT NULL
ORDER BY
    B.NameSortOrder, BB.Job_Sort_Order;