我有 2 張表客戶和訂單。
第一個問題：
這是客戶的主表，其中包含客戶編號、客戶名稱、活動標志等幾列。表可能包含相同客戶編號的 2 條或更多記錄，但根據業務邏輯，理想情況下一次只有 1 條記錄積極點。我需要找到只有 1 條記錄并且應該處于活動狀態的客戶。
我寫的查詢：
select customer_number, count(*)
from customers c
where active = false
group by customer_number
having count(*) = 1;
這將回傳有 2 條記錄且只有 1 條未處于活動狀態的客戶。
問題2：
除了客戶表，我們還有另一個表是訂單表，它包含客戶編號（與客戶表中相同）、交貨日期、訂單編號、插入時間等列。我需要找到 ACTIVE 為 false 且自 180 天以來沒有下過任何訂單的客戶。（插入時間：：日期 - 180）。
我所嘗試的并沒有給我想要的輸出，因為在回溯測驗中我發現資料是錯誤的
select om.customer_number,
c.customer_name,
om.deliverydate,
om.insert_time
from customers c, order_master om
where
om.customer_number in
(
select c2.customer_number
from customers c2
where c2.active = false
group by c2.customer_number
having count(*) =1
)
and c.customer_number = om.customer_number
group by om.customer_number, c.customer_name,
om.deliverydate, om.insert_time
having max(om.insert_time::date) < '2022-06-01' ;
我嘗試過的查詢，我已經在我的問題中提到了它們。請檢查一下。
uj5u.com熱心網友回復：
對于第一個問題，找到只有 1 條記錄并且應該是 active 的客戶，您可以使用條件聚合或過濾計數，如下所示：
select customer_number
from Customers c
group by customer_number
having count(*) = 1 and count(*) filter (where active) = 1;
對于第二個問題，找到 ACTIVE 為 false 且自 180 天以來沒有下過任何訂單的客戶，嘗試以下操作：
select cu.customer_number
from order_master om join
(
select customer_number
from Customers c
group by customer_number
having count(*) filter (where active) = 0
) cu
on om.customer_number = cu.customer_number
group by cu.customer_number
having max(om.insert_time) < current_date - interval '180 day'
查看演示。
如果要獲取非活動客戶的所有訂單詳細資訊，可以將上述查詢與訂單表連接起來，如下所示：
with inactive_cust as
(
select cu.customer_number, cu.customer_name
from order_master om join
(
select customer_number, customer_name
from Customers c
group by customer_number, customer_name
having count(*) filter (where active) = 0
) cu
on om.customer_number = cu.customer_number
group by cu.customer_number, cu.customer_name
having max(om.insert_time) < current_date - interval '180 day'
)
select c.customer_number, c.customer_name,
o.order_number, o.insert_time
from inactive_cust c join order_master o
on c.customer_number = o.customer_number
查看演示。
uj5u.com熱心網友回復：
@Ahmed-您的兩個查詢都很好。
但是在第二個查詢中，我想將其他資料提取到其中，所以我所做的是-
select om.customer_number, cu.customer_name, om.order_number ,om.insert_time
from order_master om join
(
select customer_number, customer_name
from Customers c
group by customer_number, customer_name
having count(*) filter (where active) = 0
) cu
on om.customer_number = cu.customer_number
group by om.customer_number , cu.customer_name, om.insert_time,om.order_number
having max(om.insert_time) < current_date - interval '180 day';
When I tried the query shared by you -
select om.customer_number
from order_master om join
(
select customer_number
from Customers c
group by customer_number
having count(*) filter (where active) = 0
) cu
on om.customer_number = cu.customer_number
group by om.customer_number
having max(om.insert_time) < current_date - interval '180 day';
它給了我大約 4K 的結果，當我嘗試修改時，在查詢中添加每一列后，結果計數呈指數增長，直到 75K 甚至更多。
它還向我展示了 max(om.insert_time) 遠大于 180 天的記錄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/531590.html