我在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