现有三张表
表1
CREATE TABLE `advert_stat_impl` (
`id` int(10) unsigned NOT NULL auto_increment,
`versionid` int(10) unsigned default NULL,
`stat_date` date default NULL,
`active_num` int(10) unsigned default NULL,
`unstall_num` int(10) unsigned default NULL,
`open_num` int(10) unsigned default NULL,
`re` varchar(255) default NULL,
PRIMARY KEY (`id`)
)
表2
CREATE TABLE `advert_version_impl` (
`id` int(10) unsigned NOT NULL auto_increment,
`version` varchar(45) default NULL,
`nickname` varchar(45) default NULL,
PRIMARY KEY (`id`)
)
表3
CREATE TABLE `adert_strategy_impl` (
`id` int(10) unsigned NOT NULL auto_increment,
`random` int(10) unsigned default NULL,
`advert_num` int(10) unsigned default NULL,
`advert_place` varchar(45) default NULL,
`speed` int(10) unsigned default NULL,
`direction` varchar(45) default NULL,
`timeperiod` int(10) unsigned default NULL,
`groupnum` int(10) unsigned default NULL,
`changenum` int(10) unsigned default NULL,
`stat_date` datetime default NULL,
PRIMARY KEY (`id`)
)
表4
CREATE TABLE `cmsthree`.`advert_click_num` (
`id` int(10) unsigned NOT NULL auto_increment,
`total_click_num` int(10) unsigned default NULL,
`only_click_num` int(10) unsigned default NULL,
`advert_type` varchar(45) default NULL,
`stat_date` date default NULL,
PRIMARY KEY (`id`)
)
查询条件日期之间,比如2007-10-1到2007-10-20
要得到的列 日期,某版本的active_num总和<在表advert_stat_impl中有版本ID(versionid)>,所有版本的总和,还有advert_click_num.total_click_num,adert_strategy_impl.advert_num所有的查询条件都是日期之间
表1
CREATE TABLE `advert_stat_impl` (
`id` int(10) unsigned NOT NULL auto_increment,
`versionid` int(10) unsigned default NULL,
`stat_date` date default NULL,
`active_num` int(10) unsigned default NULL,
`unstall_num` int(10) unsigned default NULL,
`open_num` int(10) unsigned default NULL,
`re` varchar(255) default NULL,
PRIMARY KEY (`id`)
)
表2
CREATE TABLE `advert_version_impl` (
`id` int(10) unsigned NOT NULL auto_increment,
`version` varchar(45) default NULL,
`nickname` varchar(45) default NULL,
PRIMARY KEY (`id`)
)
表3
CREATE TABLE `adert_strategy_impl` (
`id` int(10) unsigned NOT NULL auto_increment,
`random` int(10) unsigned default NULL,
`advert_num` int(10) unsigned default NULL,
`advert_place` varchar(45) default NULL,
`speed` int(10) unsigned default NULL,
`direction` varchar(45) default NULL,
`timeperiod` int(10) unsigned default NULL,
`groupnum` int(10) unsigned default NULL,
`changenum` int(10) unsigned default NULL,
`stat_date` datetime default NULL,
PRIMARY KEY (`id`)
)
表4
CREATE TABLE `cmsthree`.`advert_click_num` (
`id` int(10) unsigned NOT NULL auto_increment,
`total_click_num` int(10) unsigned default NULL,
`only_click_num` int(10) unsigned default NULL,
`advert_type` varchar(45) default NULL,
`stat_date` date default NULL,
PRIMARY KEY (`id`)
)
查询条件日期之间,比如2007-10-1到2007-10-20
要得到的列 日期,某版本的active_num总和<在表advert_stat_impl中有版本ID(versionid)>,所有版本的总和,还有advert_click_num.total_click_num,adert_strategy_impl.advert_num所有的查询条件都是日期之间
select stat_date,active_num as active,0 as total,0 as total_click_num,0 as advert_num from advert_stat_impl a where versionid=1 and stat_date between "2007-10-23" and "2007-10-23" group by stat_date
union all
select stat_date,0 as active,sum(active_num) as total,0 as total_click_num,0 as advert_num from advert_stat_impl b where stat_date between "2007-10-23" and "2007-10-23" group by stat_date
union all
select stat_date,0 as active,0 as total,total_click_num,0 as advert_num from advert_click_num c where stat_date between "2007-10-23" and "2007-10-23" group by stat_date
union all
select stat_date,0 as active,0 as total,0 as total_click_num,advert_num from adert_strategy_impl d where stat_date between "2007-10-23" and "2007-10-23" group by stat_date ) as temptb
group by stat_date order by stat_date我是这么写的,会有什么不合理的地方,望指教