MySQL取出表中排序資料且不重複 重複資料刪除資料(1/2)

今日做一個功能需求，從表中排序取出前十個且資料不一致的資料，如下列a表中的值有以下資料：

代碼如下	複製代碼
mysql> select * from a; +----+----------+ | id | user | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | | 4 | zhangsan | | 5 | zhaosi | | 6 | wangwu | | 7 | lisi | | 8 | lisi | | 9 | zhaosi | +----+----------+ 9 rows in set (0.00 sec)

我們需要取出id最大的前四位，且user不能一致的資訊，按照以上資訊以及需求，我們要搜尋出結果為

zhaosi

lisi

wangwu

zhangsan 

不能按照普通的做法，如：

代碼如下	複製代碼
mysql> select * from a order by id desc limit 4; +----+--------+ | id | user | +----+--------+ | 9 | zhaosi | | 8 | lisi | | 7 | lisi | | 6 | wangwu | +----+--------+ 4 rows in set (0.00 sec)

這樣搜尋出來的有重複值，得使用distinct關鍵字

代碼如下	複製代碼
mysql> select distinct user from a order by id desc limit 4;  +----------+ | user | +----------+ | zhaosi | | wangwu | | lisi | | zhangsan | +----------+ 4 rows in set (0.00 sec)

其實應該是lisi與wangwu互換一下才是比較理想的，因為lisi最大的ID是8，而wangwu最大的ID是6，可能是lisi有一個ID為2導致的，我們把ID為2的刪除，在來試試

代碼如下

複製代碼

mysql> delete from a where id=2; Query OK, 1 row affected (0.02 sec)   mysql> select * from a; +----+----------+ | id | user | +----+----------+ | 1 | zhangsan | | 3 | wangwu | | 4 | zhangsan | | 5 | zhaosi | | 6 | wangwu | | 7 | lisi | | 8 | lisi | | 9 | zhaosi | +----+----------+ 8 rows in set (0.00 sec)   mysql> select distinct user from a order by id desc limit 4;  +----------+ | user | +----------+ | lisi | | zhaosi | | wangwu | | zhangsan | +----------+ 4 rows in set (0.00 sec)

結果正是由於前邊有較低的ID記錄影響了排序。

雖然這條語句能搜尋正確的效果，但可能排序不是那麼理想，也就是ID最大的前四位能搜尋出來，但在這四位元據裡並不是按照ID大小排序的。

例1測試資料

代碼如下

複製代碼

/* 表結構 */ DROP TABLE IF EXISTS `t1`; CREATE TABLE IF NOT EXISTS `t1`(   `id` INT(1) NOT NULL AUTO_INCREMENT,   `name` VARCHAR(20) NOT NULL,   `add` VARCHAR(20) NOT NULL,   PRIMARY KEY(`id`) )Engine=InnoDB; /* 插入測試資料 */ INSERT INTO `t1`(`name`,`add`) VALUES ('abc',"123"), ('abc',"123"), ('abc',"321"), ('abc',"123"), ('xzy',"123"), ('xzy',"456"), ('xzy',"456"), ('xzy',"456"), ('xzy',"789"), ('xzy',"987"), ('xzy',"789"), ('ijk',"147"), ('ijk',"147"), ('ijk',"852"), ('opq',"852"), ('opq',"963"), ('opq',"741"), ('tpk',"741"), ('tpk',"963"), ('tpk',"963"), ('wer',"546"), ('wer',"546"), ('once',"546"); SELECT * FROM `t1`; +----+------+-----+ | id | name | add | +----+------+-----+ |  1 | abc  | 123 | |  2 | abc  | 123 | |  3 | abc  | 321 | |  4 | abc  | 123 | |  5 | xzy  | 123 | |  6 | xzy  | 456 | |  7 | xzy  | 456 | |  8 | xzy  | 456 | |  9 | xzy  | 789 | | 10 | xzy  | 987 | | 11 | xzy  | 789 | | 12 | ijk  | 147 | | 13 | ijk  | 147 | | 14 | ijk  | 852 | | 15 | opq  | 852 | | 16 | opq  | 963 | | 17 | opq  | 741 | | 18 | tpk  | 741 | | 19 | tpk  | 963 | | 20 | tpk  | 963 | | 21 | wer  | 546 | | 22 | wer  | 546 | | 23 | once | 546 | +----+------+-----+  rows in set (0.00 sec)

尋找id最小的重複資料(只尋找id欄位)

代碼如下	複製代碼
/* 尋找id最小的重複資料(只尋找id欄位) */ SELECT DISTINCT MIN(`id`) AS `id` FROM `t1` GROUP BY `name`,`add` HAVING COUNT(1) > 1; +------+ | id   | +------+ |    1 | |   12 | |   19 | |   21 | |    6 | |    9 | +------+  rows in set (0.00 sec)

尋找所有重複資料

代碼如下

複製代碼

/* 尋找所有重複資料 */ SELECT `t1`.* FROM `t1`,(   SELECT `name`,`add`   FROM `t1`   GROUP BY `name`,`add`   HAVING COUNT(1) > 1 ) AS `t2` WHERE `t1`.`name` = `t2`.`name`   AND `t1`.`add` = `t2`.`add`; +----+------+-----+ | id | name | add | +----+------+-----+ |  1 | abc  | 123 | |  2 | abc  | 123 | |  4 | abc  | 123 | |  6 | xzy  | 456 | |  7 | xzy  | 456 | |  8 | xzy  | 456 | |  9 | xzy  | 789 | | 11 | xzy  | 789 | | 12 | ijk  | 147 | | 13 | ijk  | 147 | | 19 | tpk  | 963 | | 20 | tpk  | 963 | | 21 | wer  | 546 | | 22 | wer  | 546 | +----+------+-----+  rows in set (0.00 sec)

尋找除id最小的資料外的重複資料

代碼如下

複製代碼

/* 尋找除id最小的資料外的重複資料 */ SELECT `t1`.* FROM `t1`,(   SELECT DISTINCT MIN(`id`) AS `id`,`name`,`add`   FROM `t1`   GROUP BY `name`,`add`   HAVING COUNT(1) > 1 ) AS `t2` WHERE `t1`.`name` = `t2`.`name`   AND `t1`.`add` = `t2`.`add`   AND `t1`.`id` <> `t2`.`id`; +----+------+-----+ | id | name | add | +----+------+-----+ |  2 | abc  | 123 | |  4 | abc  | 123 | |  7 | xzy  | 456 | |  8 | xzy  | 456 | | 11 | xzy  | 789 | | 13 | ijk  | 147 | | 20 | tpk  | 963 | | 22 | wer  | 546 | +----+------+-----+  rows in set (0.00 sec)

首頁 1 2 末頁