SQL将行的副本创建到同一个表中

Question

Say my SQL table looks like this:

Week | User  | Score
-----+-------+------
1    | Bob   |  14
1    | Tim   |  22
2    | Bob   | -19
2    | Time  | -12
2    | Steve |  31

I want to create an SQL query to duplicate every entry from the most recent week (in this case 2), and paste them into the same same where week increases by one, and the "score" column is zero'd out. The result below:

Week | User  | Score
-----+-------+------
1    | Bob   |  14
1    | Tim   |  22
2    | Bob   | -19
2    | Time  | -12
2    | Steve |  31
3    | Bob   |
3    | Tim   |
3    | Steve |

Answer 1

What you want to do is just take the latest values and insert them back in with slight modifications:

INSERT INTO my_table (id, name, val)
SELECT id+1, name, null
FROM my_table
WHERE id = (SELECT MAX(id) FROM my_table)

See this sqlfiddle for a working example.