关于子查询多行插入的问题
假设我有以下 2 个表,
CREATE TABLE t1(
name VARCHAR(25) NOT NULL,
time INT,
a INT
);
CREATE TABLE t2(
name VARCHAR(25) NOT NULL,
time INT,
b INT
);
并且我希望在给定时间从 t1
中提取所有值 (a
),前一个时间的所有值(例如为了方便起见,只需使用 time-1
),然后对于每个名称,从旧名称中减去新名称,并将这些值插入具有相同 时间
的 t2
中。执行此操作的缓慢方法将涉及执行类似的操作
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 ....;
从我对子查询的(有限)理解,希望应该有一种方法可以在一个查询中完成所有这一切。有什么想法吗?提前致谢 :)
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 the 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 :)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
看起来您可以使用
INSERT ... SELECT
语法:测试用例:
结果:
It looks like you can use the
INSERT ... SELECT
syntax:Test case:
Result:
有我的mysql 插入...选择
there is im mysql insert ... select