mysql查询笔记

技术 置顶 精帖
0 384
peng49
peng49 2023-05-16 09:33:32
 

GROUP_CONCAT 效果测试

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

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

测试数据如下

  1. table_list table_user
  2. +--------+ +--------+
  3. |id|list | |id|name |
  4. +--+-----+ +--+-----+
  5. | 1|1,2,3| | 1|u1 |
  6. +--+-----+ +--+-----+
  7. | 2|4,5 | | 2|u2 |
  8. +--+-----+ +--+-----+
  9. | 3|u3 |
  10. +--+-----+
  11. | 4|u4 |
  12. +--+-----+
  13. | 5|u5 |
  14. +--+-----+

效果如下

  1. SELECT
  2. id,
  3. (SELECT GROUP_CONCAT(name) FROM table_user AS u WHERE FIND_IN_SET(u.id, l.list)) AS `names`
  4. FROM `table_list` AS `l`;
id names
1 u1,u2,u3
2 u4,u5

或者

  1. SELECT l.id,
  2. GROUP_CONCAT(u.name) AS `names`
  3. FROM `table_list` AS `l`
  4. LEFT JOIN `table_user` AS `u` ON FIND_IN_SET(`u`.`id`, `l`.`list`)
  5. GROUP BY `l`.`id`
id names
1 u3,u1,u2
2 u4,u5

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

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

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

sql执行结果

  1. +-------------------------+
  2. | weekRange |
  3. +-------------------------+
  4. | 2023-05-15 ~ 2023-05-21 |
  5. +-------------------------+
  6. 1 row in set (0.00 sec)

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

回帖
登录
忘记密码?