计算重复记录

Suppose I have a table named studens.

I want to count how many students who received duplicated scores at least 2 times. Please help me, thanks.

The result that I expect is: Jonh have 100 score 2 times, James have 80 scores 2 times, Julia has 50 scores 2 times. So the amount of students who receive duplicated score at least 2 times is 3 (Jonh, Jam, and Julia).

select count(*) from
(
    select name
    from your_table
    group by name, score
    having count(*) > 1
) x

Based on the edit, your query would be:

select distinct name
from students
group by name, score
having count(*) > 1;

You could see the scores and counts by doing:

select name, score, count(*)
from students
group by name, score
having count(*) > 1;

By the way, the first query is one of the very, very few times where select distinct is appropriate with a group by. I think the second query provides more information, though.

Assuming a student has only one score per subject (that is, name, subject is unique):

SELECT COUNT(DISTINCT t1.name)
FROM scores t1
    INNER JOIN scores t2
    ON t1.name = t2.name
    AND t1.subject <> t2.subject
    AND t1.score = t2.score

SQLFiddle