MYSQL中,使用两个表生成第三个表的问题.
第一个表:RT180表
CREATE TABLE IF NOT EXISTS `rt180` (
`BSC` varchar(20) NOT NULL,
`StartDate` date NOT NULL,
`StartTime` time NOT NULL,
`StopDate` date NOT NULL,
`StopTime` time NOT NULL,
`CELL_CI_ADJ` varchar(30) NOT NULL,
`CELL_LAC_ADJ` varchar(30) NOT NULL,
`CELL_CI` varchar(30) NOT NULL,
`CELL_LAC` varchar(30) NOT NULL,
`NB_ADJ_BSC_INC_HO_REQ` varchar(30) NOT NULL,
`NB_ADJ_BSC_INC_HO_ATPT` varchar(30) NOT NULL,
`NB_ADJ_BSC_INC_HO_SUCC` varchar(30) NOT NULL,
UNIQUE KEY `RT180` (`BSC`,`StartDate`,`StartTime`,`StopDate`,`StopTime`,`CELL_CI_ADJ`,`CELL_LAC_ADJ`,`CELL_CI`,`CELL_LAC`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
第一个表RT180表中数据.
INSERT INTO `rt180` (`BSC`, `StartDate`, `StartTime`, `StopDate`, `StopTime`, `CELL_CI_ADJ`, `CELL_LAC_ADJ`, `CELL_CI`, `CELL_LAC`, `NB_ADJ_BSC_INC_HO_REQ`, `NB_ADJ_BSC_INC_HO_ATPT`, `NB_ADJ_BSC_INC_HO_SUCC`) VALUES
('yl_g2_bsc68', '2011-06-02', '20:00:00', '2011-06-03', '00:00:00', '47274', '37153', '30319', '37153', '1', '1', '1'),
('yl_g2_bsc68', '2011-06-02', '20:00:00', '2011-06-03', '00:00:00', '30317', '37153', '30319', '37153', '3', '3', '2'),
('yl_g2_bsc68', '2011-06-02', '20:00:00', '2011-06-03', '00:00:00', '47766', '37153', '30319', '37153', '2', '2', '1'),
('yl_g2_bsc68', '2011-06-02', '20:00:00', '2011-06-03', '00:00:00', '48291', '37153', '47764', '37153', '56', '56', '56'),
('yl_g2_bsc68', '2011-06-02', '20:00:00', '2011-06-03', '00:00:00', '47765', '37153', '47764', '37153', '139', '139', '139'),
('yl_g2_bsc68', '2011-06-02', '20:00:00', '2011-06-03', '00:00:00', '47256', '37337', '47764', '37153', '26', '26', '26');
第二个表al_btsmapping表
CREATE TABLE IF NOT EXISTS `al_btsmapping` (
`BSC` varchar(20) NOT NULL,
`CELL_NAME` varchar(100) NOT NULL,
`CELL_CI` varchar(15) NOT NULL,
`CELL_LAC` varchar(15) NOT NULL,
UNIQUE KEY `AL_BTSmapping_Index` (`BSC`,`CELL_NAME`,`CELL_CI`,`CELL_LAC`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
第二个表中数据
INSERT INTO `al_btsmapping` (`BSC`,`CELL_NAME`, `CELL_CI`, `CELL_LAC`) VALUES
('yl_g2_bsc68', 'XYD409_1', '47274', '37348'),
('yl_g2_bsc68', 'XYMD281_0', '30317', '37348'),
('yl_g2_bsc68', 'YL9292', '47766', '37348'),
('yl_g2_bsc68', 'YL9401', '48291', '37348'),
('yl_g2_bsc68', 'YLM2742', '47765', '37348'),
('yl_g2_bsc68', 'YLK642', '47256', '37348'),
('yl_g2_bsc68', 'YLM0632', '30319', '37348'),
('yl_g2_bsc68', 'YLK887', '47764', '37348');
以上为原始的两个数据用表.现在要生成地三个表.表的结构和内容如下.
第三个表al_ptopsector_hourly_0
CREATE TABLE IF NOT EXISTS `al_ptopsector_hourly_0` (
`startdate` date NOT NULL,
`starttime` time NOT NULL,
`BSC` varchar(20) NOT NULL,
`CELL_LAC` varchar(20) NOT NULL,
`CELL_CI` varchar(20) NOT NULL,
`CELL_NAME` varchar(30) NOT NULL,
`CELL_LAC_ADJ` varchar(20) NOT NULL,
`CELL_CI_ADJ` varchar(20) NOT NULL,
`CELL_NAME_ADJ` varchar(30) NOT NULL,
`NB_ADJ_BSC_INC_HO_REQ` varchar(20) NOT NULL,
`NB_ADJ_BSC_INC_HO_ATPT` varchar(20) NOT NULL,
`NB_ADJ_BSC_INC_HO_SUCC` varchar(20) NOT NULL,
UNIQUE KEY `al_PtopSector_hourly_0_Index` (`NB_ADJ_BSC_INC_HO_REQ`,`NB_ADJ_BSC_INC_HO_ATPT`,`NB_ADJ_BSC_INC_HO_SUCC`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
第三个表内数据.
INSERT INTO `al_ptopsector_hourly_0` (`startdate`,`starttime`,`BSC`, `CELL_LAC`,`CELL_CI`,`CELL_NAME`,`CELL_LAC_ADJ`,`CELL_CI_ADJ`,`CELL_NAME_ADJ`,NB_ADJ_BSC_INC_HO_REQ,NB_ADJ_BSC_INC_HO_ATPT,NB_ADJ_BSC_INC_HO_SUCC) VALUES
( '2011-06-02', '20:00:00','yl_g2_bsc68','37153','30319', 'YLM0632','37153','47766','YL9292','2', '2', '1'),
( '2011-06-02', '20:00:00', 'yl_g2_bsc68','37153','47764', 'YLK887','37337','47256','YLK642','26', '26', '26'),
( '2011-06-02', '20:00:00', 'yl_g2_bsc68','37153','47764', 'YLK887','37153','48291','YL9401','56', '56', '56');
以上各表简称为:RT180为A表,al_btsmapping为B表, al_ptopsector_hourly_0为C表.
1 A表内容按照每个小时不断的自动生成新数据.而B表内容相对固定.
2 欲使用SQL语句,每个小时直接生成第三个表的内容.
3 表之间的关系,C表内除了CELL_NAME,CELL_NAME_ADJ以外全部照搬A表内容.
4 C表内CELL_NAME取自B表内CELL_CI相对位置的CELL_NAME.
5 C表内CELL_NAME_ADJ一样取自B表内CELL_CI相对位置的CELL_NAME.
请问各位大侠,实现对第三个表的插入语句该怎么写?最好是插入语句.选择式插入.都可以,最好是一次直接生成所有字段.
[解决办法]
简要说明 al_ptopsector_hourly_0中的数据是怎样生成的