select a.* from tb as a,(select tel,count(*) as cnt from tb group by tel having count(*)>1) as b where a.tel=b.tel order by b.cnt desc
select distinct a.* from tb as a,(select tel,count(*) as cnt from tb group by tel having count(*)>1) as b where a.tel=b.tel order by b.cnt desc;
mysql> CREATE TABLE table1 (NAME VARCHAR(2),TEL INT); Query OK, 0 rows affected (0.08 sec)mysql> INSERT INTO table1 -> SELECT 'A1',1111 UNION ALL -> SELECT 'A2',1111 UNION ALL -> SELECT 'A1',1111 UNION ALL -> SELECT 'A2',1111 UNION ALL -> SELECT 'A3',1111 UNION ALL -> SELECT 'A3',2222 UNION ALL -> SELECT 'A3',2222 UNION ALL -> SELECT 'A4',2222 UNION ALL -> SELECT 'A5',3333; Query OK, 9 rows affected (0.03 sec) Records: 9 Duplicates: 0 Warnings: 0mysql> mysql> -- SQL查询如下: mysql> mysql> mysql> select distinct a.* -> from table1 as a,(select tel,count(*) as cnt from table1 group by tel having count(*)>1) as b -> where a.tel=b.tel -> order by b.cnt desc; +------+------+ | NAME | TEL | +------+------+ | A1 | 1111 | | A2 | 1111 | | A3 | 1111 | | A4 | 2222 | | A3 | 2222 | +------+------+ 5 rows in set (0.00 sec)
TABL1 NAME TEL A1 1111 A2 1111 A1 1111 A1 1111 A1 1111 A2 1111 A3 1111 A3 2222 A3 2222 A4 2222 A5 3333 得出 NAME TEL A1 1111 A2 1111 A3 1111 A3 2222 A4 2222 上面的都不行
举例也是一种学问。mysql> create table TABL1 -> ( -> NAME varchar(4), -> TEL varchar(10) -> ); Query OK, 0 rows affected (0.06 sec)mysql> insert into TABL1 values -> ('A1','1111'), -> ('A2','1111'), -> ('A1','1111'), -> ('A1','1111'), -> ('A1','1111'), -> ('A2','1111'), -> ('A3','1111'), -> ('A3','2222'), -> ('A3','2222'), -> ('A4','2222'), -> ('A5','3333'); Query OK, 11 rows affected (0.03 sec) Records: 11 Duplicates: 0 Warnings: 0mysql> select DISTINCT NAME, TEL from TABL1 where TEL = any ( -> select TEL from TABL1 group by TEL having COUNT(DISTINCT NAME) >1); +------+------+ | NAME | TEL | +------+------+ | A1 | 1111 | | A2 | 1111 | | A3 | 1111 | | A3 | 2222 | | A4 | 2222 | +------+------+ 5 rows in set (0.05 sec)
select DISTINCT NAME, TEL from TABL1 where TEL = any ( select TEL from TABL1 group by TEL having COUNT(DISTINCT NAME) >1);还有很多等效写法。 inner join, in , exists, MySQL在优化的时候效率会不一样,你自己可以试一下。
from tb as a,(select tel,count(*) as cnt from tb group by tel having count(*)>1) as b
where a.tel=b.tel
order by b.cnt desc
from tb as a,(select tel,count(*) as cnt from tb group by tel having count(*)>1) as b
where a.tel=b.tel
order by b.cnt desc;
Query OK, 0 rows affected (0.08 sec)mysql> INSERT INTO table1
-> SELECT 'A1',1111 UNION ALL
-> SELECT 'A2',1111 UNION ALL
-> SELECT 'A1',1111 UNION ALL
-> SELECT 'A2',1111 UNION ALL
-> SELECT 'A3',1111 UNION ALL
-> SELECT 'A3',2222 UNION ALL
-> SELECT 'A3',2222 UNION ALL
-> SELECT 'A4',2222 UNION ALL
-> SELECT 'A5',3333;
Query OK, 9 rows affected (0.03 sec)
Records: 9 Duplicates: 0 Warnings: 0mysql>
mysql> -- SQL查询如下:
mysql>
mysql>
mysql> select distinct a.*
-> from table1 as a,(select tel,count(*) as cnt from table1 group by tel having count(*)>1) as b
-> where a.tel=b.tel
-> order by b.cnt desc;
+------+------+
| NAME | TEL |
+------+------+
| A1 | 1111 |
| A2 | 1111 |
| A3 | 1111 |
| A4 | 2222 |
| A3 | 2222 |
+------+------+
5 rows in set (0.00 sec)
| NAME | TEL |
+------+------+
| A1 | 1111 |
| A1 | 1111 |
| A3 | 1111 |
| A4 | 2222 |
| A3 | 2222 |
+------+------+要是有全不同样的记录的话,有可能有上面的结果
| NAME | TEL |
+------+------+
| A1 | 1111 |
| A1 | 1111 |
| A3 | 1111 |
| A4 | 2222 |
| A3 | 2222 |
+------+------+
这个是怎么来的?没看出来你的算法。
A1 1111
A2 1111
A1 1111
A1 1111
A1 1111
A2 1111
A3 1111
A3 2222
A3 2222
A4 2222
A5 3333 得出 NAME TEL
A1 1111
A2 1111
A3 1111
A3 2222
A4 2222 上面的都不行
-> (
-> NAME varchar(4),
-> TEL varchar(10)
-> );
Query OK, 0 rows affected (0.06 sec)mysql> insert into TABL1 values
-> ('A1','1111'),
-> ('A2','1111'),
-> ('A1','1111'),
-> ('A1','1111'),
-> ('A1','1111'),
-> ('A2','1111'),
-> ('A3','1111'),
-> ('A3','2222'),
-> ('A3','2222'),
-> ('A4','2222'),
-> ('A5','3333');
Query OK, 11 rows affected (0.03 sec)
Records: 11 Duplicates: 0 Warnings: 0mysql> select DISTINCT NAME, TEL from TABL1 where TEL = any (
-> select TEL from TABL1 group by TEL having COUNT(DISTINCT NAME) >1);
+------+------+
| NAME | TEL |
+------+------+
| A1 | 1111 |
| A2 | 1111 |
| A3 | 1111 |
| A3 | 2222 |
| A4 | 2222 |
+------+------+
5 rows in set (0.05 sec)
select TEL from TABL1 group by TEL having COUNT(DISTINCT NAME) >1);还有很多等效写法。 inner join, in , exists, MySQL在优化的时候效率会不一样,你自己可以试一下。
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html