我有一張桌子daily_orders，我想在其中找到以與手機相同的順序購買手機套的客戶。這是表結構
CREATE TABLE daily_orders (
Order_date datetime,
orderid varchar(20) NOT NULL,
CustomerID int NOT NULL,
Product varchar(20) ,
Product_Category text,
Units int
);
-- insert some values
INSERT INTO daily_orders VALUES ('2021-06-15 20:06:31','AABS123',46162,'sku1223','mobile phone',1);
INSERT INTO daily_orders VALUES ('2021-07-01 13:21:22','AABWA23',87949,'sku213','laptops',1);
INSERT INTO daily_orders VALUES ('2021-06-11 07:01:25','AA12WA13',122123,'sku1223','mobile phone',1);
INSERT INTO daily_orders VALUES ('2021-06-11 07:01:25','AA12WA13',122123,'sku1223','mobile covers',2);
輸出應該是
CustomerID
122123
我試過的查詢
select a.customerID
from daily_orders a
inner join daily_orders b on a.CustomerID = b.CustomerID
where a.OrderID = b.OrderID and a.CustomerID = b.CustomerID
但我沒有得到想要的輸出。任何建議都會有所幫助。
uj5u.com熱心網友回復：
這是一個“是否存在”查詢，最好使用exists來完成。
另請注意，文本資料型別早已被棄用，不應再使用，請始終指定varchar(n)。
select o.CustomerID
from daily_orders o
where Product_Category = 'mobile phone'
and exists (
select * from daily_orders o2
where o2.orderid = o.orderid
and o2.Product_Category = 'mobile covers'
);
uj5u.com熱心網友回復：
您可以將表格與自身連接（一個實體用于封面，另一個用于手機）以獲得您想要的結果。例如：
select c.CustomerId
from daily_orders c
join daily_orders p on p.orderid = c.orderid
where c.Product_Category = 'mobile covers'
and p.Product_Category = 'mobile phone'
uj5u.com熱心網友回復：
假設orderidand Product_CategoryisVARCHAR和 not的資料型別，并且每個 sTEXT沒有不同的情況，首先過濾表以僅獲取購買了“手機封面”和/或“手機”的客戶，然后使用聚合僅獲取在同一訂單中購買兩者的客戶：CustomerIDorderid
SELECT DISTINCT CustomerID
FROM daily_orders
WHERE Product_Category IN ('mobile covers', 'mobile phone')
GROUP BY CustomerID, orderid
HAVING COUNT(DISTINCT Product_Category) = 2;
或與INTERSECT：
SELECT DISTINCT CustomerID
FROM (
SELECT CustomerID, orderid FROM daily_orders WHERE Product_Category = 'mobile covers'
INTERSECT
SELECT CustomerID, orderid FROM daily_orders WHERE Product_Category = 'mobile phone'
) t
請參閱演示。
uj5u.com熱心網友回復：
另一種選擇是使用 string_agg
select t.customerid
from ( select d.customerid,
string_agg(convert(varchar(100), d.Product_Category), ',') cat
from daily_orders d
group by d.customerid
) t
where t.cat = 'mobile phone,mobile covers'
DBFiddle在這里
uj5u.com熱心網友回復：
如果您將資料型別從 text 更改為 varchar，則以下查詢應該可以作業
表定義
CREATE TABLE daily_orders (
Order_date datetime,
orderid varchar(20) NOT NULL,
CustomerID int NOT NULL,
Product varchar(20),
Product_Category varchar(20),
Units int
);
解決方案
SELECT dor1.customerid,
dor1.product_category,
dor2.product_category
FROM daily_orders dor1
inner join daily_orders dor2
ON dor1.customerid = dor2.customerid
AND dor1.orderid = dor2.orderid
WHERE dor1.product_category <> dor2.product_category
AND dor1.product_category = 'mobile phone'
AND dor2.product_category = 'mobile covers';
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/449612.html