比SQL中的AND運算子有效的查詢

我正在使用 oracle 資料庫。以下查詢未按預期作業。

select * 
from cst_cust_attributes 
where attribute_value='event' 
and attribute_value='reg'
and attribute_value != 'guest';

我需要所有在“事件”和“注冊”中都只有屬性值的客戶資料。而不是在“客人”中。但我得到了正確的輸出。記錄由具有所有 3 個屬性值的客戶資料混合而成。

下面是表格的結構

Name            Null?    Type          
--------------- -------- ------------- 
ORGANIZATION_ID NOT NULL NUMBER(19)    
CUST_ID         NOT NULL VARCHAR2(32)  
ATTRIBUTE_ID    NOT NULL NUMBER(19)    
ATTRIBUTE_SEQ   NOT NULL NUMBER(10)    
ATTRIBUTE_VALUE NOT NULL VARCHAR2(254) 
ACTIVE_FLAG     NOT NULL NUMBER(3)     
CREATE_DATE              DATE          
CREATE_USER              VARCHAR2(254) 
UPDATE_DATE              DATE          
UPDATE_USER              VARCHAR2(254) 

uj5u.com熱心網友回復：

您可以使用COUNT分析函式和條件聚合：

SELECT *
FROM   (
  SELECT a.*,
         COUNT(CASE attribute_value WHEN 'event' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_event,
         COUNT(CASE attribute_value WHEN 'reg' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_reg,
         COUNT(CASE attribute_value WHEN 'guest' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_guest 
  FROM   cst_cust_attributes a
)
WHERE  num_event > 0
AND    num_reg   > 0
AND    num_guest = 0;

其中，對于樣本資料：

INSERT INTO cst_cust_attributes (
  organization_id,
  cust_id,
  attribute_id,
  attribute_seq,
  attribute_value,
  active_flag
)
SELECT 1, 'C1', 1, 1, 'event', 1 FROM DUAL UNION ALL
SELECT 1, 'C1', 2, 1, 'reg',   1 FROM DUAL UNION ALL
SELECT 1, 'C1', 3, 1, 'guest', 1 FROM DUAL UNION ALL
SELECT 1, 'C2', 1, 1, 'event', 1 FROM DUAL UNION ALL
SELECT 1, 'C2', 2, 1, 'reg',   1 FROM DUAL UNION ALL
SELECT 1, 'C3', 1, 1, 'event', 1 FROM DUAL;

輸出：

ORGANIZATION_ID	CUST_ID	ATTRIBUTE_ID	ATTRIBUTE_SEQ	ATTRIBUTE_VALUE	ACTIVE_FLAG	CREATE_DATE	創建用戶	更新日期	UPDATE_USER	NUM_EVENT	NUM_REG	NUM_GUEST
1	C2	1	1	事件	1	空值	空值	空值	空值	1	1	0
1	C2	2	1	注冊	1	空值	空值	空值	空值	1	1	0

你能告訴我在哪里添加以下條件和trunc(CREATE_DATE) >='05-MAY-2019' AND trunc(CREATE_DATE)<='13-APR-2022' group by cust_id;

如果您嘗試回傳所有行，則添加 aGROUP BY沒有意義。

至于日期范圍，這取決于您是否要檢查：

• eventand regand notguest值僅在該范圍內；或者
• 在該日期范圍內或之外存在任何event和reg非guest行，然后回傳該范圍內的行。

對于前者，您將WHERE在子查詢中添加一個子句：

SELECT *
FROM   (
  SELECT a.*,
         COUNT(CASE attribute_value WHEN 'event' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_event,
         COUNT(CASE attribute_value WHEN 'reg' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_reg,
         COUNT(CASE attribute_value WHEN 'guest' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_guest 
  FROM   cst_cust_attributes a
  WHERE  create_date >= DATE '2019-05-05'
  AND    create_date <  DATE '2022-04-13'   INTERVAL '1' DAY
)
WHERE  num_event > 0
AND    num_reg   > 0
AND    num_guest = 0;

對于后者，過濾器將被添加到外部查詢中：

SELECT *
FROM   (
  SELECT a.*,
         COUNT(CASE attribute_value WHEN 'event' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_event,
         COUNT(CASE attribute_value WHEN 'reg' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_reg,
         COUNT(CASE attribute_value WHEN 'guest' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_guest 
  FROM   cst_cust_attributes a
)
WHERE  num_event > 0
AND    num_reg   > 0
AND    num_guest = 0
AND    create_date >= DATE '2019-05-05'
AND    create_date <  DATE '2022-04-13'   INTERVAL '1' DAY;

其中，對于樣本資料：

INSERT INTO cst_cust_attributes (
  organization_id,
  cust_id,
  attribute_id,
  attribute_seq,
  attribute_value,
  active_flag,
  create_date
)
SELECT 1, 'C1', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C1', 2, 1, 'reg',   1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C1', 3, 1, 'guest', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C2', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C2', 2, 1, 'reg',   1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C3', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C4', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C4', 2, 1, 'reg',   1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C4', 3, 1, 'guest', 1, DATE '2018-01-01' FROM DUAL;

然后前一個查詢回傳C2andC4行，后一個查詢只回傳C2行（因為reg值存在C4但它在日期范圍之外）。

db<>在這里擺弄

我想使用連接MAIL_ID從另一個表cst_mail加入列。參考對應maild id的cust_id

就像是：

SELECT a.*,
       m.mail_id
FROM   (
         SELECT a.*,
                COUNT(CASE attribute_value WHEN 'event' THEN 1 END)
                  OVER (PARTITION BY cust_id) AS num_event,
                COUNT(CASE attribute_value WHEN 'reg' THEN 1 END)
                  OVER (PARTITION BY cust_id) AS num_reg,
                COUNT(CASE attribute_value WHEN 'guest' THEN 1 END)
                  OVER (PARTITION BY cust_id) AS num_guest 
         FROM   cst_cust_attributes a
         WHERE  create_date >= DATE '2019-05-05'
         AND    create_date <  DATE '2022-04-13'   INTERVAL '1' DAY
       )
       JOIN cst_mail m
       ON (a.cust_id = m.cust_id)
WHERE  num_event > 0
AND    num_reg   > 0
AND    num_guest = 0;

uj5u.com熱心網友回復：

您需要使用如下查詢，該查詢使用存在來檢查所有條件

select * 
from cst_cust_attributes c
left join cst_mail n on c.CUST_ID   =n.CUST_ID   
where c.attribute_value='event' 
and exists 
( 
select 1 from cst_cust_attributes b where c.CUST_ID   = b.CUST_ID  
and b.attribute_value='reg'
)
and not exists
(
select 1 from cst_cust_attributes a where c.CUST_ID   = a.CUST_ID  
and a.attribute_value = 'guest'
)

uj5u.com熱心網友回復：

所以你基本上只是希望 attribute_value 是 "event" 和 "reg" ？那么為什么不使用以下選擇呢？

select * from cst_cust_attributes where attribute_value in('event','reg');

標籤：sql 甲骨文

上一篇：尋找可以按功能提供像MongoDB這樣的輸出的Postgres查詢

下一篇：如何在SQL中僅獲取分隔符之前的第一個值？[復制]