列出所有重复记录的SQL

/************************************
*Author:Java619
*Time:2007-08-06
*************************************/

假设有一公司信息表,表名为company,其数据如下

+----+----------+-------+
| id | name     | asset |
+----+----------+-------+
|  1 | company1 |    50 |
|  2 | company2 |    20 |
|  3 | company3 |    30 |
|  4 | company1 |    50 |
|  5 | company1 |    50 |
|  6 | company3 |    30 |
+----+----------+-------+ 

现在要从中筛选出所有重复的记录,有些人可能会想到用以下语句

select * from company group by name having count(*)>1;
+----+----------+-------+
| id | name     | asset |
+----+----------+-------+
|  1 | company1 |    50 |
|  3 | company3 |    30 |
+----+----------+-------+
结果只列出有重复的公司

下面给出一正确的语句

select a.* from company as a,(select NAME from company group by NAME having count(*)>1) b where a.NAME=b.NAME order by a.NAME,a.ID;
+----+----------+-------+
| id | name     | asset |
+----+----------+-------+
|  1 | company1 |    50 |
|  4 | company1 |    50 |
|  5 | company1 |    50 |
|  3 | company3 |    30 |
|  6 | company3 |    30 |
+----+----------+-------+
以上结果正是我们所要的.