新建测试表并生成测试数据
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`;
id | names |
---|---|
1 | u1,u2,u3 |
2 | u4,u5 |
或者
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`
id | names |
---|---|
1 | u3,u1,u2 |
2 | u4,u5 |
参考链接:
https://stackoverflow.com/questions/21199310/mysql-in-clause-string-to-array
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() 函数