sql查询语句计算重复数据个数

1、创建测试表，

create table test_count(id varchar2(20)， value varchar2(20))

2、插入测试数据

insert into test_count values(1, 1)

insert into test_count values(2, 1)

insert into test_count values(3, 1)

insert into test_count values(4, 2)

insert into test_count values(6, 1)

insert into test_count values(7, 3)

insert into test_count values(8, 3)

insert into test_count values(9, 3)

insert into test_count values(10, 3)

commit

3、查询表中全量数据，select t.*， rowid from test_count t

4、编写sql，可以得到每一个value重复的个数，并按照由大到小排列；

 select value, count(*) from test_count t group by value order by 2 desc

select *

from a left join (select aid,count(1) as cnt from b group by aid ) b on a.id=b.aid

Select count（*）from [table] where id in (select distinct max(id) from [table] where gsmc like '%碳资产%' group by stockcode）

在第二条查询Max（id）的语句中增加distinct查询出的id记录都是唯一，没有重复记录。

---