select count(*) from ( select count(*) from (select distinct aid,aappid from test123)t group by t.aappid )t2
select A, count(*) from a group by A 应该是这个
表a A 、 B 1 1 1 1 1 2 1 3 2 1 2 1 2 2 3 1 3 2 另,问题更正 求结果:A在(A同B不同中) 如上情况 结果应为:7 就是说 最傻瓜的办法 select count(*) from (select A from a a1,a a2 where a1.A=a2.A and a1.B<>a2.B ) 得到的结果
不好意思 错了 应为 select count(*) from a where a.A in (select A from a a1,a a2 where a1.A=a2.A and a1.B<>a2.B )
select count(*) from (select count(*) from test group by appid,date_time);
select count(*) from (select count(*) from test1 group by a,b);
anglechen(啊健): select count(*) from (select count(*) from test1 group by a,b); result: 7Explain Plan: Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStopSELECT STATEMENT Optimizer Mode=CHOOSE SORT AGGREGATE VIEW SORT GROUP BY TABLE ACCESS FULL TEST1 没有索引,一次全表扫描。
select count(*) from a where A in (select t1.A from (select distinct t.A , t.B from a t) t1 group by t1.A having count(*)>1)这么写好像可以 不知道效率怎么样
kedyjin 讲得很明白 不过和我的意图有出入 看我写的上面的句子 是那个意思 ^_^ 先谢谢了
select sum(c) from ( select a,b,count(*) c from tab group by a,b )
select count(*) from ( select a,b,count(*) c from tab group by a,b )
还是认为一楼的答案是正解 select count(*) from a group by a,b
错了还是要套个 select~ select count(*) from (select count(*) from test1 group by a,b)
select count(*) from (select distinct a,b from table)
觉得一楼的好象不对: select count(*) from a group by a,b 我觉得是统计A,B都相同的记录数.
SELECT COUNT(*) FROM a WHERE CONCAT(A,CONCAT('_',B)) IN (SELECT CONCAT(A,CONCAT('_',B)) FROM a GROUP BY A, B HAVING COUNT(A) >1);
select sum(cnt) from ( select count(a) as cnt from ( select distinct a,b from a) having count(a) > 1 )
select count(Tb1.A) from a Tb1, a Tb2 where Tb1.A=TB2.A and Tb1.B<>Tb2.B
只需要对A字段排序就可以了 select sum(M) from (select A,B,count(distinct B) M from a group by A,B);
select count(*) from a xa,b xb where xa.A=xa.B and xa.B<>xb.B
select count(*) from A group by a,b having count(a)>1 and count(b)=1
select A,count(A) from (select distinct A,B from a ) ttgroup by A
(
select count(*) from
(select distinct aid,aappid from test123)t
group by t.aappid
)t2
应该是这个
A 、 B
1 1
1 1
1 2
1 3
2 1
2 1
2 2
3 1
3 2
另,问题更正 求结果:A在(A同B不同中)
如上情况 结果应为:7
就是说 最傻瓜的办法 select count(*) from (select A from a a1,a a2 where a1.A=a2.A and a1.B<>a2.B )
得到的结果
select count(*) from a where a.A in (select A from a a1,a a2 where a1.A=a2.A and a1.B<>a2.B )
select count(*) from (select count(*) from test1 group by a,b);
result:
7Explain Plan:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStopSELECT STATEMENT Optimizer Mode=CHOOSE
SORT AGGREGATE
VIEW
SORT GROUP BY
TABLE ACCESS FULL TEST1
没有索引,一次全表扫描。
group by t1.A
having count(*)>1)这么写好像可以 不知道效率怎么样
讲得很明白 不过和我的意图有出入 看我写的上面的句子 是那个意思
^_^
先谢谢了
(
select a,b,count(*) c from tab
group by a,b
)
(
select a,b,count(*) c from tab
group by a,b
)
select count(*) from a group by a,b
select count(*) from (select count(*) from test1 group by a,b)
select count(*) from a group by a,b
我觉得是统计A,B都相同的记录数.
( select count(a) as cnt from ( select distinct a,b from a)
having count(a) > 1
)
select
sum(M)
from
(select A,B,count(distinct B) M from a group by A,B);
where xa.A=xa.B and xa.B<>xb.B
group by a,b
having count(a)>1 and count(b)=1