sql - 如何计算与唯一 ID 相关的值已更改的次数？

Question

我试图计算出学生在大学生涯中更换专业的次数。

   Create Table Taco
  (  s_id  varchar(9),
     s_year  int,
     s_major varchar(50))

 Insert INTO Taco 
 Values (123456789, 2018, 'PSYCHOLOGY'),
        (123456789, 2016, 'GENERAL EDUCATION'),
        (123456789, 2017, 'PSYCHOLOGY'),
        (987654321, 2018, 'FILM'),
        (987654321, 2017, 'ANTHROPOLOGY'),
        (654789123, 2018, 'BIOLOGY'),
        (654789123, 2017, 'BIOLOGY'),
        (987654321, 2015, 'COMPUTER SCIENCE')

我期待结果:

 s_id     changes
 123456789     2      
 987654321     3
 654789123     1

我该怎么写这个？

Answer 1

使用Count和GROUP BY

SELECT s_id,count(s_id) 'changes'
FROM Taco 
group by s_id

sqlfiddle:http://sqlfiddle.com/#!18/2c1d7/2