Блог

У меня есть две таблицы: в первой из них есть столбцы:

 |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)
 

Хммм . . . один метод использует подзапросы в 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
         );
 

Вы можете присоединиться ко второй таблице дважды (на основе столбца «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;