SQL 取重复数据中ID号最小的数据 - TOMMYHU - 专注互联网开发及运营技术,提供相关资料及软件下载,奇趣网络时事评论!
Mar 26

SQL 取重复数据中ID号最小的数据 不指定

今天明基逐鹿的一个题目:

数据库操作中尽量不要使用条件“IN”语句
CREATE TABLE `zhoz_mst` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 66 ) NOT NULL ,
`kana` VARCHAR( 66 ) ,
`zz` VARCHAR( 66 ) NOT NULL
) ENGINE = MYISAM ;

插入测试数据:
INSERT INTO `zhoz0428`.`zhoz_mst` (`id` ,`title` ,`kana` ,`zz` )VALUES (NULL , 'aaa', 'bbb', 'ccc');
INSERT INTO `zhoz0428`.`zhoz_mst` (`id` ,`title` ,`kana` ,`zz` )VALUES (NULL , 'aaa', 'bbb2', '2');
INSERT INTO `zhoz0428`.`zhoz_mst` (`id` ,`title` ,`kana` ,`zz` )VALUES (NULL , 'aaa', 'bbb', '3');
INSERT INTO `zhoz0428`.`zhoz_mst` (`id` ,`title` ,`kana` ,`zz` )VALUES (NULL , 'aaa', 'bbb', '4');
INSERT INTO `zhoz0428`.`zhoz_mst` (`id` ,`title` ,`kana` ,`zz` )VALUES (NULL , 'a', 'b', '5');
INSERT INTO `zhoz0428`.`zhoz_mst` (`id` ,`title` ,`kana` ,`zz` )VALUES (NULL , 'a', NULL, '6');
INSERT INTO `zhoz0428`.`zhoz_mst` (`id` ,`title` ,`kana` ,`zz` )VALUES (NULL , 'a', NULL, '7');
INSERT INTO `zhoz0428`.`zhoz_mst` (`id` ,`title` ,`kana` ,`zz` )VALUES (NULL , 'a', 'b', '8');

      1 aaa bbb ccc
      2 aaa bbb2 2
      3 aaa bbb 3
      4 aaa bbb 4
      5 a b 5
      6 a NULL 6
      7 a NULL 7

删除保存最小ID:
select a.id, a.title, a.kana from zhoz_mst a
  where (a.title,a.kana) in  (select title,kana from zhoz_mst group by title,kana having count(*) > 1)
  and id not in (select min(id) from zhoz_mst group by title,kana having count(*)>1)

取出两字段完全相同:
select a.id, a.title, a.kana from zhoz_mst a
  where (a.title,a.kana) in  (select title,kana from zhoz_mst group by title,kana having count(*) > 1)
----------------------
      1 aaa bbb
      3 aaa bbb
      4 aaa bbb
      5 a b
      8 a b

取最大ID号
select  * from prn a
where a.flowid in(select max(flowid) from prn where tm>'2009-7-1' group by stcd  )


取最大ID号语句优化
select  * from prn a  (select max(flowid) flowid from prn where tm>'2010-1-28'group by stcd  ) b
where a.flowid=b.flowid

原文地址:http://blog.csdn.net/yfly13/article/details/4411206
▲返回顶部
Last modified by tommyhu on2012/03/26 20:43

Add a comment

Nickname

emotemotemotemotemotemotemotemotemotemotemotemotemotemotemotemot