计算表中重复行的数量

显示全部楼层 · 2008-12-24 12:00:59

table test:
id
1
1
1
2
2
3
select出总共有多少行，distinct的数量，重复的行数。
谢谢！！

千问 · 2008-12-24 12:00:59

SQL> select * from tmp_3;
ID
----------
1
1
1
2
2
3
6 rows selected
SQL>
SQL> select count(id)"总共有多少行",
2 count(distinct id) "distinct的数量",
3 count(id) - count(distinct id) "重复的行数"
4from tmp_3;
总共有多少行 distinct的数量重复的行数
------------ -------------- ----------
6
3
3
SQL>

[ 本帖最后由 bell6248 于 2008-3-26 14:28 编辑 ]

千问 · 2008-12-24 12:00:59

总共6行，distinct的数据为3，重复的行数为2

千问 · 2008-12-24 12:00:59

SQL>select * from t_tempa;
ID
----------
1
1
1
2
2
3
===
SQL> select distinct a.id content,b.counts duptimes f
rom t_tempa a,(select count(id) counts,id from t_tempa group by id) bwhere b.c
ounts>1 and a.id=b.id;
CONTENT DUPTIMES
---------- ----------
1
3
2
2

千问 · 2008-12-24 12:00:59

WITH A AS (
select 1 as no from dual
union all
select 1 as no from dual
union all
select 1 as no from dual
union all
select 2 as no from dual
union all
select 2 as no from dual
union all
select 3 as no from dual
)
select count(no)"总共有多少行",
count(distinct no) "distinct的数量",
count(no) - count(distinct no) "重复的行数"
from A;

千问 · 2008-12-24 12:00:59

count(*)||count(distinct)||重复的行数：select count(*),count(distinct id) ,(count(*)-count(distinct id))*2 from test;

千问 · 2008-12-24 12:00:59

原帖由 bitiwyh 于 2008-3-26 14:40 发表
SQL>select * from t_tempa;
ID
----------
1
1
1
2
2
3
===
SQL> select distinct a.id content,b.counts duptimes f
rom t_tempa a,(select count(id) counts,id from t_tempa group by id) bwhere b.c
ounts>1 and a.id=b.id;
CONTENT DUPTIMES
---------- ----------
1
3
2
2

千问 · 2008-12-24 12:00:59

沉默

千问 · 2008-12-24 12:00:59

4楼的更简单的写法：
select id, count(*) duplicate from t group by id having count(*)>1
[ 本帖最后由 lastwinner 于 2008-3-27 01:32 编辑 ]

千问 · 2008-12-24 12:00:59

原帖由 bell6248 于 2008-3-26 14:26 发表

SQL> select * from tmp_3;
ID
----------
1
1
1
2
2
3
6 rows selected
SQL>
SQL> select count(id)"总共有多少行",
2 count(distinct id) "distinct的数量",
3 count(id) - count(distinct id) "重复的行数"
4from tmp_3;
总共有多少行 distinct的数量重复的行数
------------ -------------- ----------
6
3
3
SQL>