第1个回答 2013-10-09
如果是oracle就非常简单如下:
select * from
(
select phoneno,areano,times ,ROW_NUMBER()OVER(partition by phoneno,areano ORDER BY times desc) rk
from
(select phoneno,areano,count(call_time) times from table group by phoneno,areano)
) where rk=1
MySQL的话也可以,方法不同,不懂得话可以问我
第2个回答 2013-10-09
CREATE TABLE test(a INT,b INT,c int)
INSERT INTO dbo.test
( a ,
b ,
c
)
SELECT 1,2,1
UNION ALL
SELECT 1,1,3
UNION ALL
SELECT 1,2,3
UNION ALL
SELECT 2,2,4
UNION ALL
SELECT 2,3,5
UNION ALL
SELECT 2,2,6
--SELECT * FROM test
SELECT t3.a,t3.b,t3.bb
FROM
(
SELECT a,b,bb
FROM
(
SELECT a,b,COUNT(b) AS bb
FROM dbo.test
GROUP BY a,b)t1) t3
INNER JOIN
(
SELECT a,MAX(bb) cc
FROM
(
SELECT a,b,COUNT(b) AS bb
FROM dbo.test
GROUP BY a,b
) t1
GROUP BY a) t2 ON t3.a=t2.a AND t3.bb=t2.cc
ORDER BY a
结果:
1 2 2
2 2 2
你自己改下表名和字段名字就可以了本回答被提问者和网友采纳
第4个回答 2013-10-09
select 电话号码,区号,COUNT(1) as 次数 from ##t1 group by 电话号码,区号