从表中选择一个字段具有相同值的行 [英] Selecting rows from a table that have the same value for one field
本文介绍了从表中选择一个字段具有相同值的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个带有以下两个表的MySQL数据库:
I have a MySQL database with these two tables:
Tutor(tutorId, initials, lastName, email, phone, office)
Student(studentId, initials, lastName, email, tutorId)
要查询共享同一位导师的任何学生的姓名缩写和姓氏是什么?
What is the query to return the initials and last names of any student who share the same tutor?
我尝试了SELECT intials, lastName FROM Student WHERE tutorId = tutorId
,但这只是返回所有学生的姓名.
I tried SELECT intials, lastName FROM Student WHERE tutorId = tutorId
but that just returns the names of all students.
推荐答案
您将不得不与学生对抗:
You'll have to join students against itself:
SELECT s1.initials, s1.lastName
FROM Student s1, Student s2
WHERE s1.studentId <> s2.studentID /* Every student has the same tutor as himself */
AND s1.tutorId = s2.tutorid
如果要输出对:
SELECT s1.initials, s1.lastName, s2.initials, s2.lastName
FROM Student s1, Student s2
WHERE s1.studentId <> s2.studentID /* Every student has the same tutor as himself */
AND s1.tutorId = s2.tutorid
要获取教师列表-学生:
To get a list of Tutor - Students:
SELECT tutorId, GROUP_CONCAT( initials, lastName SEPARATOR ', ')
FROM `Student`
GROUP BY tutorId
/* to only show tutors that have more than 1 student: */
/* HAVING COUNT(studentid) > 1 */
这篇关于从表中选择一个字段具有相同值的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文