如何删除数据库中重复的行?
数据库的创建语句为:
CREATE TABLE `pkt_keywords` (
`keywords_id` bigint(20) NOT NULL AUTO_INCREMENT,
`initial` char(1) NOT NULL,
`keywords` varchar(255) NOT NULL,
`search_num` bigint(20) DEFAULT '0',
PRIMARY KEY (`keywords_id`),
KEY `Index_initial` (`initial`),
KEY `Index_search` (`search_num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 我想删除数据库中initial和keywords字段相同的行
数据库的创建语句为:
CREATE TABLE `pkt_keywords` (
`keywords_id` bigint(20) NOT NULL AUTO_INCREMENT,
`initial` char(1) NOT NULL,
`keywords` varchar(255) NOT NULL,
`search_num` bigint(20) DEFAULT '0',
PRIMARY KEY (`keywords_id`),
KEY `Index_initial` (`initial`),
KEY `Index_search` (`search_num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 我想删除数据库中initial和keywords字段相同的行
解决方案 »
- 急求解决方案。Load Data InFile 'C:/备份/Data.txt',load 语法不支持中文路径
- 连接中的大于小于号
- Mysql创建表时,数据类型后面的括号和数字是什么意思?
- Java链接Mysql的小疑问
- 紧急求问
- 请问簇也就是cluster支持XP吗?我看了支持MAC,LINUX,SOLARIS
- 我想学习mysql请问初学者看哪本书好呢???
- ACCESS数据库的数据怎样迁移到POSTGRES数据库?
- mysql也開始收費了,不再是免費的了?
- ems mysql manager问题
- 请高手指点:Mysql如何实现定时上传数据到中心数据库(比较大的数据量)
- DEFINER=`root`@`localhost` 引起的java异常
FROM pkt_keywords AS A
WHERE NOT EXISTS
(
SELECT *
FROM pkt_keywords
WHERE initial = A.initial
AND keywords = A.keywords
AND keywords_id > A.keywords_id
);
FROM pkt_keywords AS A,
(
SELECT initial,keywords,MIN(keywords_id) AS keywords_id
FROM pkt_keywords
GROUP BY initial,keywords
) AS B
WHERE A.initial = B.initial
AND A.keywords = B.keywords
AND A.keywords_id > B.keywords_id;
select keywords_id from pkt_keywords a
where exists (select 1 from pkt_keywords where `initial`=a.initial and `keywords`=a.keywords and keywords_id>a.keywords_id)) b
where t.keywords_id=b.keywords_id;