我正在對一個表進行查詢，我有興趣將重復的合同編號帶到其中，為此我使用磁區依據，到目前為止一切都很好。但是，我要求您只給我帶來那些在“CAMPO”欄位中滿足值組合的合同號，SMTP_ADDR 和 TEL_NUMBER 并且這些值在合同號中并不相同，如圖所示。記錄 1 和 2 不應輸出，而 3 和 4 是您希望它們輸出的方式。
我的查詢是這樣的：
with temp as
(
select
gral.contrato,
gral.campo,
count(*) over (partition by gral.contrato) as counter
from CAM_TBL_ALERTA_GRAL gral
WHERE
FECHA_MODIFICACION >'01/07/2021'
AND
(CAMPO='SMTP_ADDR')
OR CAMPO='TEL_NUMBER'
)
select contrato,campo,counter
from temp
where counter >= 2
uj5u.com熱心網友回復：
您可以使用 DISTINCT 關鍵字和 COUNT 函式來獲得所需的結果 -
with temp as (select gral.contrato,
gral.campo,
count(CAMPO) OVER (partition by gral.contrato, campo) as counter
count(DISTINCT CAMPO) OVER (partition by gral.contrato, campo) as dist_counter
from CAM_TBL_ALERTA_GRAL gral
WHERE FECHA_MODIFICACION >'01/07/2021'
)
SELECT contrato,campo
FROM temp
WHERE counter >= 2
AND dist_counter = 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/426884.html