表结构如下:
CREATE TABLE `test` (
`testid` bigint(20) NOT NULL AUTO_INCREMENT,
`memberid` bigint(20) DEFAULT NULL,
`source` smallint(6) DEFAULT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`money` decimal(8,2) DEFAULT NULL,
`sourceid` varchar(100) DEFAULT NULL,
PRIMARY KEY (`creditid`),
KEY `credit_memberid_index` (`memberid`),
CONSTRAINT `fk_memberid` FOREIGN KEY (`memberid`) REFERENCES `t_member` (`memberid`)
) ENGINE=InnoDB AUTO_INCREMENT=20904 DEFAULT CHARSET=utf8;要求source='0' 删除sourceid一样的记录中非第一条的记录,不知道怎么优化,自己写的程序对1000条就要16分钟,总记录一共是2万多点谢谢!
CREATE TABLE `test` (
`testid` bigint(20) NOT NULL AUTO_INCREMENT,
`memberid` bigint(20) DEFAULT NULL,
`source` smallint(6) DEFAULT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`money` decimal(8,2) DEFAULT NULL,
`sourceid` varchar(100) DEFAULT NULL,
PRIMARY KEY (`creditid`),
KEY `credit_memberid_index` (`memberid`),
CONSTRAINT `fk_memberid` FOREIGN KEY (`memberid`) REFERENCES `t_member` (`memberid`)
) ENGINE=InnoDB AUTO_INCREMENT=20904 DEFAULT CHARSET=utf8;要求source='0' 删除sourceid一样的记录中非第一条的记录,不知道怎么优化,自己写的程序对1000条就要16分钟,总记录一共是2万多点谢谢!
贴记录及正确结果贴出来看看
TRY:
SELECT A.* FROM TT A
LEFT JOIN
(SELECT memberid,MIN(sourceid) AS MI FROM TT GROUP BY memberid) B
ON A.memberid=B.memberid AND A.sourceid=MI
WHERE A.source='0' AND B.sourceid IS NOT NULL
SELECT A.* FROM TT A
LEFT JOIN
(SELECT sourceid,MIN(testid) AS MI FROM TT GROUP BY sourceid) B
ON A.sourceid=B.sourceid AND A.testid=MI
WHERE A.source='0' AND B.sourceid IS NOT NULL
insert into rr
SELECT A.* FROM TT A
LEFT JOIN
(SELECT sourceid,MIN(testid) AS MI FROM TT GROUP BY sourceid) B
ON A.sourceid=B.sourceid AND A.testid=MI
WHERE A.source='0' AND B.sourceid IS NOT NULL
谢谢
在SELECT去掉自增字段, OR 在表中DROP 自增字段试试
我试了去了自增还是不行
修改后停止MYSQL,再启动,试试。