insert into temporary (faultcode,relatecode) select faultcode,relatecode from
(SELECT B.faultcode,A.faultcode FROM summary A ,(SELECT * FROM summary WHERE su_id='66') B
WHERE A.su_id<>'66' AND A.createtime BETWEEN B.Createtime-INTERVAL 50 SECOND
AND B.Createtime -INTERVAL -50 SECOND
AND a.faultcode<>b.faultcode)提示:Every derived table must have its own alias;
上面那段代码从第二行开始单拿出来是没有错的。
(SELECT B.faultcode,A.faultcode FROM summary A ,(SELECT * FROM summary WHERE su_id='66') B
WHERE A.su_id<>'66' AND A.createtime BETWEEN B.Createtime-INTERVAL 50 SECOND
AND B.Createtime -INTERVAL -50 SECOND
AND a.faultcode<>b.faultcode) t加上这个t
(SELECT B.faultcode,A.faultcode FROM summary A ,(SELECT * FROM summary WHERE su_id='66') B
WHERE A.su_id<>'66' AND A.createtime BETWEEN B.Createtime-INTERVAL 50 SECOND
AND B.Createtime -INTERVAL -50 SECOND
AND a.faultcode<>b.faultcode) a呵呵,上个帖子的问题吧
(SELECT B.faultcode,A.faultcode FROM summary A ,(SELECT * FROM summary WHERE su_id='66') B
WHERE A.su_id<>'66' AND A.createtime BETWEEN B.Createtime-INTERVAL 50 SECOND
AND B.Createtime -INTERVAL -50 SECOND
AND a.faultcode<>b.faultcode) t
(SELECT B.faultcode as new1,A.faultcode as new2 FROM summary A ,(SELECT * FROM summary WHERE su_id='66') B
WHERE A.su_id<>'66' AND A.createtime BETWEEN B.Createtime-INTERVAL 50 SECOND
AND B.Createtime -INTERVAL -50 SECOND
AND a.faultcode<>b.faultcode) a
# Source for table summary
#DROP TABLE IF EXISTS `summary`;
CREATE TABLE `summary` (
`su_id` int(11) NOT NULL AUTO_INCREMENT,
`faultcode` varchar(255) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
PRIMARY KEY (`su_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;#
# Source for table temporary
#DROP TABLE IF EXISTS `temporary`;
CREATE TABLE `temporary` (
`te_id` int(11) NOT NULL AUTO_INCREMENT,
`faultcode` varchar(255) DEFAULT NULL,
`relatecode` varchar(255) DEFAULT NULL,
PRIMARY KEY (`te_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# Source for table summary
#DROP TABLE IF EXISTS `summary`;
CREATE TABLE `summary` (
`su_id` int(11) NOT NULL AUTO_INCREMENT,
`faultcode` varchar(255) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
PRIMARY KEY (`su_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;#
# Source for table summary
#DROP TABLE IF EXISTS `summary`;
CREATE TABLE `summary` (
`su_id` int(11) NOT NULL AUTO_INCREMENT,
`faultcode` varchar(255) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
PRIMARY KEY (`su_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# Source for table temporary
#DROP TABLE IF EXISTS `temporary`;
CREATE TABLE `temporary` (
`te_id` int(11) NOT NULL AUTO_INCREMENT,
`faultcode` varchar(255) DEFAULT NULL,
`relatecode` varchar(255) DEFAULT NULL,
PRIMARY KEY (`te_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(SELECT B.faultcode AS n1,A.faultcode AS n2 FROM summary A ,(SELECT * FROM summary WHERE su_id='66') B
WHERE A.su_id<>'66' AND A.createtime BETWEEN B.Createtime-INTERVAL 50 SECOND
AND B.Createtime -INTERVAL -50 SECOND
AND a.faultcode<>b.faultcode) a你的查询中没有relatecode字段,检查一下
后面那个查询语句还是你写出来的,查询出两个字段都是faultcode,前一个faultcode对应temporary的faultcode,后一个faultcode对应temporary的relatecode。
SELECT B.faultcode,A.faultcode FROM summary A ,(SELECT * FROM summary WHERE su_id='66') B
WHERE A.su_id<>'66' AND A.createtime BETWEEN B.Createtime-INTERVAL 50 SECOND
AND B.Createtime -INTERVAL -50 SECOND
AND a.faultcode<>b.faultcode
试一下这个。
for(int i=1;i<=count;i++){
sql="INSERT INTO TEMPORARY (faultcode,relatecode) SELECT n1,n2 " +
"FROM (SELECT B.faultcode AS n1,A.faultcode AS n2 FROM summary A ," +
"(SELECT * FROM summary WHERE su_id='"+i+"') B WHERE A.su_id<>'"+i+"' " +
"AND A.createtime BETWEEN B.Createtime-INTERVAL 50 SECOND " +
"AND B.Createtime -INTERVAL -50 SECOND AND a.faultcode<>b.faultcode) a";
}还有一个问题帮我解决下吧~~~上面语句报“Failed to read auto-increment value from storage engine” 该怎么解决。
count是对summary表的计数
SQL语句显示一下
sql="INSERT INTO TEMPORARY (faultcode,relatecode) SELECT n1,n2 " +
"FROM (SELECT B.faultcode AS n1,A.faultcode AS n2 FROM summary A ," +
"(SELECT * FROM summary WHERE su_id='"+i+"') B WHERE A.su_id<>'"+i+"' " +
"AND A.createtime BETWEEN B.Createtime-INTERVAL 50 SECOND " +
"AND B.Createtime -INTERVAL -50 SECOND AND a.faultcode<>b.faultcode) a";
}
MYSQL版本?
Mysql是5.1.40的