我有這樣的表：
customer_id date order_id
12 1/11/2021 2
12 22/11/2021 3
12 31/11/2021 5
42 1/11/2021 2
42 15/11/2021 2
42 31/11/2021 2
43 22/11/2021 1
43 25/11/2021 2
也門 我只想選擇customer_id他們第一次和最后一次購買之間的 30 天，然后加入產品表，這意味著：
customer_id date order_id Product_name
12 1/11/2021 2 apple
12 22/11/2021 3 car
12 31/11/2021 5 orange
42 1/11/2021 2 apple
42 15/11/2021 2 apple
42 31/11/2021 2 apple
例如：
select customer_id, date, order_id, product_name
left join product on order_id = product_id
where customer_id.max(date) - customer_id.min(date) = 30
uj5u.com熱心網友回復：
選擇符合條件的原始行
select customer_id, date, order_id
from
(select customer_id, date, order_id
, max(date) over (partition by customer_id) dmax
, min(date) over (partition by customer_id) dmin
from myTable
) t
where date in (dmax, dmin) and datediff(day, dmin, dmax) = 30
并加入輸出和Productsorder_id = product_id
select s.*, p.product_name
from (
select customer_id, date, order_id
from
(select customer_id, date, order_id
, max(date) over (partition by customer_id) dmax
, min(date) over (partition by customer_id) dmin
from myTable
) t
where date in (dmax, dmin) and datediff(day, dmin, dmax) = 30
) s
join Product p on s.order_id = p.product_id;
uj5u.com熱心網友回復：
回答：
如果我對您的理解正確并且您只想選擇客戶 ID，則使用GROUP BYwithHAVING和的陳述句DATEDIFF()是一個選項：
SELECT customer_id
FROM (VALUES
(12, CONVERT(date, '20211101'), 2),
(12, CONVERT(date, '20211122'), 3),
(12, CONVERT(date, '20211130'), 5),
(42, CONVERT(date, '20211101'), 2),
(42, CONVERT(date, '20211231'), 2),
(43, CONVERT(date, '20211122'), 1)
) t (customer_id, date, order_id)
GROUP BY customer_id
HAVING DATEDIFF(day, MIN(date), MAX(date)) = 30
結果：
customer_id
12
43
更新：
看來，您需要訂單資訊，而不是客戶 ID：
SELECT *
FROM Orders o
JOIN Products p ON o.order_id = p.product_id
WHERE EXISTS (
SELECT 1
FROM Orders
WHERE customer_id = o.customer_id
GROUP BY customer_id
HAVING DATEDIFF(day, MIN(date), MAX(date)) = 30
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/407737.html
標籤：