Skip to content
鼓励作者:欢迎打赏犒劳

实际工作中常用SQL

查看版本

sql
select version()

数据准备

shell
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255)  DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `department` varchar(255)  DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (1, 'Joe', 70000.00, '研发部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (2, 'Henry', 80000.00, '研发部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (3, 'Sam', 60000.00, '研发部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (4, 'Max', 90000.00, '研发部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (5, 'Janet', 69000.00, '人事部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (6, 'Randy', 85000.00, '人事部');
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department`) VALUES (7, 'Eva', 85000.00, '人事部');

分组取前三

开窗函数

以下是一个使用ROW_NUMBER()的例子,它会为每个分组内的行分配一个唯一的序号,即使值相同也会被分配不同的序号。

sql
SELECT *
FROM (
  SELECT department,name, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
  FROM employee
) AS tt
WHERE row_num <= 3;

githubPage

如果你想保留重复值的相对顺序,并且只是为了排序,不需要分配唯一序号,可以使用DENSE_RANK():

sql
SELECT department,name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employee

githubPage

普通写法

-- 部门分组取薪水前三的,但是这样写不严谨,对于并列的会出现多条

sql
SELECT
  e.`department` AS '部门',
  e.`name` AS '员工',
  e.salary AS '工资'
FROM
  employee e
WHERE
  (
    SELECT
      count(em.salary)
    FROM
      employee em
    WHERE
      em.salary > e.salary
      AND em.department = e.department
  ) < 3
ORDER BY
  e.department,
  e.salary DESC

分组之后得到数组集合

JSON_ARRAYAGG函数

警告

JSON_ARRAYAGG() 这个函数可以将一组结果合成为一个JSON数组。 它是在 MySQL 5.7.22 及更高版本中引入的。

sql
SELECT department, JSON_ARRAYAGG(name) AS order_ids
FROM employee
GROUP BY department;

githubPage

GROUP_CONCAT函数

警告

如果你使用的是MySQL的一个旧版本,不支持JSON_ARRAYAGG,你可以使用GROUP_CONCAT函数, 它可以将分组后的多个值连接为一个字符串,但输出格式为字符串,不是真正的数组。

sql
SELECT department, GROUP_CONCAT(name) AS order_ids
FROM employee
GROUP BY department;

githubPage

JSON_ARRAY创建JSON格式的数组

警告

这个函数用于创建一个JSON格式的数组。它是在 MySQL 5.7.8 版本首次引入的,与JSON相关的其他函数一起随着对JSON数据类型的支持一起推出

sql
SELECT JSON_ARRAY('apple', 'banana', 'cherry') AS fruitArray;

githubPage

如何实现两个数组内容的合并?

高于5.7版本

MySQL提供了JSON_MERGE()(在MySQL 5.7.8-5.7.21版本中)或在MySQL 5.7.22及更新的版本中提供的JSON_MERGE_PRESERVE()和JSON_MERGE_PATCH()函数来实现JSON数组(或对象)的合并。

警告

JSON_MERGE_PRESERVE()函数将保留两个JSON数组中的所有元素。如果合并的JSON包含对象,JSON_MERGE_PRESERVE()在遇到同名的key时会将对应的value合并为一个新的数组。

sql
SET @json1 = JSON_ARRAY('apple', 'banana');
SET @json2 = JSON_ARRAY('cherry', 'date');

SELECT JSON_MERGE_PRESERVE(@json1, @json2) as merged_json;

githubPage

低于5.7版本

如果您的MySQL版本是5.7.22之前的,您可以使用JSON_MERGE()

sql
SET @json1 = JSON_ARRAY('apple', 'banana');
SET @json2 = JSON_ARRAY('cherry', 'date');

SELECT JSON_MERGE(@json1, @json2) as merged_json;

如有转载或 CV 的请标注本站原文地址