表结构:
CREATE TABLE `order_goods` (
`rec_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`goods_number` smallint(10) NOT NULL DEFAULT '1',
`company_id` mediumint(8) unsigned NOT NULL DEFAULT '0' ',
`CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)
我想每天晚上11点统计一下截止到当天,一个月(30天)内每个company的goods_number总数
select company_id, sum(goods_number) from order_goods group by company_id,然后update(不是insert)到一张统计表中:
CREATE TABLE `order_total` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`company_id` smallint(10) NOT NULL DEFAULT '1',
`total` mediumint(8) unsigned NOT NULL DEFAULT '0'
)
请问这个作业怎么写?谢谢!~
CREATE TABLE `order_goods` (
`rec_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`goods_number` smallint(10) NOT NULL DEFAULT '1',
`company_id` mediumint(8) unsigned NOT NULL DEFAULT '0' ',
`CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)
我想每天晚上11点统计一下截止到当天,一个月(30天)内每个company的goods_number总数
select company_id, sum(goods_number) from order_goods group by company_id,然后update(不是insert)到一张统计表中:
CREATE TABLE `order_total` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`company_id` smallint(10) NOT NULL DEFAULT '1',
`total` mediumint(8) unsigned NOT NULL DEFAULT '0'
)
请问这个作业怎么写?谢谢!~
on a.company_id=b.company_id
set a.total=b.total
创建临时表goods
(select company_id, sum(goods_number) sumgoods from order_goods group by company_id )
update order_total set goods_number=goods.sumgoods where goods.company_id=company_id
mysql> show variables like '%sche%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.01 sec)启动event功能:
mysql> set global event_scheduler =1;
Query OK, 0 rows affected (0.00 sec)确定起用event功能后,执行下面的建立event:(只update不insert哦)
delimiter //
create EVENT my_stat_event
ON SCHEDULE
EVERY 24 HOUR
STARTS concat(date_format(now(),'%Y-%m-%d'),' 23:00:00')
on completion preserve ENABLE
do
begin
update order_total a inner join (select company_id, sum(goods_number) as total_count from order_goods group by company_id) b on a.company_id=b.company_id
set a.total=b.total_count;
end;
//执行上面的建立作业后,查看作业的信息:
mysql> select * from information_schema.events limit 1;
那是肯定不能的,上面的语句是根据你的需求“然后update(不是insert)”来处理的
on duplicate update ...
参考一下MYSQL手册中的语法。MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
update order_total a inner join (select company_id, sum(goods_number) as total_count from order_goods group by company_id) b on a.company_id=b.company_id
set a.total=b.total_count; insert into order_total (company_id,total)
select company_id, sum(goods_number) as total_count from order_goods a where not exists (select 1 form order_total b where b.company_id=a.company_id) group by company_id;
end;