mysql查询笔记

GROUP_CONCAT 效果测试

新建测试表并生成测试数据

CREATE TABLE `table_list`
(
    `id`   INT         NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `list` VARCHAR(64) NOT NULL DEFAULT ''
);
INSERT INTO `table_list` (`list`) VALUES ('1,2,3'),('4,5');
CREATE TABLE `table_user`
(
    `id`   INT         NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(64) NOT NULL DEFAULT ''
);
INSERT INTO `table_user` (`name`) VALUES ('u1'),('u2'),('u3'),('u4'),('u5');

测试数据如下

table_list    table_user
+--------+    +--------+
|id|list |    |id|name |
+--+-----+    +--+-----+
| 1|1,2,3|    | 1|u1   |
+--+-----+    +--+-----+
| 2|4,5  |    | 2|u2   |
+--+-----+    +--+-----+
              | 3|u3   |
              +--+-----+
              | 4|u4   |
              +--+-----+
              | 5|u5   |
              +--+-----+

效果如下

SELECT
       id,
       (SELECT GROUP_CONCAT(name) FROM table_user AS u WHERE FIND_IN_SET(u.id, l.list)) AS `names`
FROM `table_list` AS `l`;

或者

SELECT l.id,
       GROUP_CONCAT(u.name) AS `names`
FROM `table_list` AS `l`
LEFT JOIN `table_user` AS `u` ON FIND_IN_SET(`u`.`id`, `l`.`list`)
GROUP BY `l`.`id`

参考链接:
https://stackoverflow.com/questions/21199310/mysql-in-clause-string-to-array

mysql通过日期获取一周的时间范围

SELECT CONCAT(
                    STR_TO_DATE(CONCAT(YEARWEEK('2023-05-16', 1), ' Monday'), '%X%V %W'),
                    ' ~ ',
                    DATE_ADD(STR_TO_DATE(CONCAT(YEARWEEK('2023-05-16', 1), ' Monday'), '%X%V %W'), INTERVAL 6 DAY)
                ) AS `weekRange`;

sql执行结果

+-------------------------+
| weekRange               |
+-------------------------+
| 2023-05-15 ~ 2023-05-21 |
+-------------------------+
1 row in set (0.00 sec)

Getting first day of the week in MySql using Week No
MySQL YEARWEEK() 函数