UNION & ORDER две таблицы внутри общего выражения таблицы
У меня есть CTE внутри хранимой процедуры SQL, которая UNION
значения из двух баз данных - это номера клиентов и дата последнего заказа этого клиента.
Вот оригинальный SQL -
;WITH CTE_last_order_date AS
(
SELECT c1.customer ,MAX(s2.dt_created) AS last_order_date
FROM customers c1 WITH (NOLOCK)
LEFT JOIN archive_orders s2 WITH (NOLOCK)
ON c1.customer = s2.customer
GROUP BY c1.customer
UNION ALL
SELECT c1.customer ,MAX(s1.dt_created) AS last_order_date
FROM customers c1 WITH (NOLOCK)
LEFT JOIN orders s1 WITH (NOLOCK)
ON c1.customer = s1.customer
GROUP BY c1.customer
)
Пример результатов:
customer, last_order_date
CF122595, 2011-11-15 15:30:22.000
CF122595, 2016-08-15 10:01:51.230
(2 row(s) affected)
Это, очевидно, не относится к UNION
Правило различных записей, потому что значения даты не совпадают, то есть SQL возвращал максимальное значение из обеих таблиц (т. е. окончательный набор записей не был отличным)
Чтобы попытаться обойти это, я попробовал другой метод, заимствованный из этого вопроса, и реализовал группировку:
;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
ORDER BY MIN('group'), customer
)
Пример результатов:
customer, last_order_date
CF122595, 2016-08-15 10:01:51.230
(1 row(s) affected)
У этого было различие (ха), работающее хорошо, вплоть до того, чтобы стучать в правило, которое предотвращает ORDER BY
внутри выражений общих таблиц, которые необходимы для того, чтобы выбрать самую низкую группу (что подразумевает прямые заказы (группа 1), чья дата должна иметь приоритет над архивом (группа 2)).
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Все помощь или идеи приветствуются.
4 ответа
Вместо того, чтобы группировать, затем объединять, затем снова группировать, почему бы не объединить таблицы заказов и работать оттуда:
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
)
дающий 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
Я бы не стал вкладывать 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
Альтернативным подходом может быть получение клиента только из архивной таблицы, в которой у нас нет текущей. Что-то вроде:
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)
)
Если объединить все возможные строки вместе, а затем вычислить 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