mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| nian | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> select * from t;
+------+-------+
| id | nian |
+------+-------+
| 1 | one |
| 1 | one |
| 2 | two |
| 2 | two |
| 3 | three |
| 3 | three |
| 4 | four |
+------+-------+
7 rows in set (0.00 sec)mysql> select all id,nian from t group by id,nian having count(*)>1 ;
+------+-------+
| id | nian |
+------+-------+
| 1 | one |
| 2 | two |
| 3 | three |
+------+-------+不知道是不是你的意思呢
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| nian | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> select * from t;
+------+-------+
| id | nian |
+------+-------+
| 1 | one |
| 1 | one |
| 2 | two |
| 2 | two |
| 3 | three |
| 3 | three |
| 4 | four |
+------+-------+
7 rows in set (0.00 sec)mysql> select all id,nian from t group by id,nian having count(*)>1 ;
+------+-------+
| id | nian |
+------+-------+
| 1 | one |
| 2 | two |
| 3 | three |
+------+-------+不知道是不是你的意思呢
+------+-------+
| id | nian |
+------+-------+
| 1 | one |
| 1 | one |
| 2 | two |
| 2 | two |
| 3 | three |
| 3 | three |
+------+-------+
没有 | 4 | four | 因为这是一条不重复的记录。
也就是,我想查询出所有“重复”的记录。多谢!!!
select str1, str2 from t group by concat(str1, str2) having count(*)>0,这是针对两个列(str1, str2)类型都是字符串类型的
select num1, num2 from t group by num1+num2 having count(*)>0,这是针对两个列(num1, num2)类型都是数字类型的
mysql> select all id,nian,count(*) from t group by id,nian having count(*)>1 ;
我试了类似
mysql> select all id,nian,count(*) from t group by id,nian having count(*)>1 ;
的确像小木说的, 还是不能列出所有重复的记录.看来,没有子查询很难实现了! :-(