求一段sql语句 要考虑效率和资源
字段： A、B
求：A字段值相同 且 B字段值不同 的总记录数
不建索引 200万条记录
考虑效率和资源
[63 byte] By [1234567] at [2007-8-15 11:11:19] 
# 1 Re: 求一段sql语句 要考虑效率和资源
select count(*) from a group by a,b熊猫贩子 at 2007-3-26 18:59:0 >
# 2 Re: 求一段sql语句 要考虑效率和资源
select count(*) from(
select count(*) from
(select distinct aid,aappid from test123)t
group by t.aappid
)t2
待从头，收拾旧山河 at 2007-3-26 18:59:5 >
# 4 Re: 求一段sql语句 要考虑效率和资源
表aA 、 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 )
得到的结果
无处不在 at 2007-3-26 18:59:15 >
# 5 Re: 求一段sql语句 要考虑效率和资源
不好意思 错了 应为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 )
Coding超过了10W行 at 2007-3-26 18:59:22 >
# 6 Re: 求一段sql语句 要考虑效率和资源
select count(*) from (select count(*) from test group by appid,date_time);面壁思过 at 2007-3-26 18:59:34 >
# 7 Re: 求一段sql语句 要考虑效率和资源
select count(*) from (select count(*) from test group by appid,date_time);大平/要做必须最好 at 2007-3-26 18:59:35 >
# 8 Re: 求一段sql语句 要考虑效率和资源
select count(*) from (select count(*) from test1 group by a,b);阿来 at 2007-3-26 18:59:43 >
# 9 Re: 求一段sql语句 要考虑效率和资源
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)
这么写好像可以 不知道效率怎么样
Bjarne Stroustrup at 2007-3-26 18:59:59 >
# 10 Re: 求一段sql语句 要考虑效率和资源
select sum(c) from(
select a,b,count(*) c from tab
group by a,b
)
C++探索者 at 2007-3-26 19:0:4 >
# 11 Re: 求一段sql语句 要考虑效率和资源
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)
空心蝴蝶 at 2007-3-26 19:0:28 >
# 12 Re: 求一段sql语句 要考虑效率和资源
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);琥珀 at 2007-3-26 19:4:19 >
# 13 Re: 求一段sql语句 要考虑效率和资源
select sum(cnt) from ( select count(a) as cnt from ( select distinct a,b from a)
having count(a) > 1
)
花心筒 at 2007-3-26 19:4:31 >
# 14 Re: 求一段sql语句 要考虑效率和资源
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);
快乐无限 at 2007-3-26 19:4:52 >
# 15 Re: 求一段sql语句 要考虑效率和资源
select count(*) from a xa,b xbwhere xa.A=xa.B and xa.B<>xb.B
Cplus@hit.edu.cn at 2007-3-26 19:5:5 >