我必須從客戶表中獲取記錄，其中客戶禮品型別只是“現金”，但這些客戶不應該簽署“優惠券”和“折扣”。下面是我的查詢，它沒有提供確切的記錄。還包括 1 個以上的gift_type。我也試過使用子查詢，還是不行。
select cust_id from customer where gift_type='cash' and gift_type != 'coupon' and gift_type != 'discount';
&
select cust_id
from customer c1, customer c2
where c1.gift_type='cash' and c2.gift_type != 'coupon' and c2.gift_type != 'discount';
在此先感謝您的幫助。
uj5u.com熱心網友回復：
select c.*
from customer as c
where c.gift_type='cash'
and not exists
(
select 1 from customer as x
where c.customer_id=x.customer_id
and x.gift_type in('coupon','discount')
)
希望以上內容適合你
uj5u.com熱心網友回復：
您可以使用以下方法在單個表掃描中執行此操作：
SELECT *
FROM (
SELECT c.*,
COUNT(CASE WHEN gift_type IN ('coupon', 'discount') THEN 1 END)
OVER (PARTITION BY customer_id) AS num_invalid
FROM customer c
) c
WHERE gift_type = 'cash'
AND num_invalid = 0;
其中，對于樣本資料：
CREATE TABLE customer (customer_id INT, gift_type VARCHAR(10));
INSERT INTO customer(customer_id, gift_type)
SELECT 1, 'cash' FROM DUAL UNION ALL
SELECT 1, 'coupon' FROM DUAL UNION ALL
SELECT 2, 'cash' FROM DUAL UNION ALL
SELECT 2, 'discount' FROM DUAL UNION ALL
SELECT 3, 'cash' FROM DUAL;
輸出：
顧客ID GIFT_TYPE NUM_INVALID 3 現金 0
Oracle資料庫<>小提琴MySQL資料庫<>小提琴
uj5u.com熱心網友回復：
您可能想要使用not in或not exists，因為您想要所有禮品型別為現金的客戶，但同時這些相同的客戶不能存在于其他禮品型別的表中。
選項1
select * from customer a
where a.gift_type = 'cash' and
not exists
( select 1 from customer c where a.customer_id = c.customer_id and c.gift_type in ('coupon' , 'discount' )
);
選項 2
select * from customer a
where a.gift_type = 'cash' and
a.customer_id not in
( select c.customer_id from customer c where a.customer_id = c.customer_id and
c.gift_type in ('coupon' , 'discount' )
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/346471.html
下一篇：MySQL查詢不檢索不同的值