暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MYSQL-SQL开发总结（三）-开发技巧2

MYSQL-SQL开发总结（三）-开发技巧2

rocklei123的技术点滴 2018-10-18
313

0. 目标

通过本文可以了解在使用mysql数据库开发时，一些开发技巧。本文比较基础，大神请绕道。

通过本人能够掌握：

  • MySQL 行列转换

  • MySQL 列转行

  • MySQL 行转列

  • MySQL 生成唯一序列号

  • MySQL 删除重复数据

使用数据及导入方法见MYSQL-SQL开发总结（一）-SQL基础

1. 测试表数据

  1. mysql> SELECT * FROM websites;
  2. +----+----------+---------------------------+-------+---------+
  3. | id | name     | url                       | alexa | country |
  4. +----+----------+---------------------------+-------+---------+
  5. |  1 | Google   | https://www.google.cm/    |     1 | USA     |
  6. |  2 | 淘宝     | https://www.taobao.com/   |    13 | CN      |
  7. |  3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |
  8. |  4 | 微博     | http://weibo.com/         |    20 | CN      |
  9. |  5 | Facebook | https://www.facebook.com/ |     3 | USA     |
  10. +----+----------+---------------------------+-------+---------+
  11. 5 rows in set (0.00 sec)

  13. mysql> SELECT * FROM access_log;
  14. +-----+---------+-------+------------+
  15. | aid | site_id | count | date       |
  16. +-----+---------+-------+------------+
  17. |   1 |       1 |    45 | 2016-05-10 |
  18. |   2 |       3 |   100 | 2016-05-13 |
  19. |   3 |       1 |   230 | 2016-05-14 |
  20. |   4 |       2 |    10 | 2016-05-14 |
  21. |   5 |       5 |   205 | 2016-05-14 |
  22. |   6 |       4 |    13 | 2016-05-15 |
  23. |   7 |       3 |   220 | 2016-05-15 |
  24. |   8 |       5 |   545 | 2016-05-16 |
  25. |   9 |       3 |   201 | 2016-05-17 |
  26. +-----+---------+-------+------------+
  27. 9 rows in set (0.00 sec)

  29. mysql>
  1. mysql> SELECT w.name ,a.count FROM websites w JOIN access_log a ON w.id = a.site_id;
  2. +----------+-------+
  3. | name     | count |
  4. +----------+-------+
  5. | Google   |    45 |
  6. | Google   |   230 |
  7. | 淘宝     |    10 |
  8. | 菜鸟教程 |   100 |
  9. | 菜鸟教程 |   220 |
  10. | 菜鸟教程 |   201 |
  11. | 微博     |    13 |
  12. | Facebook |   205 |
  13. | Facebook |   545 |
  14. +----------+-------+
  15. 9 rows in set (0.01 sec)

2.行转列场景

欲实现效果，将各个公司网站访问总数统一在一行显示。实现效果如下：

2.1 使用自连接方法实现行转列

* 思路 * 

我们可以通过单独查询其中一种应用总的访问次数，然后在对几种结果通过cross join 聚合。

  • Google 公司网站访问次数总计

  1. mysql> SELECT SUM(COUNT) AS 'Google' FROM access_log a JOIN websites w ON w.id = a.site_id AND w.name = 'Google';
  2. +--------+
  3. | Google |
  4. +--------+
  5. |    275 |
  6. +--------+
  7. 1 row in set (0.00 sec)

  9. mysql>

  • Facebook公司网站访问次数总计

  1. mysql> SELECT SUM(COUNT) AS 'Facebook' FROM access_log a JOIN websites w ON w.id = a.site_id AND w.name = 'Facebook';
  2. +----------+
  3. | Facebook |
  4. +----------+
  5. |      750 |
  6. +----------+
  7. 1 row in set (0.00 sec)

  9. mysql>

  • 菜鸟教程公司网站访问次数总计

  1. mysql> SELECT SUM(COUNT) AS '菜鸟教程' FROM access_log a JOIN websites w ON w.id = a.site_id AND w.name = '菜鸟教程';
  2. +----------+
  3. | 菜鸟教程 |
  4. +----------+
  5. |      521 |
  6. +----------+
  7. 1 row in set (0.00 sec)

  9. mysql>

  • 通过cross join 结果聚合

  1. SELECT *
  2. FROM (SELECT
  3.         SUM(COUNT) AS 'Google'
  4.       FROM access_log a
  5.         JOIN websites w
  6.           ON w.id = a.site_id
  7.             AND w.name = 'Google') G
  8.   CROSS JOIN (SELECT
  9.                 SUM(COUNT) AS 'Facebook'
  10.               FROM access_log a
  11.                 JOIN websites w
  12.                   ON w.id = a.site_id
  13.                     AND w.name = 'Facebook') F
  14.   CROSS JOIN (SELECT
  15.                 SUM(COUNT) AS '菜鸟教程'
  16.               FROM access_log a
  17.                 JOIN websites w
  18.                   ON w.id = a.site_id
  19.                     AND w.name = '菜鸟教程') C;
  1. mysql>
  2. +--------+----------+----------+
  3. | Google | Facebook | 菜鸟教程 |
  4. +--------+----------+----------+
  5. |    275 |      750 |      521 |
  6. +--------+----------+----------+
  7. 1 row in set (0.01 sec)

  9. mysql>

* 缺点：* 

将原来查询的结果每一行单独查询出来，再进行拼接。因此每增加一个同学就增加一个SELECT语句。并且是通过交叉连接，要保证每个查询的结果只能是一个，不然没办法通过交叉连接实现转换。

2.2 使用CASE实现行转列

  1. SELECT
  2.   SUM(CASE WHEN NAME = 'Google' THEN COUNT END ) AS 'Google',
  3.   SUM(CASE WHEN NAME = 'Facebook' THEN COUNT END ) AS 'Facebook',
  4.   SUM(CASE WHEN NAME = '菜鸟教程' THEN COUNT END ) AS '菜鸟教程'
  5. FROM websites w
  6.   JOIN access_log a
  7.     ON w.id = a.site_id;

3 列转行场景

3.1 单列转多行场景

3.1.1 单列转多行场景实际用途

  • （1）属性拆分

  • （2）ETL数据处理

3.1.2 单列转多行场景演示-权限拆分

* 原始数据建表语句及数据 *

  1. Create Table: CREATE TABLE `user_roles` (
  2.   `user_roles_id` int(11) DEFAULT NULL,
  3.   `role_name` varchar(100) DEFAULT NULL,
  4.   `major_roles` varchar(100) DEFAULT NULL,
  5.   `creation_time` datetime DEFAULT NULL
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8

  8. mysql>

  10. INSERT INTO USER_ROLES
  11.             (USER_ROLES_ID,
  12.              ROLE_NAME,
  13.              MAJOR_ROLES,
  14.              CREATION_TIME)
  15. VALUES (1,
  16.         'middleware',
  17.         '2,21,22,23,24',
  18.         CURTIME());
  19. INSERT INTO USER_ROLES (USER_ROLES_ID, ROLE_NAME, MAJOR_ROLES, CREATION_TIME)
  20. VALUES (2, 'db','9,10', CURTIME());
  21. INSERT INTO USER_ROLES (USER_ROLES_ID, ROLE_NAME, MAJOR_ROLES, CREATION_TIME)
  22. VALUES (3, 'SYSTEM',  '4,6,7,8', CURTIME());
  23. INSERT INTO USER_ROLES (USER_ROLES_ID, ROLE_NAME, MAJOR_ROLES, CREATION_TIME)
  24. VALUES (4, 'bcy109', '21,22,25,26,27,28,23,24,16',CURTIME());
  25. INSERT INTO USER_ROLES (USER_ROLES_ID, ROLE_NAME, MAJOR_ROLES, CREATION_TIME)
  26. VALUES (5, 'bcytest', '17,18,23,24',CURTIME());

* 表数据 *

* 预期实现目标 *

3.1.3 使用序列化表的方法实现行转列

* 建立序列表：*

  1. CREATE TABLE  tb_sequence(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
  2. INSERT INTO tb_sequence VALUES(),(),(),(),(),(),(),(),();

  4. mysql> SELECT * FROM tb_sequence;
  5. +----+
  6. | id |
  7. +----+
  8. |  1 |
  9. |  2 |
  10. |  3 |
  11. |  4 |
  12. |  5 |
  13. |  6 |
  14. |  7 |
  15. |  8 |
  16. |  9 |
  17. +----+
  18. 9 rows in set (0.01 sec)

  20. mysql>

* 最终语句 *

  1. SELECT b.user_roles_id,b.role_name,
  2. REPLACE(SUBSTRING(SUBSTRING_INDEX(major_roles,',',a.id),CHAR_LENGTH(SUBSTRING_INDEX(major_roles,',',a.id-1))+1),',','') AS major_roles FROM tb_sequence a CROSS JOIN(SELECT user_roles_id,role_name,CONCAT(major_roles,',') AS major_roles,
  3. LENGTH(major_roles)-LENGTH(REPLACE(major_roles,',',''))+1 size
  4. FROM user_roles b ORDER BY b.user_roles_id) b ON a.id<=b.size;

* 语句解读 *

  • 内部子查询：目的是在 major_roles 每行数据增加一个逗号，在用总长度减去没有逗号的情况时的长度 = size （表示有几个权限，将要转成几行记录）

  1. mysql> SELECT user_roles_id,role_name,CONCAT(major_roles,',') AS major_roles,
  2.     -> LENGTH(major_roles)-LENGTH(REPLACE(major_roles,',',''))+1 size
  3.     -> FROM user_roles b ORDER BY b.user_roles_id;
  4. +---------------+------------+-----------------------------+------+
  5. | user_roles_id | role_name  | major_roles                 | size |
  6. +---------------+------------+-----------------------------+------+
  7. |             1 | middleware | 2,21,22,23,24,              |    5 |
  8. |             2 | db         | 9,10,                       |    2 |
  9. |             3 | SYSTEM     | 4,6,7,8,                    |    4 |
  10. |             4 | bcy109     | 21,22,25,26,27,28,23,24,16, |    9 |
  11. |             5 | bcytest    | 17,18,23,24,                |    4 |
  12. +---------------+------------+-----------------------------+------+
  13. 5 rows in set (0.00 sec)

  15. mysql>

  • 外部查询：外层查询根据序列表 cross join 子查询结果

3.2 多列转多行场景

3.2.1 多列转多行场景实际用途

  • （1）学生成绩查询

  • （2） ETL 数据抽取

3.2.2 多列转多行案例分析-学生成绩行列转换

* 建表语句及原始数据插入语句 *

  1. CREATE TABLE `TB_GRADE` (
  2.   `ID` INT(10) NOT NULL AUTO_INCREMENT,
  3.   `USER_NAME` VARCHAR(20) DEFAULT NULL,
  4.   `CN_SCORE` FLOAT DEFAULT '0',
  5.   `MATCH_SCORE` FLOAT DEFAULT '0',
  6.   `EN_SCORE` FLOAT DEFAULT '0',
  7.   PRIMARY KEY (`ID`)
  8. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

  10. INSERT INTO TB_GRADE(USER_NAME, CN_SCORE, MATCH_SCORE,EN_SCORE)  VALUES
  11. ("张三",58 ,34,72),
  12. ("李四",87 ,79,72),
  13. ("王五",88 ,76,90);

* 表中原始数据 *

  1. mysql> SELECT * FROM TB_GRADE;
  2. +----+-----------+----------+-------------+----------+
  3. | ID | USER_NAME | CN_SCORE | MATCH_SCORE | EN_SCORE |
  4. +----+-----------+----------+-------------+----------+
  5. |  1 | 张三      |       58 |          34 |       72 |
  6. |  2 | 李四      |       87 |          79 |       72 |
  7. |  3 | 王五      |       88 |          76 |       90 |
  8. +----+-----------+----------+-------------+----------+
  9. 3 rows in set (0.02 sec)

  11. mysql>

* 预期实现目标 *

  1. +-----------+-------------+------+
  2. | user_name | 学科        | 成绩 |
  3. +-----------+-------------+------+
  4. | 张三      | CN_SCORE    |   58 |
  5. | 李四      | CN_SCORE    |   87 |
  6. | 王五      | CN_SCORE    |   88 |
  7. | 张三      | MATCH_SCORE |   34 |
  8. | 李四      | MATCH_SCORE |   79 |
  9. | 王五      | MATCH_SCORE |   76 |
  10. | 张三      | EN_SCORE    |   72 |
  11. | 李四      | EN_SCORE    |   72 |
  12. | 王五      | EN_SCORE    |   90 |
  13. +-----------+-------------+------+

3.2.3 使用UNION方法实现列转行

* 语句拆分解读 *

  1. mysql> SELECT user_name,'CN_SCORE' AS '语文',CN_SCORE AS '成绩' FROM TB_GRADE a;
  2. +-----------+----------+------+
  3. | user_name | 语文     | 成绩 |
  4. +-----------+----------+------+
  5. | 张三      | CN_SCORE |   58 |
  6. | 李四      | CN_SCORE |   87 |
  7. | 王五      | CN_SCORE |   88 |
  8. +-----------+----------+------+
  9. 3 rows in set (0.00 sec)

  11. mysql> SELECT user_name,'MATCH_SCORE' AS '数学',MATCH_SCORE AS '成绩' FROM TB_GRADE a;
  12. +-----------+-------------+------+
  13. | user_name | 数学        | 成绩 |
  14. +-----------+-------------+------+
  15. | 张三      | MATCH_SCORE |   34 |
  16. | 李四      | MATCH_SCORE |   79 |
  17. | 王五      | MATCH_SCORE |   76 |
  18. +-----------+-------------+------+
  19. 3 rows in set (0.00 sec)

  21. mysql> SELECT user_name,'EN_SCORE' AS '英语',EN_SCORE AS '成绩' FROM TB_GRADE a;
  22. +-----------+----------+------+
  23. | user_name | 英语     | 成绩 |
  24. +-----------+----------+------+
  25. | 张三      | EN_SCORE |   72 |
  26. | 李四      | EN_SCORE |   72 |
  27. | 王五      | EN_SCORE |   90 |
  28. +-----------+----------+------+
  29. 3 rows in set (0.00 sec)

* 最终语句 *

  1. SELECT user_name,'CN_SCORE' AS '学科',CN_SCORE AS '成绩' FROM TB_GRADE a
  2. UNION ALL
  3. SELECT user_name,'MATCH_SCORE' AS '学科',MATCH_SCORE AS '成绩' FROM TB_GRADE a
  4. UNION ALL
  5. SELECT user_name,'EN_SCORE' AS '学科',EN_SCORE AS '成绩' FROM TB_GRADE a;

* 最终效果 *

  1. mysql> SELECT user_name,'CN_SCORE' AS '学科',CN_SCORE AS '成绩' FROM TB_GRADE a
  2.     -> UNION ALL
  3.     -> SELECT user_name,'MATCH_SCORE' AS '学科',MATCH_SCORE AS '成绩' FROM TB_GRADE a
  4.     -> UNION ALL
  5.     -> SELECT user_name,'EN_SCORE' AS '学科',EN_SCORE AS '成绩' FROM TB_GRADE a;
  6. +-----------+-------------+------+
  7. | user_name | 学科        | 成绩 |
  8. +-----------+-------------+------+
  9. | 张三      | CN_SCORE    |   58 |
  10. | 李四      | CN_SCORE    |   87 |
  11. | 王五      | CN_SCORE    |   88 |
  12. | 张三      | MATCH_SCORE |   34 |
  13. | 李四      | MATCH_SCORE |   79 |
  14. | 王五      | MATCH_SCORE |   76 |
  15. | 张三      | EN_SCORE    |   72 |
  16. | 李四      | EN_SCORE    |   72 |
  17. | 王五      | EN_SCORE    |   90 |
  18. +-----------+-------------+------+
  19. 9 rows in set (0.01 sec)

  21. mysql>

3.2.4 使用序列化表的方法实现列转行

* 语句拆分解析 *

  • 通过序列固定获取学科

  1. mysql> SELECT user_name, CASE WHEN c.id=1 THEN 'CN_SCORE' WHEN c.id=2 THEN 'MATCH_SCORE' WHEN c.id=3 THEN 'EN_SCORE' END AS '学科'
  2.     -> FROM   TB_GRADE a
  3.     -> CROSS JOIN tb_sequence c WHERE c.id<=3 ;
  4. +-----------+-------------+
  5. | user_name | 学科        |
  6. +-----------+-------------+
  7. | 张三      | CN_SCORE    |
  8. | 李四      | CN_SCORE    |
  9. | 王五      | CN_SCORE    |
  10. | 张三      | MATCH_SCORE |
  11. | 李四      | MATCH_SCORE |
  12. | 王五      | MATCH_SCORE |
  13. | 张三      | EN_SCORE    |
  14. | 李四      | EN_SCORE    |
  15. | 王五      | EN_SCORE    |
  16. +-----------+-------------+
  17. 9 rows in set (0.00 sec)

  19. mysql>

  • 通过序列固定获取成绩

  1. mysql> SELECT user_name, COALESCE(CASE WHEN c.id=1 THEN CN_SCORE END, CASE WHEN c.id=2 THEN MATCH_SCORE END, CASE WHEN c.id=3 THEN EN_SCORE END) AS '成绩'
  2.     -> FROM   TB_GRADE a
  3.     -> CROSS JOIN tb_sequence c WHERE c.id<=3
  4.     -> ;
  5. +-----------+------+
  6. | user_name | 成绩 |
  7. +-----------+------+
  8. | 张三      |   58 |
  9. | 李四      |   87 |
  10. | 王五      |   88 |
  11. | 张三      |   34 |
  12. | 李四      |   79 |
  13. | 王五      |   76 |
  14. | 张三      |   72 |
  15. | 李四      |   72 |
  16. | 王五      |   90 |
  17. +-----------+------+
  18. 9 rows in set (0.00 sec)

  20. mysql>

  • 最终查询

  1. SELECT user_name, CASE WHEN c.id=1 THEN 'CN_SCORE' WHEN c.id=2 THEN 'MATCH_SCORE' WHEN c.id=3 THEN 'EN_SCORE' END AS '学科',
  2. COALESCE(CASE WHEN c.id=1 THEN CN_SCORE END, CASE WHEN c.id=2 THEN MATCH_SCORE END, CASE WHEN c.id=3 THEN EN_SCORE END) AS '成绩'
  3. FROM   TB_GRADE a
  4. CROSS JOIN tb_sequence c WHERE c.id<=3 ORDER BY user_name;
  1. mysql> SELECT user_name, CASE WHEN c.id=1 THEN 'CN_SCORE' WHEN c.id=2 THEN 'MATCH_SCORE' WHEN c.id=3 THEN 'EN_SCORE' END AS '学科',
  2.     -> COALESCE(CASE WHEN c.id=1 THEN CN_SCORE END, CASE WHEN c.id=2 THEN MATCH_SCORE END, CASE WHEN c.id=3 THEN EN_SCORE END) AS '成绩'
  3.     -> FROM   TB_GRADE a
  4.     -> CROSS JOIN tb_sequence c WHERE c.id<=3 ORDER BY user_name;
  5. +-----------+-------------+------+
  6. | user_name | 学科        | 成绩 |
  7. +-----------+-------------+------+
  8. | 张三      | MATCH_SCORE |   34 |
  9. | 张三      | EN_SCORE    |   72 |
  10. | 张三      | CN_SCORE    |   58 |
  11. | 李四      | CN_SCORE    |   87 |
  12. | 李四      | MATCH_SCORE |   79 |
  13. | 李四      | EN_SCORE    |   72 |
  14. | 王五      | CN_SCORE    |   88 |
  15. | 王五      | MATCH_SCORE |   76 |
  16. | 王五      | EN_SCORE    |   90 |
  17. +-----------+-------------+------+
  18. 9 rows in set (0.00 sec)

  20. mysql>

4. 生成唯一序列号

4.1 唯一序列号场景及序列号生成方式

* 需要使用唯一序列号的场景：*

1 作为数据库主键。

2 业务序列号。

* 生成序列号的方法：*

  • MySQL：AUTO_INCREMENT


  • SQLServer：IDENTITY/SEQUENCE


  • Oracle：SEQUENCE


  • PgSQL：SEQUENCE


* 如何选择生成序列号的方式：*

【原则】：优先选择系统提供的序列号生成方式。

【优点】：

1 控制并发；

2 不重复，保证序列号的唯一性。

【缺点】：序列号不连续（数据空洞），例如 1、2、4。

【原因】：对已有的数据的删除，以及事务回滚等方式不会影响自增长的序号，例如已有数据 1、2、3，删除 3 号数据。之后再插入一条数据，此时数据表的数据为 1、2、4。

4.2 建立特殊需求的序列号

创建订单号，订单序列号格式如下：YYYYMMDDnnnnnnn。如201810170000002

* 基础表 *

  1. CREATE TABLE order_seq(
  2. timestr INT UNSIGNED,
  3. order_sn INT UNSIGNED
  4. );

* 存储过程 *

  1. DELIMITER //
  2.   CREATE PROCEDURE seq_no()
  3.   BEGIN
  4.       DECLARE v_cnt   INT;
  5.       DECLARE v_timestr INT;
  6.       DECLARE rowcount BIGINT;
  7.       SET     v_timestr = DATE_FORMAT(NOW(), '%Y%m%d');
  8.       SELECT  ROUND(RAND() * 100, 0) + 1 INTO v_cnt;
  9.       START   TRANSACTION;
  10.         UPDATE  order_seq SET order_sn = order_sn + v_cnt WHERE timestr = v_timestr;
  11.         IF      
  12.             ROW_COUNT() = 0 THEN INSERT INTO order_seq(timestr, order_sn) VALUES(v_timestr,v_cnt);
  13.         END IF;
  14.         SELECT CONCAT(v_timestr, LPAD(order_sn, 7, 0)) AS order_sn
  15.             FROM order_seq
  16.         WHERE timestr = v_timestr;
  17.       COMMIT;
  18.   END
  19. //
  20. DELIMITER ;

* 结果展示 *

此存储过程经测试，每秒中可以生成1千个订单号

  1. mysql> call seq_no();
  2. +-----------------+
  3. | order_sn        |
  4. +-----------------+
  5. | 201810170000135 |
  6. +-----------------+
  7. 1 row in set (0.01 sec)

  9. Query OK, 0 rows affected (0.05 sec)

  11. mysql> select * from order_seq;
  12. +----------+----------+
  13. | timestr  | order_sn |
  14. +----------+----------+
  15. | 20181017 |      135 |
  16. +----------+----------+
  17. 1 row in set (0.00 sec)

  19. mysql>

知识点：

  • 1、在sql语句中添加变量。 declare @localvariable datatype 声明时需要指定变量的类型，可以使用SET、SELECT、SELECT...INTO对变量进行赋值，在sql语句中就可以使用@local_variable来调用变量。


  • 2、RAND()返回一个介于 0 到 1（不包括 0 和 1）之间的伪随机 float 值。


  • 3、事务


  • 4、ROW_COUNT()函数返回查询语句执行后，被影响的列数目


  • 5、IF...THEN...END IF;


5. 删除重复数据

5.1 查询数据是否重复

  1. mysql> SELECT * FROM runoob_tbl;
  2. +-----------+--------------+---------------+-----------------+
  3. | runoob_id | runoob_title | runoob_author | submission_date |
  4. +-----------+--------------+---------------+-----------------+
  5. |         1 | 学习 PHP     | 菜鸟教程      | 2017-04-12      |
  6. |         2 | 学习 MySQL   | 菜鸟教程      | 2017-04-12      |
  7. |         3 | 学习 Java    | RUNOOB.COM    | 2015-05-01      |
  8. |         4 | 学习 Python  | RUNOOB.COM    | 2016-03-06      |
  9. |         5 | 学习 C       | FK            | 2017-04-05      |
  10. +-----------+--------------+---------------+-----------------+
  11. 5 rows in set (0.00 sec)

  13. mysql>
  14. mysql> SELECT runoob_author,COUNT(*) FROM runoob_tbl GROUP BY runoob_author HAVING COUNT(*)>1;
  15. +---------------+----------+
  16. | runoob_author | COUNT(*) |
  17. +---------------+----------+
  18. | RUNOOB.COM    |        2 |
  19. | 菜鸟教程      |        2 |
  20. +---------------+----------+
  21. 2 rows in set (0.00 sec)

  23. mysql>

5.2 删除重复数据，对于相同数据保留ID最大的

* sql 语句*

  1. DELETE a
  2. FROM runoob_tbl a
  3.   JOIN (SELECT
  4.           runoob_author,
  5.           COUNT(*),
  6.           MAX(runoob_id) AS runoob_id
  7.         FROM runoob_tbl
  8.         GROUP BY runoob_author
  9.         HAVING COUNT( * ) > 1) b
  10.     ON a.runoob_author = b.runoob_author
  11. WHERE a.runoob_id < b.runoob_id;

* 效果 *

  1. mysql> DELETE a
  2.     -> FROM runoob_tbl a
  3.     ->   JOIN (SELECT
  4.     ->           runoob_author,
  5.     ->           COUNT(*),
  6.     ->           MAX(runoob_id) AS runoob_id
  7.     ->         FROM runoob_tbl
  8.     ->         GROUP BY runoob_author
  9.     ->         HAVING COUNT( * ) > 1) b
  10.     ->     ON a.runoob_author = b.runoob_author
  11.     -> WHERE a.runoob_id < b.runoob_id;
  12. Query OK, 2 rows affected, 1 warning (0.22 sec)

  14. mysql> select * from  runoob_tbl;
  15. +-----------+--------------+---------------+-----------------+
  16. | runoob_id | runoob_title | runoob_author | submission_date |
  17. +-----------+--------------+---------------+-----------------+
  18. |         2 | 学习 MySQL   | 菜鸟教程      | 2017-04-12      |
  19. |         4 | 学习 Python  | RUNOOB.COM    | 2016-03-06      |
  20. |         5 | 学习 C       | FK            | 2017-04-05      |
  21. +-----------+--------------+---------------+-----------------+
  22. 3 rows in set (0.00 sec)

  24. mysql>

6 希望大家手动敲一遍代码，会收获颇丰！

7 参考

[菜鸟教程 http://www.runoob.com/sql/sql-tutorial.html]

[慕课网sqlercn老师-mysql 开发技巧 https://www.imooc.com/learn/427]

10.欢迎关注米宝窝，持续更新中，谢谢！

米宝窝 https://rocklei123.github.io/


mysql
文章转载自rocklei123的技术点滴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论