mysql截断字段_mysql：截断一个字段并在另一个表中汇总数据

mysql> select * from topsmsspammers_hour limit 1;

+---------------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

| date | spam | suspect | clean | spammer | spam_campaign_id | messageType | policy | isSenderPolicy |

+---------------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

| 2013-06-11 23:00:00 | 1 | 0 | 0 | +12073708065 | NotSet|Message35392HerbisReallyWorkingRealy | 4 | 1 | 1 |

+---------------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

1 row in set (0.00 sec)

mysql>

我想使用本表数据来填充完全相同的列名，但与截断日期合计一天日期表(和计数改变以反映变化) 我第一次确定的选择查询这将截断我需要的数据：

mysql> select SUBSTRING_INDEX(date, ' ', 1) as date, sum(spam) as spam, sum(suspect) as suspect, sum(clean) as clean, spammer, spam_campaign_id, messageType, policy, isSenderPolicy from topsmsspammers_hour group by date, spammer, spam_campaign_id, messageType, policy, isSenderPolicy limit 1;

+------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

| date | spam | suspect | clean | spammer | spam_campaign_id | messageType | policy | isSenderPolicy |

+------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

| 2013-06-11 | 1 | 0 | 0 | +12073708065 | NotSet|Message35392HerbisReallyWorkingRealy | 4 | 1 | 1 |

+------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

1 row in set, 1 warning (1.66 sec)

mysql>

我又试图使用选择查询来填充新表，但截断日期尚未插入。而是原来的日期插入：

mysql> insert into topsmsspammers_day (date, spam, suspect, clean, spammer, spam_campaign_id, messageType, policy, isSenderPolicy) select SUBSTRING_INDEX(date, ' ', 1) as date, sum(spam) as spam, sum(suspect) as suspect, sum(clean) as clean, spammer, spam_campaign_id, messageType, policy, isSenderPolicy from topsmsspammers_hour group by date, spammer, spam_campaign_id, messageType, policy, isSenderPolicy limit 1;

Query OK, 1 row affected, 1 warning (1.68 sec)

Records: 1 Duplicates: 0 Warnings: 1

mysql> select * from topsmsspammers_day limit 1; +---------------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

| date | spam | suspect | clean | spammer | spam_campaign_id | messageType | policy | isSenderPolicy |

+---------------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

| 2013-06-11 00:00:00 | 1 | 0 | 0 | +12073708065 | NotSet|Message35392HerbisReallyWorkingRealy | 4 | 1 | 1 |

+---------------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

1 row in set (0.00 sec)

mysql>

我试着再更改数据有用于填充新表不同的名字，但是这仍然导致了完整的日期越来越插入到表：

mysql> insert into topsmsspammers_day (date, spam, suspect, clean, spammer, spam_campaign_id, messageType, policy, isSenderPolicy) select SUBSTRING_INDEX(date, ' ', 1) as dayDate, sum(spam) as spam, sum(suspect) as suspect, sum(clean) as clean, spammer, spam_campaign_id, messageType, policy, isSenderPolicy from topsmsspammers_hour group by dayDate, spammer, spam_campaign_id, messageType, policy, isSenderPolicy limit 1;

Query OK, 1 row affected (1.75 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from topsmsspammers_day limit 1;

+---------------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

| date | spam | suspect | clean | spammer | spam_campaign_id | messageType | policy | isSenderPolicy |

+---------------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

| 2013-06-11 00:00:00 | 1 | 0 | 0 | +12073708065 | NotSet|Message35392HerbisReallyWorkingRealy | 4 | 1 | 1 |

+---------------------+------+---------+-------+--------------+---------------------------------------------+-------------+--------+----------------+

1 row in set (0.00 sec)

mysql>

我甚至尝试在插入更改名称但跌倒了就不是在新表中的字段名称：

mysql> insert into topsmsspammers_day (dayDate, spam, suspect, clean, spammer, spam_campaign_id, messageType, policy, isSenderPolicy) select SUBSTRING_INDEX(date, ' ', 1) as dayDate, sum(spam) as spam, sum(suspect) as suspect, sum(clean) as clean, spammer, spam_campaign_id, messageType, policy, isSenderPolicy from topsmsspammers_hour group by dayDate, spammer, spam_campaign_id, messageType, policy, isSenderPolicy limit 1;

ERROR 1054 (42S22): Unknown column 'dayDate' in 'field list'

mysql>

我缺少什么？我怎样才能将截断的聚合数据插入到新表中？

一个

它看起来像2013年6月11日23:00:00被改为2013年6月11日00:00:00所以也许它与数据类型 一个

2013-08-26

amadain