sql语句如下,功能是把历史表数据统计后插入到汇总表中
INSERT INTO st_wasav_r(STCD,IDTM,STTDRCD,AVUPZ,AVDWZ,AVGTQ)
SELECT STCD, MAX(TM) AS IDTM, '4' AS STTDRCD, avg(UPZ) AS AVUPZ, avg(DWZ) AS AVDWZ, avg(TGTQ) AS AVGTQ
FROM st_was_r
WHERE STCD = '30002'
GROUP BY STCD
两张表的数据结构如下:
CREATE TABLE `st_wasav_r` (
`STCD` varchar(8) NOT NULL,
`IDTM` datetime DEFAULT NULL,
`STTDRCD` varchar(1) NOT NULL,
`AVUPZ` double(7,3) DEFAULT NULL,
`AVDWZ` double(7,3) DEFAULT NULL,
`AVGTQ` double(9,3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `st_was_r` (
`STCD` varchar(8) NOT NULL,
`TM` datetime NOT NULL,
`UPZ` double(7,3) DEFAULT NULL,
`DWZ` double(7,3) DEFAULT NULL,
`TGTQ` double(9,3) DEFAULT NULL,
`SWCHRCD` varchar(1) DEFAULT NULL,
`SUPWPTN` varchar(1) DEFAULT NULL,
`SDWWPTN` varchar(1) DEFAULT NULL,
`MSQMT` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
数据库不支持insert嵌套select使用,返回信息sql_mode=only_full_group_by。现在无法修改数据库配置,故想到了存储过程,请问下这个用存储过程可行吗?或者有其他优雅的处理方式吗?