How to compare the value of two rows with SQL?

问题:

I am using sqlite database. My table schema is

CREATE TABLE performance(area TEXT, name TEXT, score INTEGER, dt TEXT)

The content in the table is like this:

uk|josh|4|2013-11-04 20:00
ca|josh|2|2013-11-05 20:00
us|josh|6|2013-11-05 20:00
uk|andy|5|2013-11-04 20:00
us|andy|1|2013-11-05 20:00
uk|sara|9|2013-11-05 20:00
ca|sara|7|2013-11-06 20:00   
ca|sara|2|2013-11-06 20:00

I used the following sql statement to select name and its corresponding sum of score grouping by name and dt.

select name, sum(score), dt from performance group by name, dt;

I got

josh|4|2013-11-04 20:00
josh|8|2013-11-05 20:00
andy|5|2013-11-04 20:00
andy|1|2013-11-05 20:00
sara|9|2013-11-05 20:00
sara|9|2013-11-06 20:00

Now I want to expand my query so that the sql statement can search that whose sum of score didn't change at different time(dt). In this case the output should be like:

sara|9|2013-11-05 20:00
sara|9|2013-11-06 20:00

How to compose such a sql?

回答1:

This can be achieved by a self (anti) join:

SELECT a.*
FROM   (SELECT name, dt, SUM(score) as sum_score 
        FROM   performance 
        GROUP BY name, dt) a
JOIN   (SELECT name, dt, SUM(score) as sum_score
        FROM   performance 
        GROUP BY name, dt) b
ON     a.name = b.name AND a.sum_score = b.sum_score AND a.dt < b.dt

回答2:

select dt, name, sum(score), dt 
from performance 
group by name, dt
having count(*) > count(distinct score);

*Note: If you are grouping by dt, name, I think you should display them also.