不使用DISTICT是因为真正的表不止A,B两个字段。还有一个C字段A B C
a1 b1 c1
a1 b1 c2
a1 b2 c3
a1 b3 c4
a2 b1 c5
a2 b1 c6
a3 b2 c7
a3 b2 c8
a3 b3 c9如果是上面这种表结构,使用DISTINCT也可以.
a1 b1 c1
a1 b1 c2
a1 b2 c3
a1 b3 c4
a2 b1 c5
a2 b1 c6
a3 b2 c7
a3 b2 c8
a3 b3 c9如果是上面这种表结构,使用DISTINCT也可以.
(select a,count(*) count from table group by a,substr(2,length(b)-1)) group by a;
(select a,count(*) count from table group by a,substr(2,length(b)-1)) group by a;好像不行,我运行结果如下A SUM(COUNT)
-------------------- ----------
a1 4
a2 2
a3 3
select A,count(distinct B) from T1 group by A
A B C
a1 b1 c1
a1 b1 c2
a1 b2 c3
a1 b3 c4
a2 b1 c5
a2 b1 c6
a3 b2 c7
a3 b2 c8
a3 b3 c9select A,count(distinct B) from T1 group by A
A COUNT(DISTINCTB)
a1 3
a2 1
a3 2
from(
select distinct a,b from t1
)
group by a;
----- -----
a1 b1
a1 b1
a1 b2
a1 b3
a2 b1
a2 b1
a3 b2
a3 b2
a3 b39 行 已选择SQL>
SQL> select a, count(*)
2 from (select a, b
3 from (select a,
4 b,
5 row_number() over(partition by a, b order by a) t
6 from test)
7 where t = 1)
8 group by a;A COUNT(*)
----- ----------
a1 3
a2 1
a3 2
select a,sum(count) from
(select a,1 count from table group by a,substr(2,length(b)-1)) group by a;