Question on multi-row insertion with subqueries

Say I have the following 2 tables,

CREATE TABLE t1(
  name VARCHAR(25) NOT NULL,
  time INT,
  a INT
);

CREATE TABLE t2(
  name VARCHAR(25) NOT NULL,
  time INT,
  b INT
);

and Im looking to pull all the values (a) out of t1 with a given time, all the values with the previous time (say just time-1 for convenience) then for each name subtract the newer one from the older one and insert those values into t2 with t开发者_StackOverflowhe same time. The slow way of doing this would involve doing something like

SELECT name, a FROM t1 WHERE time = x;
SELECT name, a FROM t1 WHERE time = x-1;
(subtract the as for each name)
INSERT INTO t2 VALUES ....;

From my (limited) understanding of subqueries, there should hopefully be a way to do this all in 1 query. Any ideas? Thanks in advance :)

It looks like you can use the INSERT ... SELECT syntax:

INSERT INTO t2 (name, time, b)
SELECT  ta.name, ta.time time, (ta.a - tb.a) b
FROM    t1 ta 
JOIN    t1 tb ON (tb.time = ta.time - 1 AND tb.name = ta.name);

Test case:

INSERT INTO t1 VALUES ('t1', 1, 100);
INSERT INTO t1 VALUES ('t1', 2, 200);
INSERT INTO t1 VALUES ('t1', 3, 500);
INSERT INTO t1 VALUES ('t1', 4, 600);
INSERT INTO t1 VALUES ('t1', 5, 800);
INSERT INTO t1 VALUES ('t1', 6, 900);

Result:

SELECT * FROM t2;
+------+------+------+
| name | time | b    |
+------+------+------+
| t1   |    2 |  100 |
| t1   |    3 |  300 |
| t1   |    4 |  100 |
| t1   |    5 |  200 |
| t1   |    6 |  100 |
+------+------+------+
5 rows in set (0.00 sec)

there is im mysql insert ... select

INSERT INTO table ( fields )
SELECT fields FROM table;