SQL开发技巧

SQL语句分类

• DDL：数据定义语言
• TPL：事务处理语言
• DCL：数据控制语言-grant
• DML：数据操作语言-crud

join从句

操作表数据：

mysql> select * from user1;
+----+-----------+----------+
| id | user_name | over     |
+----+-----------+----------+
|  1 | 唐僧      | 唐僧成佛 |
|  2 | 猪八戒    | 净坛使者 |
|  3 | 孙悟空    | 斗战胜佛 |
|  4 | 沙僧      | 金身罗汉 |
+----+-----------+----------+
4 rows in set (0.00 sec)

mysql> select * from user2;
+----+-----------+--------+
| id | user_name | over   |
+----+-----------+--------+
|  1 | 孙悟空    | 成佛   |
|  2 | 牛魔王    | 妖怪吧 |
|  3 | 蛟魔王    | 妖怪吧 |
|  4 | 鹏魔王    | 妖怪吧 |
|  5 | 狮驼王    | 妖怪吧 |
+----+-----------+--------+
5 rows in set (0.00 sec)

内连接 inner

基于连接谓词将两张表的列组合在一起，产生新的结果表，交集。

mysql> select a.user_name, a.over, b.over from user1 a join user2 b on a.user_name = b.user_name;
+-----------+----------+------+
| user_name | over     | over |
+-----------+----------+------+
| 孙悟空    | 斗战胜佛 | 成佛 |
+-----------+----------+------+
1 row in set (0.00 sec)

全外连接 full outer

并集，MySQL中不支持直接的full join。用union all来实现。

mysql> select a.user_name, a.over, b.over from user1 a left join user2 b on a.user_name = b. user_name
    -> union all
    -> select b.user_name, b.over, a.over
    -> from user1 a right join user2 b on b.user_name = a.user_name;
+-----------+----------+----------+
| user_name | over     | over     |
+-----------+----------+----------+
| 孙悟空    | 斗战胜佛 | 成佛     |
| 唐僧      | 唐僧成佛 | NULL     |
| 猪八戒    | 净坛使者 | NULL     |
| 沙僧      | 金身罗汉 | NULL     |
| 孙悟空    | 成佛     | 斗战胜佛 |
| 牛魔王    | 妖怪吧   | NULL     |
| 蛟魔王    | 妖怪吧   | NULL     |
| 鹏魔王    | 妖怪吧   | NULL     |
| 狮驼王    | 妖怪吧   | NULL     |
+-----------+----------+----------+
9 rows in set (0.00 sec)

左外连接 left outer

以左表为准。

mysql> select a.user_name, a.over, b.over from user1 a left join user2 b on a.user_name = b.user_name where b.user_name is null;
+-----------+----------+------+
| user_name | over     | over |
+-----------+----------+------+
| 唐僧      | 唐僧成佛 | NULL |
| 猪八戒    | 净坛使者 | NULL |
| 沙僧      | 金身罗汉 | NULL |
+-----------+----------+------+
3 rows in set (0.00 sec)
mysql> select a.user_name, a.over, b.over from user1 a left join user2 b on a.user_name = b.user_name where b.user_name is not null;
+-----------+----------+------+
| user_name | over     | over |
+-----------+----------+------+
| 孙悟空    | 斗战胜佛 | 成佛 |
+-----------+----------+------+
1 row in set (0.00 sec)

右外连接 right outer

以右表为准。

mysql> select b.user_name, b.over, a.over from user1 a right join user2 b on b.user_name = a.user_name where a.user_name is null;
+-----------+--------+------+
| user_name | over   | over |
+-----------+--------+------+
| 牛魔王    | 妖怪吧 | NULL |
| 蛟魔王    | 妖怪吧 | NULL |
| 鹏魔王    | 妖怪吧 | NULL |
| 狮驼王    | 妖怪吧 | NULL |
+-----------+--------+------+
4 rows in set (0.00 sec)

mysql> select b.user_name, b.over, a.over from user1 a right join user2 b on b.user_name = a.user_name where a.user_name is not null;
+-----------+------+----------+
| user_name | over | over     |
+-----------+------+----------+
| 孙悟空    | 成佛 | 斗战胜佛 |
+-----------+------+----------+
1 row in set (0.00 sec)

交叉连接 cross

实际中需要避免。

mysql> select a.user_name, a.over, b.over from user1 a cross join user2 b;
+-----------+----------+--------+
| user_name | over     | over   |
+-----------+----------+--------+
| 唐僧      | 唐僧成佛 | 成佛   |
| 猪八戒    | 净坛使者 | 成佛   |
| 孙悟空    | 斗战胜佛 | 成佛   |
| 沙僧      | 金身罗汉 | 成佛   |
| 唐僧      | 唐僧成佛 | 妖怪吧 |
| 猪八戒    | 净坛使者 | 妖怪吧 |
| 孙悟空    | 斗战胜佛 | 妖怪吧 |
| 沙僧      | 金身罗汉 | 妖怪吧 |
| 唐僧      | 唐僧成佛 | 妖怪吧 |
| 猪八戒    | 净坛使者 | 妖怪吧 |
| 孙悟空    | 斗战胜佛 | 妖怪吧 |
| 沙僧      | 金身罗汉 | 妖怪吧 |
| 唐僧      | 唐僧成佛 | 妖怪吧 |
| 猪八戒    | 净坛使者 | 妖怪吧 |
| 孙悟空    | 斗战胜佛 | 妖怪吧 |
| 沙僧      | 金身罗汉 | 妖怪吧 |
| 唐僧      | 唐僧成佛 | 妖怪吧 |
| 猪八戒    | 净坛使者 | 妖怪吧 |
| 孙悟空    | 斗战胜佛 | 妖怪吧 |
| 沙僧      | 金身罗汉 | 妖怪吧 |
+-----------+----------+--------+
20 rows in set (0.00 sec)

如何更新过滤条件中包括自身的表

直接更新是不可以的：

mysql> update user1 set over='齐天大圣' where user1.user_name in (
    -> select b.user_name
    -> from user1 a inner join user2 b on
    -> a.user_name=b.user_name);
ERROR 1093 (HY000): You can't specify target table 'user1' for update in FROM clause

先join，更新join之后的表方法:

mysql> update user1 a join(
    -> select b.user_name
    -> from user1 a inner join user2 b on
    -> a.user_name=b.user_name) b on a.user_name = b.user_name set a.over='齐天大圣';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user1;
+----+-----------+----------+
| id | user_name | over     |
+----+-----------+----------+
|  1 | 唐僧      | 唐僧成佛 |
|  2 | 猪八戒    | 净坛使者 |
|  3 | 孙悟空    | 齐天大圣 |
|  4 | 沙僧      | 金身罗汉 |
+----+-----------+----------+
4 rows in set (0.00 sec)

join优化子查询

低效的子查询：

mysql> select a.user_name, a.over, (select over from user2 b where a.user_name=b.user_name) as over2 from user1 a;
+-----------+----------+-------+
| user_name | over     | over2 |
+-----------+----------+-------+
| 唐僧      | 唐僧成佛 | NULL  |
| 猪八戒    | 净坛使者 | NULL  |
| 孙悟空    | 齐天大圣 | 成佛  |
| 沙僧      | 金身罗汉 | NULL  |
+-----------+----------+-------+
4 rows in set (0.00 sec)
mysql> explain select a.user_name, a.over, (select over from user2 b where a.user_name=b.user_name) as over2 from user1 a\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: b
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
2 rows in set, 2 warnings (0.00 sec)

left join优化后：

mysql> select a.user_name, a.over, b.over as over2 from user1 a left join user2 b on a.user_name=b.user_name;
+-----------+----------+-------+
| user_name | over     | over2 |
+-----------+----------+-------+
| 孙悟空    | 齐天大圣 | 成佛  |
| 唐僧      | 唐僧成佛 | NULL  |
| 猪八戒    | 净坛使者 | NULL  |
| 沙僧      | 金身罗汉 | NULL  |
+-----------+----------+-------+
4 rows in set (0.00 sec)

mysql> explain select a.user_name, a.over, b.over as over2 from user1 a left join user2 b on a.user_name=b.user_name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

join优化聚合子查询

mysql> select a.user_name, b.timestr, b.kills from user1 a join user_kills b on a.id=b.user_id where b.kills=(select max(c.kills) from user_kills c where c.user_id = b.user_id);
+-----------+-----------+-------+
| user_name | timestr   | kills |
+-----------+-----------+-------+
| 猪八戒    | 2013-2-5  |    12 |
| 沙僧      | 2013-1-10 |     3 |
+-----------+-----------+-------+
2 rows in set (0.00 sec)
mysql> explain select a.user_name, b.timestr, b.kills from user1 a join user_kills b on a.id=b.user_id where b.kills=(select max(c.kills) from user_kills c where c.user_id = b.user_id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: b
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: c
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
3 rows in set, 2 warnings (0.00 sec)

优化后：

mysql> select a.user_name, b.timestr, b.kills from user1 a join user_kills b on a.id = b.user_id join user_kills c on c.user_id=b.user_id group by a.user_name, b.timestr, b.kills having b.kills = max(c.kills);
+-----------+-----------+-------+
| user_name | timestr   | kills |
+-----------+-----------+-------+
| 沙僧      | 2013-1-10 |     3 |
| 猪八戒    | 2013-2-5  |    12 |
+-----------+-----------+-------+
2 rows in set (0.00 sec)

mysql> explain select a.user_name, b.timestr, b.kills from user1 a join user_kills b on a.id = b.user_id join user_kills c on c.user_id=b.user_id group by a.user_name, b.timestr, b.kills having b.kills = max(c.kills)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test1.b.user_id
         rows: 1
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0.00 sec)

如何实现分组选择

每一组数据中选出前几名的情景，可以分组逐个进行查询，这种情况下，分组比较多，需要执行同一次查询很多次，而且增加了应用程序与数据库的交互次数，增加了数据库执行查询的次数，不符合批处理的原则，也会增加网络流量，因此实际中不使用这种方式。

优化方法:

mysql> select d.user_name, c.timestr, kills from (select user_id, timestr, kills, (select count(*) from user_kills b where b.user_id=a.user_id and a.kills<=b.kills) as cnt from user_kills a group by user_id, timestr, kills) c join user1 d on c.user_id=d.id where cnt<=2;
+-----------+-----------+-------+
| user_name | timestr   | kills |
+-----------+-----------+-------+
| 猪八戒    | 2013-2-5  |    12 |
| 猪八戒    | 2013-1-10 |    10 |
| 沙僧      | 2013-1-10 |     3 |
+-----------+-----------+-------+
3 rows in set (0.00 sec)

如何进行行列转换

行转列

报表统计，汇总显示会经常使用行转列的操作。

• 打印流水单：

  mysql> select a.user_name, b.kills from user1 a join user_kills b on a.id=b.user_id;
  +-----------+-------+
  | user_name | kills |
  +-----------+-------+
  | 猪八戒    |    10 |
  | 猪八戒    |     2 |
  | 猪八戒    |    12 |
  | 沙僧      |     3 |
  | 猪八戒    |     6 |
  | 孙悟空    |    50 |
  | 孙悟空    |    12 |
  | 孙悟空    |     5 |
  | 猪八戒    |    15 |
  | 沙僧      |   123 |
  | 沙僧      |    14 |
  | 沙僧      |    10 |
  +-----------+-------+
  12 rows in set (0.00 sec)

• 汇总通过sum和group by

  mysql> select a.user_name, sum(b.kills) from user1 a join user_kills b on a.id=b.user_id group by a.user_name;
  +-----------+--------------+
  | user_name | sum(b.kills) |
  +-----------+--------------+
  | 孙悟空    |           67 |
  | 沙僧      |          150 |
  | 猪八戒    |           45 |
  +-----------+--------------+
  3 rows in set (0.00 sec)

• 通过cross join来实现行转列, 存在性能问题。

  mysql> select * from (select sum(b.kills) as '猪八戒' from user1 a join user_kills b on a.id=b.user_id and user_name='猪八戒') a cross join (select sum(b.kills) as '孙悟空' from user1 a join user_kills b on a.id=b.user_id and user_name='孙悟空') b cross join (select sum(b.kills) as '沙僧' from user1 a join user_kills b on a.id=b.user_id and user_name='沙僧') c;
  +--------+--------+------+
  | 猪八戒 | 孙悟空 | 沙僧 |
  +--------+--------+------+
  |     45 |     67 |  150 |
  +--------+--------+------+
  1 row in set (0.00 sec)

• 使用case语句来实现行转列

  mysql> select sum(case when user_name='孙悟空' then kills end) as '孙悟空', sum(case when user_name='猪八戒' then kills end) as '猪八戒', sum(case when user_name='沙僧' then kills end) as '沙僧' from user1 a join user_kills b on  a.id = b.user_id;
  +--------+--------+------+
  | 孙悟空 | 猪八戒 | 沙僧 |
  +--------+--------+------+
  |     67 |     45 |  150 |
  +--------+--------+------+
  1 row in set (0.00 sec)

列转行

属性拆分，比如权限分隔。

• 利用序列表实现列转行

  mysql> SELECT
  user_name,REPLACE(substring(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') AS mobile
  FROM tb_sequence AS a
  CROSS JOIN
  (SELECT user_name, CONCAT(mobile,',') AS mobile, (LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1) AS size FROM user1) AS b
  ON a.id <= b.size;
  +-----------+----------+
  | user_name | mobile   |
  +-----------+----------+
  | 唐僧      | 12123123 |
  | 唐僧      | 123      |
  | 唐僧      | 123432   |
  | 唐僧      | 2131     |
  | 唐僧      | 4213     |
  | 唐僧      | 3421     |
  | 猪八戒    | 12123123 |
  | 猪八戒    | 123      |
  | 猪八戒    | 123432   |
  | 猪八戒    | 2131     |
  | 猪八戒    | 4213     |
  | 猪八戒    | 3421     |
  | 孙悟空    | 12123123 |
  | 孙悟空    | 123      |
  | 孙悟空    | 123432   |
  | 孙悟空    | 2131     |
  | 孙悟空    | 4213     |
  | 孙悟空    | 3421     |
  | 沙僧      | 12123123 |
  | 沙僧      | 123      |
  | 沙僧      | 123432   |
  | 沙僧      | 2131     |
  | 沙僧      | 4213     |
  | 沙僧      | 3421     |
  +-----------+----------+
  24 rows in set (0.00 sec)

• 使用union join实现行列转换

原数据

mysql> select user_name, arms, clothing, shoe from user1 a join user1_equipment b on a.id = b.user_id;
+-----------+----------+------------+------------+
| user_name | arms     | clothing   | shoe       |
+-----------+----------+------------+------------+
| 唐僧      | 九环锡杖 | 袈裟       | 僧鞋       |
| 猪八戒    | 九齿钉耙 | 僧衣       | 僧鞋       |
| 孙悟空    | 金箍棒   | 梭子黄金甲 | 藕丝云步履 |
| 沙僧      | 降妖宝杖 | 僧衣       | 僧鞋       |
+-----------+----------+------------+------------+
4 rows in set (0.00 sec)

结果集：

mysql> select user_name, 'arms' as equipment, arms from user1 a join user1_equipment b on a.id = b.user_id
    -> union all
    -> select user_name, 'clothing' as equipment, clothing from user1 a join user1_equipment b on a.id = b.user_id
    -> union all
    -> select user_name, 'shoe' as equipment, shoe from user1 a join user1_equipment b on a.id = b.user_id
    -> order by user_name;
+-----------+-----------+------------+
| user_name | equipment | arms       |
+-----------+-----------+------------+
| 唐僧      | clothing  | 袈裟       |
| 唐僧      | arms      | 九环锡杖   |
| 唐僧      | shoe      | 僧鞋       |
| 孙悟空    | arms      | 金箍棒     |
| 孙悟空    | shoe      | 藕丝云步履 |
| 孙悟空    | clothing  | 梭子黄金甲 |
| 沙僧      | shoe      | 僧鞋       |
| 沙僧      | clothing  | 僧衣       |
| 沙僧      | arms      | 降妖宝杖   |
| 猪八戒    | shoe      | 僧鞋       |
| 猪八戒    | clothing  | 僧衣       |
| 猪八戒    | arms      | 九齿钉耙   |
+-----------+-----------+------------+
12 rows in set (0.00 sec)

• 使用序列号进行列转行

  mysql> select user_name,
      -> case when c.id=1 then 'arms'
      -> when c.id=2 then 'clothing'
      -> when c.id=3 then 'shoe'
      -> end as equipment,
      -> coalesce(case when c.id=1 then arms end,
      -> case when c.id=2 then clothing end,
      -> case when c.id=3 then shoe end) as eq_name
      -> from user1 a
      -> join user1_equipment b on a.id=b.user_id
      -> cross join tb_sequence c where c.id<=3 order by user_name;
  +-----------+-----------+------------+
  | user_name | equipment | eq_name    |
  +-----------+-----------+------------+
  | 唐僧      | shoe      | 僧鞋       |
  | 唐僧      | arms      | 九环锡杖   |
  | 唐僧      | clothing  | 袈裟       |
  | 孙悟空    | shoe      | 藕丝云步履 |
  | 孙悟空    | arms      | 金箍棒     |
  | 孙悟空    | clothing  | 梭子黄金甲 |
  | 沙僧      | clothing  | 僧衣       |
  | 沙僧      | shoe      | 僧鞋       |
  | 沙僧      | arms      | 降妖宝杖   |
  | 猪八戒    | clothing  | 僧衣       |
  | 猪八戒    | shoe      | 僧鞋       |
  | 猪八戒    | arms      | 九齿钉耙   |
  +-----------+-----------+------------+
  12 rows in set (0.00 sec)

如何生成唯一序列号

需要的场景：

• 数据库主键，聚集索引方式
• 业务序列号如发票号，车票号，订单号

生成序列号的方法：

• MySQL：auto_increment
• Oracle：sequence
• SQLServer：identity/sequence

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

• 优先选择系统提供的序列号生成方式，需要注意数据空洞的问题：

  mysql> create table t (id int auto_increment not null, primary key(id));
  Query OK, 0 rows affected (0.04 sec)
  mysql> insert into t value(),();
  Query OK, 2 rows affected (0.01 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  mysql> select * from t;
  +----+
  | id |
  +----+
  |  1 |
  |  2 |
  +----+
  2 rows in set (0.00 sec)
  mysql> begin;
  Query OK, 0 rows affected (0.00 sec)
  mysql> insert into t values();
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from t;
  +----+
  | id |
  +----+
  |  1 |
  |  2 |
  |  3 |
  +----+
  3 rows in set (0.00 sec)
  mysql> rollback;
  Query OK, 0 rows affected (0.01 sec)
  mysql> insert into t values();
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from t;
  +----+
  | id |
  +----+
  |  1 |
  |  2 |
  |  4 |
  +----+
  3 rows in set (0.00 sec)
  

  • 使用SQL方式生成序列号，特殊情况下使用。如生成订单序列号，要求序列号的格式为YYYYMMDDNNNNNNN

    CREATE PROCEDURE usp_seqnum()
        BEGIN
          #定义变量并获取相关值
          DECLARE v_cnt INT;
          DECLARE v_timestr INT;
          SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d');
          SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;
          #新建表
          DROP TABLE IF EXISTS im_orderseq;
          CREATE TABLE  im_orderseq(
            timestr NVARCHAR(8) NOT NULL ,
            ordersn INT(3)
          );
          START TRANSACTION;
          #更新表的最值
          UPDATE im_orderseq SET ordersn = ordersn + v_cnt WHERE timestr = v_timestr;
          IF ROW_COUNT() = 0 THEN
            #插入数据
            INSERT INTO im_orderseq(timestr,ordersn) VALUES(v_timestr,v_cnt);
          END IF;
          SELECT CONCAT(v_timestr,LPAD(ordersn,7,0))AS ordersn
          FROM im_orderseq WHERE timestr = v_timestr;
          COMMIT;
        END;
    
        mysql> call usp_seqnum();
        +-----------------+
        | ordersn         |
        +-----------------+
        | 201804270000009 |
        +-----------------+
        1 row in set (0.04 sec)
    
        Query OK, 0 rows affected (0.05 sec)

如何删除重复元素

产生原因：

* 人为原因，重复录入数据，重复提交数据
* 系统原因，由于系统升级或设计的原因使原来可以重复的数据变为不能重复了。

查询数据是否重复：group by + having

mysql> select * from user1_test;
+----+-----------+----------+------------------------------------+
| id | user_name | over     | mobile                             |
+----+-----------+----------+------------------------------------+
|  1 | 唐僧      | 唐僧成佛 | 12123123,123,123432,2131,4213,3421 |
|  2 | 猪八戒    | 净坛使者 | 12123123,123,123432,2131,4213,3421 |
|  3 | 孙悟空    | 齐天大圣 | 12123123,123,123432,2131,4213,3421 |
|  4 | 沙僧      | 金身罗汉 | 12123123,123,123432,2131,4213,3421 |
|  8 | 唐僧      | 唐僧成佛 | 12123123,123,123432,2131,4213,3421 |
|  9 | 猪八戒    | 净坛使者 | 12123123,123,123432,2131,4213,3421 |
| 10 | 孙悟空    | 齐天大圣 | 12123123,123,123432,2131,4213,3421 |
| 11 | 沙僧      | 金身罗汉 | 12123123,123,123432,2131,4213,3421 |
+----+-----------+----------+------------------------------------+
8 rows in set (0.00 sec)

mysql> select user_name, count(*) from user1_test group by user_name having count(*)>1;
+-----------+----------+
| user_name | count(*) |
+-----------+----------+
| 唐僧      |        2 |
| 孙悟空    |        2 |
| 沙僧      |        2 |
| 猪八戒    |        2 |
+-----------+----------+
4 rows in set (0.00 sec)

删除重复数据，相同数据保留ID最大的：

mysql> select * from user1_test;
+----+-----------+----------+------------------------------------+
| id | user_name | over     | mobile                             |
+----+-----------+----------+------------------------------------+
|  1 | 唐僧      | 唐僧成佛 | 12123123,123,123432,2131,4213,3421 |
|  2 | 猪八戒    | 净坛使者 | 12123123,123,123432,2131,4213,3421 |
|  3 | 孙悟空    | 齐天大圣 | 12123123,123,123432,2131,4213,3421 |
|  4 | 沙僧      | 金身罗汉 | 12123123,123,123432,2131,4213,3421 |
|  8 | 唐僧      | 唐僧成佛 | 12123123,123,123432,2131,4213,3421 |
|  9 | 猪八戒    | 净坛使者 | 12123123,123,123432,2131,4213,3421 |
| 10 | 孙悟空    | 齐天大圣 | 12123123,123,123432,2131,4213,3421 |
| 11 | 沙僧      | 金身罗汉 | 12123123,123,123432,2131,4213,3421 |
+----+-----------+----------+------------------------------------+
8 rows in set (0.00 sec)

mysql> delete a from user1_test a join (
    -> select user_name, count(*), max(id) as id
    -> from user1_test
    -> group by user_name
    -> having count(*)>1) b
    -> on a.user_name=b.user_name
    -> where a.id < b.id;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from user1_test;
+----+-----------+----------+------------------------------------+
| id | user_name | over     | mobile                             |
+----+-----------+----------+------------------------------------+
|  8 | 唐僧      | 唐僧成佛 | 12123123,123,123432,2131,4213,3421 |
|  9 | 猪八戒    | 净坛使者 | 12123123,123,123432,2131,4213,3421 |
| 10 | 孙悟空    | 齐天大圣 | 12123123,123,123432,2131,4213,3421 |
| 11 | 沙僧      | 金身罗汉 | 12123123,123,123432,2131,4213,3421 |
+----+-----------+----------+------------------------------------+
4 rows in set (0.00 sec)

如何在子查询中匹配两个值

• 子查询：当一个查询是另一个查询的条件时，成为子查询。

解决同属性多值过滤的问题

如何计算累进税问题