我正在 2 個表之間進行連接，在那里他們給我帶來了有關系的 number_phone，我帶來了這些重復的次數，但是，我試圖為計數設定一個條件，以便它只列出那些重復的超過 4 次，我嘗試使用帳戶，它使我的計數器全部為空。值得一提的是，我并沒有占用 group by 進行計數，因為它給我帶來了錯誤的價值觀。
SELECT
REPLACE(REPLACE(t.id_contrato,'0999',''),'0998','')as contrato ,
t.num_telefono,
conc.valor_actual,
(
SELECT COUNT('x')
FROM TBL_TELEFONO ct
WHERE ct.num_telefono = t.num_telefono
AND ct.origen_tel='TELEFONO CONTRATO'
-- HAVING COUNT(*) > 4
) as counter
FROM TBL_TELEFONO t
INNER JOIN CAM_TBL_ALERTA_CONCENTRADO conc ON t.num_telefono = conc.valor_actual
WHERE id_contrato IS NOT NULL
AND id_contrato NOT IN ('N/A')
ORDER BY 4 DESC
如何僅列出重復 4 次以上的內容？
uj5u.com熱心網友回復：
我不喜歡在沒有示例資料的情況下除錯代碼，因此我將嘗試在 Scott 的示例EMP表上對其進行說明。“作業”就像您的“電話號碼”一樣。
SQL> select deptno, ename, job
2 from emp
3 order by job;
DEPTNO ENAME JOB
---------- ---------- ---------
20 SCOTT ANALYST --> 2 analysts
20 FORD ANALYST
10 MILLER CLERK --> 4 clerks
30 JAMES CLERK
20 SMITH CLERK
20 ADAMS CLERK
30 BLAKE MANAGER --> 3 managers
20 JONES MANAGER
10 CLARK MANAGER
10 KING PRESIDENT --> 1 president
30 TURNER SALESMAN --> 4 salesmen
30 MARTIN SALESMAN
30 WARD SALESMAN
30 ALLEN SALESMAN
14 rows selected.
SQL>
據此，我們想把所有的店員和推銷員都找來，因為他們有 4 個（或更多）。
代替count聚合函式，count以其決議形式使用：
SQL> select deptno, ename, job,
2 count(*) over (partition by job) cnt
3 from emp
4 order by job;
DEPTNO ENAME JOB CNT
---------- ---------- --------- ----------
20 SCOTT ANALYST 2
20 FORD ANALYST 2
10 MILLER CLERK 4
30 JAMES CLERK 4
20 SMITH CLERK 4
20 ADAMS CLERK 4
30 BLAKE MANAGER 3
20 JONES MANAGER 3
10 CLARK MANAGER 3
10 KING PRESIDENT 1
30 TURNER SALESMAN 4
30 MARTIN SALESMAN 4
30 WARD SALESMAN 4
30 ALLEN SALESMAN 4
14 rows selected.
SQL>
現在事情變得更容易了：將該查詢用作 CTE（或子查詢），并應用where子句：
SQL> with temp as
2 (select deptno, ename, job,
3 count(*) over (partition by job) cnt
4 from emp
5 )
6 select deptno, ename, job
7 from temp
8 where cnt >= 4
9 order by job;
DEPTNO ENAME JOB
---------- ---------- ---------
10 MILLER CLERK
30 JAMES CLERK
20 SMITH CLERK
20 ADAMS CLERK
30 TURNER SALESMAN
30 MARTIN SALESMAN
30 WARD SALESMAN
30 ALLEN SALESMAN
8 rows selected.
SQL>
應用于您的查詢（同樣，如果沒有任何示例資料，則無法對其進行測驗）：
with temp as
(select
replace(replace(t.id_contrato,'0999',''),'0998','')as contrato ,
t.num_telefono,
conc.valor_actual,
count(*) over (partition by t.num_telefono) as counter
from tbl_telefono t
inner join cam_tbl_alerta_concentrado conc on t.num_telefono = conc.valor_actual
where id_contrato is not null
and id_contrato not in ('N/A')
)
select contrato, num_telefono, valor_actual
from temp
where counter >= 4;
uj5u.com熱心網友回復：
將查詢包裝在另一個中以僅在 count > 4 處回傳
select *
from (
<your query, but without order by>
) x
where count > 4
order by count desc
uj5u.com熱心網友回復：
加入超過 4 個的選擇
SELECT
REPLACE(REPLACE(t.id_contrato,'0999',''),'0998','')as contrato ,
t.num_telefono,
conc.valor_actual,
ct.counter
FROM TBL_TELEFONO t
INNER JOIN (
SELECT num_telefono, COUNT(*) AS counter
FROM TBL_TELEFONO
WHERE origen_tel='TELEFONO CONTRATO'
GROUP BY num_telefono
HAVING COUNT(*) > 4
) ct
ON ct.num_telefono = t.num_telefono
INNER JOIN CAM_TBL_ALERTA_CONCENTRADO conc
ON t.num_telefono = conc.valor_actual
WHERE id_contrato IS NOT NULL
AND id_contrato NOT IN ('N/A')
ORDER BY ct.counter DESC
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/392770.html