如何从逗号存储的mysql中选择当前工作日

lbsnaicq 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(322)

我有一个表格，以下面的格式存储计划生产。我需要得到本周的工作日期。

+------------+-------------+---------+----------+---------+------+--------------+---------------------------------------------+
| planned_id | per_quarter | per_day | per_week | quarter | year | working_days | working_dates                         |                                                                                                                             |
+------------+-------------+---------+----------+---------+------+--------------+---------------------------------------------+
|          1 | 860         | 14.10   | 70.50    | 2       | 2018 |           61 | 02-04-2018,03-04-2018,04-04-2018,05-04-2018 |
|                                                                                 06-04-2018,09-04-2018,12-04-2018,15-04-2018
+------------+-------------+---------+----------+---------+------+--------------+---------------------------------------------+

我想知道本周的工作日期。我使用的查询如下。我不知道我哪里出错了。

SELECT Value from ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(working_dates, ',', n.n), ',', -1) Value
          FROM planned_production t CROSS JOIN 
        (
           SELECT a.N + b.N * 10 + 1 n
             FROM 
            (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
           ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
            ORDER BY n
        ) n
         WHERE quarter = quarter(curdate()) and n.n <= 1 + (LENGTH(t.working_dates) - LENGTH(REPLACE(t.working_dates, ',', '')))
) as TEMP_TABLE 
        WHERE Value between CURRENT_DATE() - INTERVAL 
        WEEKDAY(CURRENT_DATE()) + 7 DAY
        AND
        (CURRENT_DATE() - INTERVAL WEEKDAY(CURRENT_DATE()) DAY) - INTERVAL 1 
        SECOND ORDER BY Value asc;

我需要这样的输出

+------------+
| value      |
+------------+
| 17-05-2018 |
| 18-05-2018 |
+------------+

欢迎您提出任何建议，并提前表示感谢。

mysql Date mysql-workbench

来源：https://stackoverflow.com/questions/50384140/how-to-select-current-week-working-dates-from-mysql-stored-in-comma-saperated-va

1条答案

按热度按时间

rkue9o1l1#

表结构：

create table test (planned_id int, per_quarter float, per_day float, per_week float, 
                  quarter int, year int, working_days int, working_dates varchar(1000));

表值：

insert into test values ( 1,860,14.10,70.50,2,2018,61,'02-04-2018,03-04-2018,04-04-2018,05-04-2018,06-04-2018,09-04-2018,12-04-2018,15-04-2018,17-05-2018,18-05-2018');             
insert into test values ( 2,800,12.10,60.50,2,2018,50,'02-04-2018,03-04-2018,04-04-2018,05-04-2018,06-04-2018,09-04-2018,12-04-2018,15-04-2018,14-05-2018,15-05-2018') ;

sql查询：

set @startdate := date_add(SUBDATE(current_date, WEEKDAY(current_date)), interval -1 day);
select test.planned_id, checkdate 
from 
( 
select @startdate := date_add(@startdate, interval 1 day) as checkdate
from ( SELECT 0 singles
UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9) 
a cross join (
SELECT 0 singles
UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9) b
cross join (
SELECT 0 singles
UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9) c 
) as dim_date
join test
where 
checkdate between SUBDATE(current_date, WEEKDAY(current_date))
and date_add(SUBDATE(current_date, WEEKDAY(current_date)) , interval 7 day)
and find_in_set(DATE_FORMAT(checkdate, '%d-%m-%Y'),working_dates) != 0
order by test.planned_id

输出：

planned_id  checkdate
1   2018-05-17
1   2018-05-18
2   2018-05-14
2   2018-05-15

逻辑：
1）首先创建表dim\u date。您可以使用上面查询中所示的查询来创建它，但我建议您在数据库中创建一个表。
2）使用筛选器仅选择一周

checkdate between SUBDATE(current_date, WEEKDAY(current_date))
and date_add(SUBDATE(current_date, WEEKDAY(current_date)) , interval 7 day)

3）在dim\ u表和test之间使用交叉连接
4）使用find\ in\ u set函数检查日期是否存在。

and find_in_set(DATE_FORMAT(checkdate, '%d-%m-%Y'),working_dates) != 0

赞(0）回复(0）举报 2021-06-21

我来回答

如何从逗号存储的mysql中选择当前工作日

1条答案

相关问题

热门标签

最新问答