这是一次面试碰到的题目,一开始以为要用到group by关键字,后来仔细想了下,其实不需要的。
这张数据表名为student,各字段名如上图所示
我写的sql如下所示:
第一种:用关联查询
SELECT DISTINCT s1.*
FROM student s1, student s2
WHERE s1.id != s2.id
and s1.`name` = s2.`name`
ORDER BY s1.id;
SELECT DISTINCT s1.*
FROM student s1 LEFT JOIN student s2
ON s1.id != s2.id
WHERE s1.`name` = s2.`name`
ORDER BY s1.id;
第二种:用嵌套查询
SELECT s1.*
FROM student s1
WHERE s1.`name` in (
SELECT s2.`name`
FROM student s2
WHERE s2.id != s1.id
and s2.`name` = s1.`name`
)
ORDER BY s1.id;
第三种:用exists子查询
SELECT s1.*
FROM student s1
WHERE EXISTS (
SELECT 1
FROM student s2
WHERE s2.id != s1.id
and s2.`name` = s1.`name`
)
ORDER BY s1.id;
查询结果如下:
注:第一种方法的distinct关键字必须要有,要不然会有重复的记录。
PS:group by 关键字一般只有在统计查询时才用到。还有如果用group by 关键字时,select查询字段只能是集函数或者是group by 中的字段