MySQL—sql语句多个字段模糊查询并指定优先级排序

拼搏现实的明天。 2024-03-25 10:41 97阅读 0赞

## 关注 wx：`CodingTechWork` ##

## 需求 ##

在进行一些产品开发时，需要在一个填写框中填写名称或者编码进行精准或模糊查询。要求：

1.  一个填写框供底层数据表结构多个字段进行模糊查询使用。
2.  查询结果排序：名称优先排序，然后按照编码排序，若查不到按照时间排序。

## 实践 ##

### 方法 ###

使用`order by`配合`case when...then...语句`。

### 建表 ###

CREATE TABLE `t1` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `code` varchar(255) DEFAULT NULL,
      `create_time` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

### 插入 ###

INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (1, '中国苏州', 'chinasz', '2023-03-13 11:20:51');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (2, '中国苏州常熟', 'chinaszcs', '2023-03-13 11:22:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (3, '中国江苏', 'chinajs', '2023-03-11 11:20:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (4, '江苏', 'js', '2023-03-12 11:20:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (5, '中国江苏盐城', 'chinajsyc', '2023-03-13 11:10:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (6, '江苏盐城', 'jsyc', '2023-03-13 11:11:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (7, '中国江苏无锡', 'chinajswx', '2023-03-10 11:20:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (8, '江苏常州', 'jscz', '2023-02-13 11:20:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (9, '中国', 'china', '2023-01-13 11:20:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (10, '江苏苏州', 'jssz', '2023-03-11 11:20:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (11, '江浙沪', 'jzh', '2023-03-13 11:20:40');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (12, '江苏001', 'js001', '2023-03-11 11:20:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (13, '中国江苏省', 'chinajss', '2023-03-13 11:20:52');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (14, '江苏省', 'jss', '2023-03-13 12:19:03');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (15, '新加坡', 'singapore', '2023-03-13 12:19:17');
    INSERT INTO `testdb`.`t1` (`id`, `name`, `code`, `create_time`) VALUES (16, '日本', 'japan', '2023-03-13 12:19:37');

### 排序查询 ###

#### 全局查询 ####

##### sql语句 #####

select * from t1 group by id ORDER BY create_time DESC;

##### 查询结果 #####

mysql> select * from t1 group by id ORDER BY create_time DESC;
    +----+--------------------+-----------+---------------------+
    | id | name               | code      | create_time         |
    +----+--------------------+-----------+---------------------+
    | 16 | 日本             | japan     | 2023-03-13 12:19:37 |
    | 15 | 新加坡          | singapore | 2023-03-13 12:19:17 |
    | 14 | 江苏省          | jss       | 2023-03-13 12:19:03 |
    |  2 | 中国苏州常熟 | chinaszcs | 2023-03-13 11:22:52 |
    | 13 | 中国江苏省    | chinajss  | 2023-03-13 11:20:52 |
    |  1 | 中国苏州       | chinasz   | 2023-03-13 11:20:51 |
    | 11 | 江浙沪          | jzh       | 2023-03-13 11:20:40 |
    |  6 | 江苏盐城       | jsyc      | 2023-03-13 11:11:52 |
    |  5 | 中国江苏盐城 | chinajsyc | 2023-03-13 11:10:52 |
    |  4 | 江苏             | js        | 2023-03-12 11:20:52 |
    |  3 | 中国江苏       | chinajs   | 2023-03-11 11:20:52 |
    | 10 | 江苏苏州       | jssz      | 2023-03-11 11:20:52 |
    | 12 | 江苏001          | js001     | 2023-03-11 11:20:52 |
    |  7 | 中国江苏无锡 | chinajswx | 2023-03-10 11:20:52 |
    |  8 | 江苏常州       | jscz      | 2023-02-13 11:20:52 |
    |  9 | 中国             | china     | 2023-01-13 11:20:52 |
    +----+--------------------+-----------+---------------------+
    16 rows in set (0.00 sec)

#### 不输入参数查询 ####

##### sql语句 #####

set @a = '';
    select t.id, t.`name`,t.`code`,t.create_time
    from t1 t where t.`name` like concat('%',@a,'%') 
    or t.`code` like concat('%',@a,'%') group by t.id 
    order by(
    case 
    when t.`name` = @a then 1 
    when t.`code` = @a then 2
    when t.`name` like concat(@a,'%') then 3
    when t.`name` like concat('%',@a,'%') then 4
    when t.`name` like concat('%',@a) then 5
    when t.`code` like concat(@a,'%') then 6
    when t.`code` like concat('%',@a,'%') then 7
    when t.`code` like concat(@a,'%') then 8
    else 9
    end) 
    asc, t.create_time desc;

#####  #####

+----+--------------------+-----------+---------------------+
    | id | name               | code      | create_time         |
    +----+--------------------+-----------+---------------------+
    | 16 | 日本             | japan     | 2023-03-13 12:19:37 |
    | 15 | 新加坡          | singapore | 2023-03-13 12:19:17 |
    | 14 | 江苏省          | jss       | 2023-03-13 12:19:03 |
    |  2 | 中国苏州常熟 | chinaszcs | 2023-03-13 11:22:52 |
    | 13 | 中国江苏省    | chinajss  | 2023-03-13 11:20:52 |
    |  1 | 中国苏州       | chinasz   | 2023-03-13 11:20:51 |
    | 11 | 江浙沪          | jzh       | 2023-03-13 11:20:40 |
    |  6 | 江苏盐城       | jsyc      | 2023-03-13 11:11:52 |
    |  5 | 中国江苏盐城 | chinajsyc | 2023-03-13 11:10:52 |
    |  4 | 江苏             | js        | 2023-03-12 11:20:52 |
    |  3 | 中国江苏       | chinajs   | 2023-03-11 11:20:52 |
    | 10 | 江苏苏州       | jssz      | 2023-03-11 11:20:52 |
    | 12 | 江苏001          | js001     | 2023-03-11 11:20:52 |
    |  7 | 中国江苏无锡 | chinajswx | 2023-03-10 11:20:52 |
    |  8 | 江苏常州       | jscz      | 2023-02-13 11:20:52 |
    |  9 | 中国             | china     | 2023-01-13 11:20:52 |
    +----+--------------------+-----------+---------------------+
    16 rows in set (0.00 sec)

#### 输入查询参数 ####

##### sql语句 #####

set @a = 'js';
    select t.id, t.`name`,t.`code`,t.create_time
    from t1 t where t.`name` like concat('%',@a,'%') 
    or t.`code` like concat('%',@a,'%') group by t.id 
    order by(
    case 
    when t.`name` = @a then 1 
    when t.`code` = @a then 2
    when t.`name` like concat(@a,'%') then 3
    when t.`name` like concat('%',@a,'%') then 4
    when t.`name` like concat('%',@a) then 5
    when t.`code` like concat(@a,'%') then 6
    when t.`code` like concat('%',@a,'%') then 7
    when t.`code` like concat(@a,'%') then 8
    else 9
    end) 
    asc, t.create_time desc;

##### 结果 #####

+----+--------------------+-----------+---------------------+
    | id | name               | code      | create_time         |
    +----+--------------------+-----------+---------------------+
    |  4 | 江苏             | js        | 2023-03-12 11:20:52 |
    | 14 | 江苏省          | jss       | 2023-03-13 12:19:03 |
    |  6 | 江苏盐城       | jsyc      | 2023-03-13 11:11:52 |
    | 10 | 江苏苏州       | jssz      | 2023-03-11 11:20:52 |
    | 12 | 江苏001          | js001     | 2023-03-11 11:20:52 |
    |  8 | 江苏常州       | jscz      | 2023-02-13 11:20:52 |
    | 13 | 中国江苏省    | chinajss  | 2023-03-13 11:20:52 |
    |  5 | 中国江苏盐城 | chinajsyc | 2023-03-13 11:10:52 |
    |  3 | 中国江苏       | chinajs   | 2023-03-11 11:20:52 |
    |  7 | 中国江苏无锡 | chinajswx | 2023-03-10 11:20:52 |
    +----+--------------------+-----------+---------------------+
    10 rows in set (0.00 sec)