当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Write a query to find the students with the same first name AND last name?

问题描述:

So here is what I have tried:

SELECT A.STUDENT_ID

FROM STUDENT A, STUDENT B

WHERE A.FNAME=B.FNAME

AND A.LNAME=B.LNAME

AND A.STUDENT_ID!=B.STUDENT_ID;

Here is the STUDENT table columns:

STUDENT_ID primary key

FNAME

LNAME

It seems to work. But according to the practice exam, it is worth 15 points so I am not sure if it is totally right. What if there more that two students have the same fname and lname? How would I write it or would this work for that too?

网友答案:

To avoid the Cartesian product (which is what you get with those multiple FROM clauses) and the duplicate rows that would create, I'd use an EXISTS clause

SELECT a.STUDENT_ID, a.FNAME, a.LNAME
FROM STUDENT a WHERE EXISTS (
  SELECT 1 FROM STUDENT b
  WHERE a.FNAME = b.FNAME
  AND a.LNAME = b.LNAME
  AND a.STUDENT_ID <> b.STUDENT_ID
);

http://sqlfiddle.com/#!9/75fd6/3

You would also benefit greatly by having an index on both FNAME and LNAME but I doubt that's going to be relevant to your practice exam.

网友答案:

What you have will technically work. However, the number of rows returned per matched student will equal n - 1, where n is the number of students who have the same first and last name as the matched student.

To address this, use a DISTINCT clause in your query.

SELECT DISTINCT A.STUDENT_ID
FROM STUDENT A, STUDENT B
WHERE A.FNAME=B.FNAME
    AND A.LNAME=B.LNAME
    AND A.STUDENT_ID!=B.STUDENT_ID;
分享给朋友:
您可能感兴趣的文章:
随机阅读: