我在Format下面有一个表Stormended_production,我需要获取当前一周的working_dates。


+------------+-------------+---------+----------+---------+------+--------------+---------------------------------------------+
| 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 |
+------------+



  欢迎任何建议,并在此先感谢。

最佳答案

表结构:

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_date。您可以使用上面的查询中所示的查询来创建它,但是我建议您在数据库中创建一个表。

2)使用过滤器仅选择一个星期

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


3)在dim_table和test之间使用交叉连接

4)使用find_in_set函数检查日期是否存在。

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

10-05 20:53
查看更多