SELECT NAME,TEL
from TABL1 where
TEL in
(
select TEL from TABL1
group by TEL having count(*)>1
)
from TABL1 where
TEL in
(
select TEL from TABL1
group by TEL having count(*)>1
)
解决方案 »
- 数据库太大删除不了怎么办
- 逗号相隔字段值查询的最优方法是什么呢?
- 请大家帮忙看看,使用mysql connector/c++ 往数据库中插入blob字段出1210错
- 存储过程变量如何引用
- 帮忙分析个数据库
- 有奖竞答:统计文件数据
- mysql中的blob类型,怎么显示成可读的东西?(不知道存的是什么,应该是比较少的数据文件),谢谢指点!
- MySQL中,DatabaseMetaData.getTables方法返回的ResultSet中的第二个字段均为空,是否一定是这样的?
- 急!mysql数据显示问题
- mysqldump怎么使用?
- 安装myslq5.0.45 选择哪一个
- 不同版本的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