如何比较两行的值和SQL？ [英] How to compare the value of two rows with SQL?

问题描述

我使用的是sqlite数据库。我的表模式是

  CREATE TABLE性能（area TEXT，name TEXT，score INTEGER，dt TEXT）
  

表中的内容如下：

  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 
 
我使用以下sql语句通过名称和dt选择名称及其对应的分数分组总和。
 根据名称选择名称，总和（分数），dt， 
  
我得到
  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 
  
现在我想扩展我的查询，以便sql语句可以搜索其总分没有改变不同时间（dt）。在这种情况下，输出应该是：
  sara | 9 | 2013-11-05 20:00 
 sara | 9 | 2013-11-06 20:00 
  
如何撰写这样的sql？
解决方案
这可以通过自身（反）连接来实现：
  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和a.dt b.dt 
  
 
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?
解决方案
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


                        
这篇关于如何比较两行的值和SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！