MySQL #

# 切换数据库
mysql> use mysql;
Database changed

# 查看用户
# 这里有两个名为 root 的用户，但是其 host 不同
# user 表以 user + host 为联合主键
mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
5 rows in set (0.01 sec)

# 查看当前用户
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.01 sec)


# 查看指定用户的所有权限
-- ⚠️ host 部分必须使用 引号 包裹
mysql> show grants for root@'%';

# 创建用户
mysql> create user 'linnzh' identified with mysql_native_password by 'linnzh_pwd';
Query OK, 0 rows affected (0.05 sec)

# 授予权限
# ref: https://dev.mysql.com/doc/refman/8.0/en/grant.html
mysql> create database `study`;
Query OK, 1 row affected (0.04 sec)

mysql> grant select,insert,update,delete on study.* to 'linnzh'@'%';
Query OK, 0 rows affected (0.02 sec)
 
mysql> show grants for linnzh@'%';
+-------------------------------------------------------------------+
| Grants for linnzh@%                                               |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `linnzh`@`%`                                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `study`.* TO `linnzh`@`%` |
+-------------------------------------------------------------------+
2 rows in set (0.01 sec)

# 修改密码
# ref: https://dev.mysql.com/doc/refman/8.0/en/alter-user.html
mysql> alter user 'linnzh'@'%' identified with mysql_native_password BY 'new_password';

# 查看慢查询是否开启，以及慢查询日志位置
mysql> show variables like '%query_log%';
+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| binlog_rows_query_log_events | OFF                                  |
| slow_query_log               | ON                                   |
| slow_query_log_file          | /var/lib/mysql/4c8044aa7817-slow.log |
+------------------------------+--------------------------------------+
3 rows in set (0.03 sec)

# 查看慢查询执行超时时间（全局变量）
# 超过该时长的 SQL 语句都会被记录在慢查询日志里
mysql> show variables like '%query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.03 sec)

# 假定我们有两张表，一张表为 product 表，存放产品信息，其中有产品价格列price；
# 另外一张表是 product_price 表，要将 product_price 表中的价格字段 price 更新为 product 表中价格字段 price 的 80%

# 方案1
mysql> UPDATE `product` p, `product_price` pp
SET pp.`price` = p.`price` * 0.8 
WHERE p.`productId` = pp.`productId`;

# 方案2:使用INNER JOIN然后更新
mysql> UPDATE `product` p
INNER JOIN `product_price` pp ON p.`productId` = pp.`productId`
SET pp.`price` = p.`price` * 0.8;

# 方案3:使用LEFT JOIN来做多表UPDATE，如果product_price表中没有产品价格记录的话，将product表的isDeleted字段设置为1
mysql> UPDATE `product` p
LEFT JOIN `product_price` pp ON p.`productId` = pp.`productId`
SET p.`deleted` = 1 
WHERE pp.`productId` IS NULL;

# 方案4:使用 INNER JOIN 同时更新两张表
mysql> UPDATE `product` p
INNER JOIN `product_price` pp ON p.`productId` = pp.`productId`
SET pp.`price` = p.`price` * 0.8, p.`dateUpdate` = CURDATE();

# 用 CASE 表达式的条件分支进行的更新操作是一气呵成的，因此可以避免出现如覆盖更新、主键重复、使用临时值等所导致的错误

-- 用 CASE 表达式写正确的更新操作
UPDATE `Salaries`
SET `salary` = (CASE WHEN `salary` >= 300000 THEN `salary` ＊ 0.9 WHEN `salary` >= 250000 AND `salary` < 280000 THEN `salary` ＊ 1.2 ELSE `salary` END);

-- 用CASE表达式调换值
UPDATE `SomeTable` 
SET `p_key` = (CASE WHEN `p_key`='a' THEN 'b' WHEN `p_key`='b' THEN 'a' ELSE `p_key` END) WHERE `p_key` IN ('a','b');

# 查询当前的事务隔离级别
mysql> show variables like '%tx%';

# 设置事务的隔离级别 - InnoDB 默认为 repeatable read（可重复读）
mysql> set global transaction isolation level repeatable read;

# ref: https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
# 示例来源：https://segmentfault.com/a/1190000018335749
# 更多示例：https://www.cainiaojc.com/note/qa3196.html

mysql> create database stored_procedure;
Query OK, 1 row affected (0.03 sec)

mysql> use stored_procedure;
Database changed

mysql> CREATE TABLE `dim_date` (
  `id` int(8) NOT NULL DEFAULT '0',
  `key` date NULL DEFAULT NULL,
  `year` int(4) NOT NULL,
  `quarter` int(1) NOT NULL,
  `month` int(2) NOT NULL,
  `week` int(1) NOT NULL COMMENT '星期',
  `weekofyear` int(2) NOT NULL COMMENT '一年中的第几周',
  `day` int(2) NOT NULL COMMENT '日',
  `dayofyear` int(3) NOT NULL COMMENT '一年总的第几天',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)

# 定义存储过程 —— getAllDate
# 生成了以当前日期为基准前后3650天的日期记录
mysql> DELIMITER //
DROP PROCEDURE IF EXISTS getAllDate;

CREATE PROCEDURE getAllDate()
BEGIN
    DECLARE count int DEFAULT 0;
    DECLARE startDay DATE DEFAULT DATE(NOW());
    DECLARE endDay DATE DEFAULT DATE(NOW());
    -- 定义异常处理方式  http://www.cnblogs.com/cookiehu/p/4994278.html
    DECLARE out_status VARCHAR(200) DEFAULT 'OK';
    DECLARE CONTINUE HANDLER  
    FOR 1062
    SET out_status = 'Duplicate Entry';
    -- 异常处理方式完毕
    WHILE count < 3650 DO
            INSERT INTO `dim_date`(`id`, `key`, `year`, `quarter`, `month`, `week`, `weekofyear`, `day`, `dayofyear`) VALUES (cast(DATE_FORMAT(startDay,'%Y%m%d') as UNSIGNED), startDay, YEAR(startDay), QUARTER(startDay), MONTH(startDay), WEEKDAY(startDay) + 1, week(startDay, 1), DAY(startDay), DAYOFYEAR(startDay));
            SET count = count + 1;
            SET startDay = DATE_ADD(DATE(NOW()), INTERVAL COUNT DAY);
            SET endDay = DATE_SUB(DATE(NOW()), INTERVAL COUNT DAY);
            INSERT INTO `dim_date`(`id`, `key`, `year`, `quarter`, `month`, `week`, `weekofyear`, `day`, `dayofyear`) VALUES (cast(DATE_FORMAT(endDay,'%Y%m%d') AS UNSIGNED), endDay, YEAR(endDay), QUARTER(endDay), MONTH(endDay), WEEKDAY(endDay) + 1, week(endDay, 1), DAY(endDay), DAYOFYEAR(endDay));
    END WHILE;
END //

DELIMITER ;

Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.04 sec)

# 调用存储过程
mysql> call getAllDate();
Query OK, 1 row affected (14.94 sec)

mysql> select count(*) from dim_date where year = 2022;
+----------+
| count(*) |
+----------+
|      365 |
+----------+
1 row in set (0.01 sec)

mysql> CREATE DATABASE IF NOT EXISTS study CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.04 sec)

mysql> USE study;
Database changed

mysql> CREATE TABLE IF NOT EXISTS `comment`(
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `parent_id` int(11) NOT NULL DEFAULT 0 COMMENT '上级ID',
    `path` varchar(64) NOT NULL DEFAULT '' COMMENT '该节点的所有祖先信息，使用反斜线分割',
    `comment` varchar(255) NOT NULL DEFAULT '' COMMENT '评论内容'
) COMMENT '评论表' ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 0 rows affected (0.07 sec)

# 插入一些数据
mysql> INSERT INTO `comment` (`id`, `parent_id`, `path`, `comment`) VALUES (1, 0, '1/', '这本书不错');
INSERT INTO `comment` (`id`, `parent_id`, `path`, `comment`) VALUES (2, 1, '1/2', '此书作者和译者的视野颇为广阔，在思路上更加大胆');
INSERT INTO `comment` (`id`, `parent_id`, `path`, `comment`) VALUES (3, 1, '1/3', '我在犹豫要不要买');
INSERT INTO `comment` (`id`, `parent_id`, `path`, `comment`) VALUES (4, 2, '1/2/4', '说的有道理');
INSERT INTO `comment` (`id`, `parent_id`, `path`, `comment`) VALUES (5, 3, '1/3/5', '值得购买，内容比较契合目前的数据发展潮流');
INSERT INTO `comment` (`id`, `parent_id`, `path`, `comment`) VALUES (6, 5, '1/3/5/6', '封面和纸张设计略显粗糙');
INSERT INTO `comment` (`id`, `parent_id`, `path`, `comment`) VALUES (7, 0, '7/', '在当下大数据时代，这本书一定要看');
INSERT INTO `comment` (`id`, `parent_id`, `path`, `comment`) VALUES (8, 3, '3/8', '该书气味之大，装帧之差实属罕见');
INSERT INTO `comment` (`id`, `parent_id`, `path`, `comment`) VALUES (9, 7, '7/9', '这本书必须要看');
INSERT INTO `comment` (`id`, `parent_id`, `path`, `comment`) VALUES (10, 7, '7/10', '实践性较强');

# 获取指定 comment 的所有上级ID
# https://www.cnblogs.com/chenjiacheng/p/6522211.html
mysql> DELIMITER //
-- 删除原有函数
DROP FUNCTION IF EXISTS getAllParentComments;
-- 定义函数
CREATE DEFINER=`root`@`%` FUNCTION `getAllParentComments`(currentId INT) RETURNS varchar(64) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
    READS SQL DATA
BEGIN
  -- 声明局部变量：局部变量无法返回
  -- DECLARE path VARCHAR(64) DEFAULT '';
  -- 声明全局变量
  SET @path = '';
  
  -- 修改变量值
  -- 获取当前 comment 的 path
  SET @path = (SELECT `path` FROM `comment` WHERE `id` = currentId);
  -- 去掉最后一个ID（即当前ID）
  SET @path = LEFT(@path, LENGTH(@path) - LENGTH(currentId) - 1);
  
  RETURN @path;
END //
-- 定义结束
DELIMITER ;

Query OK, 0 rows affected (0.03 sec)
 
Query OK, 0 rows affected (0.03 sec)

# 调用函数——与调用自带函数一致
mysql> SELECT getAllParentComments(4) as parent;
+--------+
| parent |
+--------+
| 1/2    |
+--------+
1 row in set (0.01 sec)


# 如果要查找某个节点的所有后代，例如查找comment_id等于3的所有后代，可以使用如下的查询语句：
mysql> SELECT * FROM `comment` WHERE path LIKE '1/3/_%';
+----+-----------+---------+--------------------------------------------------------------+
| id | parent_id | path    | comment                                                      |
+----+-----------+---------+--------------------------------------------------------------+
|  5 |         3 | 1/3/5   | 值得购买，内容比较契合目前的数据发展潮流 |
|  6 |         5 | 1/3/5/6 | 封面和纸张设计略显粗糙                            |
+----+-----------+---------+--------------------------------------------------------------+
2 rows in set (0.01 sec)



# 如果要查找下一层子节点，可以使用如下的查询语句
# 请根据数据特点编写正则表达式
mysql> SELECT * FROM `comment` WHERE path REGEXP '^1/3/[0-9]+[/]?$';
+----+-----------+-------+--------------------------------------------------------------+
| id | parent_id | path  | comment                                                      |
+----+-----------+-------+--------------------------------------------------------------+
|  5 |         3 | 1/3/5 | 值得购买，内容比较契合目前的数据发展潮流 |
+----+-----------+-------+--------------------------------------------------------------+
1 row in set (0.01 sec)

# 插入操作也比较简单，只需要复制一份父节点的路径，并将新节点的ID值（comment_id）添加到路径末尾就可以了。

# 枚举路径的方式使得查询子树和祖先都变得更加简单，查看分隔符即可知道节点的层次，
# 虽然冗余存储了一些数据，应用程序需要额外增加代码以确保路径信息的正确性，
# 但这种设计的扩展性更好，更能适应未来数据的不断增长

# 设计闭包表
mysql> CREATE TABLE `node_relation` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `ancestor` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '祖先节点',
  `descendant` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '后代节点',
  `distance` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '相隔层级，>=1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_anc_desc` (`ancestor`,`descendant`),
  KEY `idx_desc` (`descendant`)
) COMMENT '节点关系表 - 闭包表' ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Query OK, 0 rows affected (0.09 sec)

# 由于闭包表新增了节点和节点之间的关系，所以在变更树结构的时候，会重构这个关系，想想就觉得复杂。
# 所以数据量少，请谨用闭包表。


# 插入数据
# 该数据为一个 1->2->3 的二叉树结构
mysql> INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (1, 2, 1);
INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (1, 3, 1);
INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (1, 4, 2);
INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (1, 5, 2);
INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (1, 6, 2);
INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (2, 4, 1);
INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (2, 5, 1);
INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (3, 6, 1);


# 常用操作
# 查询 ancestor 为 1 的所有子集
mysql> SELECT `descendant` FROM `node_relation` WHERE `ancestor` = 1;
+------------+
| descendant |
+------------+
|          2 |
|          3 |
|          4 |
|          5 |
|          6 |
+------------+
5 rows in set (0.00 sec)

# 查询 ancestor 为 5 的所有祖先节点
mysql> SELECT `ancestor` FROM `node_relation` WHERE `descendant` = 5;
+----------+
| ancestor |
+----------+
|        1 |
|        2 |
+----------+
2 rows in set (0.01 sec)

# 增
# 1. 新增直接节点记录
mysql> INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (6, 7, 1);
Query OK, 1 row affected (0.02 sec)
# 2. 记录闭包关系（新增节点与其他节点——直接节点的相关节点的关系）
mysql> SELECT `ancestor`,`distance` FROM `node_relation` WHERE `descendant` = 6;
+----------+----------+
| ancestor | distance |
+----------+----------+
|        1 |        2 |
|        3 |        1 |
+----------+----------+
2 rows in set (0.01 sec)
# 3. 新增间接节点记录（根据 2 的结果，建议使用程序而不是 SQL
# 节点距离应 + 1
mysql> INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (1, 7, 3);
INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (3, 7, 2);

Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)

# 删：删除节点及节点相关的间接节点
# 即删除节点之间的关系即可
mysql> DELETE FROM `node_relation` WHERE `ancestor` = 3 OR `descendant` = 3;

# 移动：即变更父节点
# 这里假设：变更节点 C 为 2，其旧父节点 O 为 1，其子孙节点包括：4、5，新父节点 P 为 3
# 查询 旧父节点O 与变更节点 C 及其子孙节点 的所有关系
mysql> SELECT `ancestor`, `descendant`, `distance` FROM `node_relation` WHERE `ancestor` = 2 OR `descendant` = 2;
+----------+------------+----------+
| ancestor | descendant | distance |
+----------+------------+----------+
|        1 |          2 |        1 |
|        2 |          4 |        1 |
|        2 |          5 |        1 |
+----------+------------+----------+
3 rows in set (0.01 sec)

# 重建新关系
# 新关系为：变更节点 C 子树（包括 C） x 新父节点 P 组树（包括 P）的笛卡尔积
mysql> SELECT `ancestor`, `descendant`, `distance` FROM `node_relation` WHERE `ancestor` = 2 OR `descendant` = 3;
+----------+------------+----------+
| ancestor | descendant | distance |
+----------+------------+----------+
|        1 |          3 |        1 |
|        2 |          4 |        1 |
|        2 |          5 |        1 |
+----------+------------+----------+
3 rows in set (0.01 sec)
# 新关系
mysql> SELECT `ancestor`,`descendant`,`distance`,'P' AS `level` FROM `node_relation` WHERE `descendant` = 3
UNION ALL
SELECT `ancestor`,`descendant`,`distance`,'C' AS `level` FROM `node_relation` WHERE `ancestor` = 2;
+----------+------------+----------+-------+
| ancestor | descendant | distance | level |
+----------+------------+----------+-------+
|        1 |          3 |        1 | P     |
|        2 |          4 |        1 | C     |
|        2 |          5 |        1 | C     |
+----------+------------+----------+-------+
3 rows in set (0.01 sec)

# 或者使用 CASE……[WHEN……THEN……]ELSE……END 结构
mysql> SELECT `ancestor`,`descendant`,`distance`,
CASE `descendant` WHEN 3 THEN 'P' ELSE 'C' END AS `level` 
FROM `node_relation` WHERE `ancestor` = 2 OR `descendant` = 3;

+----------+------------+----------+-------+
| ancestor | descendant | distance | level |
+----------+------------+----------+-------+
|        1 |          3 |        1 | P     |
|        2 |          4 |        1 | C     |
|        2 |          5 |        1 | C     |
+----------+------------+----------+-------+
3 rows in set (0.01 sec)
# -----------------------难点：计算 P 和 C 之间的笛卡尔积-----------------------

# 由于这里只有两层结构（父子），可以使用嵌套循环
DELIMITER //
DROP PROCEDURE IF EXISTS caculateCartesianProduct;

CREATE PROCEDURE caculateCartesianProduct(INOUT cId int(11), INOUT pId int(11))
BEGIN
    DECLARE cursorParent CURSOR FOR SELECT `ancestor` FROM `node_relation` WHERE `descendant` = pId;
    DECLARE cursorChild CURSOR FOR SELECT `descendant` FROM `node_relation` WHERE `ancestor` = cId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    
    # 打开游标
    OPEN cursorParent;
    # 检索光标
      FETCH cursorParent INTO parent;
      
      
    # 关闭游标
    CLOSE cursorParent;
END //

DELIMITER ;

# -----------------------预期结果-----------------------

+----------+------------+----------+
| ancestor | descendant | distance |
+----------+------------+----------+
|        1 |          2 |        2 |
|        1 |          4 |        3 |
|        1 |          5 |        3 |
|        3 |          2 |        1 |
|        3 |          4 |        2 |
|        3 |          5 |        2 |
+----------+------------+----------+

# -----------------------难点：计算 P 和 C 之间的笛卡尔积-----------------------

# 删除 旧父节点O 与变更节点 C 的关系
mysql> DELETE FROM `node_relation` WHERE `descendant` = 2 AND `ancestor` = 1;
Query OK, 1 rows affected (0.03 sec)

# 新关系走「增」步骤
mysql> INSERT INTO `node_relation` (`ancestor`, `descendant`, `distance`) VALUES (3, 2, 1);

# 有点问题：当子节点的 pid 大于父节点时，该子节点的子节点将查不出来
SELECT  id, parent_id
FROM    (SELECT * FROM `company`
            ORDER BY `parent_id`, `id`) AS targetTable,
        (SELECT @pv := ?) AS initialisation
WHERE   FIND_IN_SET(targetTable.`pid`, @pv) > 0 AND @pv := CONCAT(@pv, ',', targetTable.`id`);


# MySQL 8 实现
WITH RECURSIVE `target` AS(
    SELECT parent.id, parent.pid,parent.title FROM auth_group `parent` WHERE parent.id = ?
    UNION ALL
    SELECT child.id, child.pid,child.title FROM auth_group `child` INNER JOIN `target` ON target.id = child.pid
) SELECT id,title,pid FROM target;

SELECT @r AS _id,
    (SELECT @r := parent_id FROM company WHERE id = _id) AS pid,
    @l := @l + 1 AS level
FROM (SELECT @r := ?, @l := 0) AS vars, company
WHERE @r <> 0 AND parent_id > 0 ORDER BY level;

# 参考 leetcode 第 196 题
# 使用「自连接」，从 P1 表中删除符合 where 条件的记录

# 查询
select *
from Person p1, Person p2
where p1.email = p2.email and p1.id > p2.id;

# 删除
delete p1 
from Person p1, Person p2
where p1.email = p2.email and p1.id > p2.id;

delete 
from Person
where Id in
    (
        select Id
        from
            (
                select Id,
                    row_number() over(partition by Email order by Id) rn
                from Person
            ) t1
        where rn>1
    );

# case ... when ... then ... end 形式
update Salary set sex = 
    case sex
        when 'Female' then 'Male'
        when 'Male' then 'Female'
    end;
    
# if() 函数，类似 PHP 中的 ?: 三元操作符
update Salary set sex = if(sex='Female', 'Male', 'Female');

SELECT id, 
    SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue,
    SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue,
    SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue,
    SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue,
    SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue,
    SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue,
    SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue,
    SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue,
    SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue,
    SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue,
    SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue,
    SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
FROM department
GROUP BY id
ORDER BY id;


# 这里使用 case ... when ... then ... end 结构来获取符合条件的结果
# 换成 if() 函数也可
# 最后对结果使用聚合函数 sum() 来统计
SELECT id, 
    SUM(IF(month='Jan', revenue, NULL)) AS Jan_Revenue,
    SUM(IF(month='Feb', revenue, NULL)) AS Feb_Revenue,
    SUM(IF(month='Mar', revenue, NULL)) AS Mar_Revenue,
    SUM(IF(month='Apr', revenue, NULL)) AS Apr_Revenue,
    SUM(IF(month='May', revenue, NULL)) AS May_Revenue,
    SUM(IF(month='Jun', revenue, NULL)) AS Jun_Revenue,
    SUM(IF(month='Jul', revenue, NULL)) AS Jul_Revenue,
    SUM(IF(month='Aug', revenue, NULL)) AS Aug_Revenue,
    SUM(IF(month='Sep', revenue, NULL)) AS Sep_Revenue,
    SUM(IF(month='Oct', revenue, NULL)) AS Oct_Revenue,
    SUM(IF(month='Nov', revenue, NULL)) AS Nov_Revenue,
    SUM(IF(month='Dec', revenue, NULL)) AS Dec_Revenue
FROM department
GROUP BY id
ORDER BY id;

SELECT product_id, 'store1' store, store1 price FROM Products WHERE store1 IS NOT NULL
UNION ALL
SELECT product_id, 'store2' store, store2 price FROM Products WHERE store2 IS NOT NULL
UNION ALL
SELECT product_id, 'store3' store, store3 price FROM Products WHERE store3 IS NOT NULL;

# 不考虑去重时，尽量使用 UNION ALL（UNION 将产生去重开销）

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

SELECT a.sell_date, 
    count(distinct a.product) as num_sold, 
    group_concat(distinct product order by product SEPARATOR ',') as products
FROM Activities a
GROUP BY a.sell_date
ORDER BY a.sell_date;

# 隐含条件：'conditions' （疾病）包含 0 个或以上的疾病代码，以空格分隔
# conditions 需要做处理

SELECT patient_id,patient_name,conditions
FROM Patients
WHERE FIND_IN_SET('DIAB1', REPLACE(conditions, ' ', ',')) <> 0;