使用groupby从另一个表中将值插入到特定列中 [英] Insert value into specific column from another table with groupby
本文介绍了使用groupby从另一个表中将值插入到特定列中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表(a,b)。在表a中,我想在一个小时内获得多少个记录(我有datetime列),那么结果将被插入到表b中,但是在特定的ID(已经存在ID的值)中。
这是我的错误代码:
INSERT INTO b(value)
WHERE ID = 15
SELECT DAY COUNT(*)
FROM a
WHERE date> ='2015-09-19 00:00:00'AND date< '2015-09-19 00:59:59'
GROUP BY DAY(date),HOUR(date);;
我可以从这个案例中查询吗?
非常感谢任何回复!
解决方案
Schema
创建表tA
(id int auto_increment主键,
日期时间不为空,
- 其他东西
键(theDate) - 使其快速
);
创建表tB
(myId int主键, - 根据定义PK不为空
someCol int not null
);
- 截断表tA;
- 截断表tB;
insert tA(theDate)values
('2015-09-19'),
('2015-09-19 00:24:21'),
('2015-09-19 07:24:21'),
('2015-09-20 00:00:00');
insert tB(myId,someCol)值(15,-1); - (-1)只是为它的
插入tB(myId,someCol)值(16,-1); - ( - )只是为了它
update tB
set someCol =(select count(*)from tA whereDate between' -09-19 00:00:00'和'2015-09-19 00:59:59')
其中tB.myId = 15;
结果
code> select * from tB;
+ ------ + --------- +
| myId | someCol |
+ ------ + --------- +
| 15 | 2 |
| 16 | -1 |
+ ------ + --------- +
只有myId = 15被触动。
I am using MySQL. I want to insert value's result from groupby of datetime to specific column (using where, maybe). Let say: I have two tables (a, b). In table a, I want to get how many total records during a hour (which I have datetime column), then the result will insert into table b, but in specific ID (there is already exist ID's value).
This is my error code:
INSERT INTO b(value)
WHERE ID=15
SELECT DAY COUNT(*)
FROM a
WHERE date >= '2015-09-19 00:00:00' AND date < '2015-09-19 00:59:59'
GROUP BY DAY(date),HOUR(date);";
Is that possible I make a query from this case? Thank you very much for any reply!
解决方案
Schema
create table tA
( id int auto_increment primary key,
theDate datetime not null,
-- other stuff
key(theDate) -- make it snappy fast
);
create table tB
( myId int primary key, -- by definition PK is not null
someCol int not null
);
-- truncate table tA;
-- truncate table tB;
insert tA(theDate) values
('2015-09-19'),
('2015-09-19 00:24:21'),
('2015-09-19 07:24:21'),
('2015-09-20 00:00:00');
insert tB(myId,someCol) values (15,-1); -- (-1) just for the heck of it
insert tB(myId,someCol) values (16,-1); -- (-1) just for the heck of it
The Query
update tB
set someCol=(select count(*) from tA where theDate between '2015-09-19 00:00:00' and '2015-09-19 00:59:59')
where tB.myId=15;
The Results
select * from tB;
+------+---------+
| myId | someCol |
+------+---------+
| 15 | 2 |
| 16 | -1 |
+------+---------+
only myId=15 is touched.
这篇关于使用groupby从另一个表中将值插入到特定列中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文