想用一條sql 搜出 no不重復的欄位數量 和 name不為空的數量
uj5u.com熱心網友回復：
是no欄位不重復的記錄數量 和 name欄位不為空的記錄數量uj5u.com熱心網友回復：
這樣吧
SELECT COUNT(*) FROM TABLE_NAME T WHERE T.NO NOT IN (SELECT NO FROM TABLE_NAME T1 GROUP BY NO HAVING COUNT(NO)>1) AND T.NAME IS NOT NULL
uj5u.com熱心網友回復：
不對啊大佬，我是要兩個欄位。。。。比如 select xxx as no欄位不重復的記錄數量，yyy as name欄位不為空的記錄數量 from tb1uj5u.com熱心網友回復：
select (2*count(distinct t.no)-count(t.no)) as not_repeat_no,count(all t.name) from table t;uj5u.com熱心網友回復：
select *
from table_name a
where a.no in (select b.no
from table_name b
where b.name is not null
group by b.no
having count(1) = 1);
uj5u.com熱心網友回復：
select count(distinct no) , count(name) from tableuj5u.com熱心網友回復：
select count(distinct no),sum(nvl(name,1) from tableuj5u.com熱心網友回復：
NO不重復，兩個2，name留哪個？uj5u.com熱心網友回復：
select count(1)
from (select t.*
from tab_test t
where t.name is not null
group by t.no
having count(1) = 1)
uj5u.com熱心網友回復：
SELECT COUNT(*) FROM TABLE_NAME T WHERE T.NO NOT IN (SELECT NO FROM TABLE_NAME T1 GROUP BY NO HAVING COUNT(NO)>1) AND T.NAME IS NOT NULLuj5u.com熱心網友回復：
寫兩個count的sql，然后用union鏈接，可以一起查出來uj5u.com熱心網友回復：
select count(distinct no) , count(name) from table
uj5u.com熱心網友回復：
with a as (select 1 as no,'a' name from dualunion all select 2,'b' from dual
union all select 3,'c' from dual
union all select 4,null from dual)
select distinct count(no) as count_no,count(name) from a
/
DISTINCT 自動回過濾掉 null值
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/90780.html
標籤：基礎和管理
上一篇：oracle 入門菜鳥求問個問題