我想展示在同一天至少購買 2 次的客戶案例。但我只想計算在不同商店購買的商品。到目前為止，我有：
Select Purchase.PurClientId, Purchase.PurDate, Purchase.PurId
from Purchase
join
(
Select count(Purchase.PurId),
Purchase.PurClientId,
to_date(Purchase.PurDate)
from Purchases
group by Purchase.PurClientId,
to_date(Purchase.PurDate)
having count (Purchase.PurId) >=2
) k
on k.PurClientId=Purchase.PurClientId
但我不知道如何計算只有在不同商店生產的購買。允許識別商店的列是Purchase.PurShopId. 感謝幫助！
uj5u.com熱心網友回復：
您可以使用：
SELECT PurId,
PurDate,
PurClientId,
PurShopId
FROM (
SELECT p.*,
COUNT(DISTINCT PurShopId) OVER (
PARTITION BY PurClientId, TRUNC(PurDate)
) AS num_stores
FROM Purchase p
)
WHERE num_stores >= 2;
或者
SELECT *
FROM Purchase p
WHERE EXISTS(
SELECT 1
FROM Purchase x
WHERE p.purclientid = x.purclientid
AND p.purshopid != x.purshopid
AND TRUNC(p.purdate) = TRUNC(x.purdate)
);
其中，對于樣本資料：
CREATE TABLE purchase (
purid PRIMARY KEY,
purdate,
purclientid,
PurShopId
) AS
SELECT 1, DATE '2021-01-01', 1, 1 FROM DUAL UNION ALL
SELECT 2, DATE '2021-01-02', 1, 1 FROM DUAL UNION ALL
SELECT 3, DATE '2021-01-02', 1, 2 FROM DUAL UNION ALL
SELECT 4, DATE '2021-01-03', 1, 1 FROM DUAL UNION ALL
SELECT 5, DATE '2021-01-03', 1, 1 FROM DUAL UNION ALL
SELECT 6, DATE '2021-01-04', 1, 2 FROM DUAL;
兩個輸出：
普瑞德 普拉達 客戶 ID 專賣店 2 2021-01-02 00:00:00 1 1 3 2021-01-02 00:00:00 1 2
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/371574.html
上一篇：對于長度超過32512個字符的字串，PLSQLDeveloper中的dbms_output.put_line失敗并顯示ORA-06502
下一篇：不能使用區域函式。不合法的識別符