СОЮЗ & amp; ЗАКАЗАТЬ две таблицы внутри общего выражения таблицы

Вместо того, чтобы группировать, затем объединять, затем снова группировать, почему бы не объединить таблицы заказов и не работать оттуда:

SELECT c1.customer ,MAX(s2.dt_created) AS last_order_date
FROM customers c1
INNER JOIN (select customer, dt_created from archive_orders
union all select customer, dt_created from orders) s2
ON c1.customer = s2.customer
GROUP BY c1.customer

Помните, что в SQL ваша задача - сообщить системе , что вы хотите , а не , какие шаги / процедуры нужно выполнить, чтобы получить эти результаты . Вышеприведенное, логически, описывает то, что мы хотим - мы хотим, чтобы дата последнего заказа от заказов каждого клиента, и нас не волнует, был ли это заархивированный заказ или неархивированный.

Так как мы собираемся уменьшить информацию о заказе до одной строки (для каждого клиента) во время поведения GROUP BY, нам также не нужно UNION для удаления дубликатов, чтобы Я переключился на UNION ALL.

(Признаюсь, я не мог реально увидеть, что ORDER BY должен был добавить к миксу в этот момент, поэтому я не пытался включить его сюда. Если это входит в CTE, то подумайте на том факте, что CTE, как и таблицы и представления, не имеют присущего порядка . Единственное предложение ORDER BY, которое влияет на порядок строк результатов, - это то, которое применяется к внешнему / окончательному SELECT) [ 1117]

Дать orders приоритет над archived_orders:

;With CTE1 as (
    SELECT c1.customer,group,MAX(s2.dt_created) as MaxInGroup
    FROM customers c1
    INNER JOIN (select customer, dt_created,2 as group from archive_orders
    union all select customer, dt_created,1 from orders) s2
    ON c1.customer = s2.customer
    GROUP BY c1.customer,group
), CTE2 as (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY customer ORDER BY group) as rn
    from CTE2
)
select * from CTE2 where rn = 1

Альтернативным подходом может быть получение клиента только из архивной таблицы, где у нас нет текущей. Что-то вроде:

WITH CurrentLastOrders(customer, last_order_date) AS    -- Get current last orders
(
    SELECT o.customer, max(o.dt_created) AS last_order_date
    FROM orders s WITH (NOLOCK) ON c.customer = o.customer
    GROUP BY o.customer
),
ArchiveLastOrders(customer, last_order_date) AS -- Get archived last orders where customer does not have a current order
(
    SELECT o.customer, max(o.dt_created) AS last_order_date
    FROM archive_orders o WITH (NOLOCK)
    WHERE NOT EXISTS ( SELECT *
                        FROM CurrentLastOrders lo
                        WHERE o.customer = lo.customer)
    GROUP BY o.customer
),
AllLastOrders(customer, last_order_date) AS -- All customers with orders
(
    SELECT customer, last_order_date
    FROM CurrentLastOrders
    UNION ALL
    SELECT customer, last_order_date
    FROM ArchiveLastOrders
)
AllLastOrdersPlusCustomersWithNoOrders(customer, last_order_date) AS    -- All customerswith latest order if they have one
(
    SELECT customer, last_order_date
    FROM AllLastOrders
    UNION ALL
    SELECT customer, null
    FROM customers c WITH (NOLOCK)
    WHERE NOT EXISTS ( SELECT *
                        FROM AllLastOrders lo
                        WHERE c.customer = lo.customer)
)

Я бы не пытался вкладывать SQL для достижения определенного набора результатов, это та же логика группировки по клиенту в обоих объединенных запросах. Если вам нужен отдельный упорядоченный набор, вы можете сделать это вне CTE

Как насчет:

;WITH CTE_last_order_date AS
(
   SELECT c1.customer ,s2.dt_created AS last_order_date, '2' AS 'group'
   FROM customers c1 WITH (NOLOCK)
   LEFT JOIN archive_orders s2 WITH (NOLOCK) ON c1.customer = s2.customer

   UNION ALL

   SELECT c1.customer ,s1.dt_created AS last_order_date, '1' AS 'group'
   FROM customers c1 WITH (NOLOCK)
   LEFT JOIN orders s1 WITH (NOLOCK) ON c1.customer = s1.customer

)
SELECT customer, MAX(last_order_date)
FROM CTE_last_order_date
GROUP BY customer 
ORDER BY MIN('group'), customer

если объединить все возможные строки вместе, а затем вычислить row_number, разделить на клиентов и упорядочить по «group», а затем по убыванию last_order_date, то вы можете выбрать все строки = 1, чтобы получить «top 1» для каждого клиента

;WITH CTE_last_order_date AS
(
SELECT max(last_order_date) as 'last_order_date', customer
FROM (
SELECT distinct cust.customer, max(s2.dt_created) AS last_order_date, '2' AS 'group'
FROM customers c1 WITH (NOLOCK)

LEFT JOIN archive_orders s2 WITH (NOLOCK)
ON c1.customer = s2.customer

GROUP BY c1.customer

UNION 

SELECT distinct c1.customer, max(sord.dt_created) AS last_order_date, '1' AS 'group'
FROM customers c1 WITH (NOLOCK)

LEFT JOIN orders s1 WITH (NOLOCK)
ON cust.customer = sord.customer

GROUP BY
   c1.customer
   ) AS t
GROUP  BY customer

)
,   --row_number below is 'per customer' and can be used to make rn=1 the top 1 for each customerid
ROWN AS (SELECT Customer,last_order_date,[group], row_number() OVER(partition by customer order by [group] ASC, sord.dt_created DESC) AS RN)
SELECT * FROM Rown WHERE Rown.rn = 1