首页 > 试题广场 >

查询university重复的记录明细，比如'北京大学'重复

[单选题]

查询university重复的记录明细，比如'北京大学'重复2次，则结果是2条（id=1和id=3）。如下SQL不符合预期的是（）

user_profile中全部记录如下：

+----+-----------+--------+------+--------------+----------+
| id | device_id | gender | age  | university   | province |
+----+-----------+--------+------+--------------+----------+
|  1 |      2138 | male   |   21 | 北京大学     | BeiJing  |
|  2 |      3214 | male   | NULL | 复旦大学     | Shanghai |
|  3 |      6543 | female |   20 | 北京大学     | BeiJing  |
|  4 |      2315 | female |   23 | 浙江大学     | ZheJiang |
|  5 |      5432 | male   |   25 | 山东大学     | Shandong |
+----+-----------+--------+------+--------------+----------+
5 rows in set (0.00 sec)

SELECT t2.*
FROM (
   SELECT university, COUNT(*) AS c1
   FROM user_profile
   GROUP BY university
   HAVING c1 > 1
) t1
   LEFT JOIN user_profile t2 ON t1.university = t2.university;

SELECT t2.*
FROM (
   SELECT university, COUNT(*) AS c1
   FROM user_profile
   GROUP BY university
) t1
   LEFT JOIN user_profile t2
   ON t1.university = t2.university
   AND c1 > 1;

SELECT t2.*
FROM (
   SELECT university, COUNT(*) AS c1
   FROM user_profile
   GROUP BY university
) t1
   LEFT JOIN user_profile t2 ON t1.university = t2.university
WHERE c1 > 1;

SELECT t2.*
FROM (
   SELECT *
   FROM (
      SELECT university, COUNT(*) AS c1
      FROM user_profile
      GROUP BY university
   ) t1
   WHERE c1 > 1
) t3
   LEFT JOIN user_profile t2 ON t3.university = t2.university;

查看答案及解析