英文:
how can I join february '23 with february '22 by day of the week
问题
以下是翻译的内容，已去掉代码部分：
我正在思考一个优雅的解决方案，将2023年2月与2022年2月相匹配。
使用PHP，我发现2023年2月的第一天是星期三，2022年2月的第一个星期三是2022年2月2日，我想按星期几将它们匹配，结果如下所示：
2023 年表格：
日期 星期几 人数
2023-02-01 星期三 4
2023-02-02 星期四 17
2023-02-03 星期五 4
2023-02-04 星期六 0
2023-02-05 星期日 22
2023-02-06 星期一 33
2023-02-07 星期二 12
2023-02-08 星期三 3
...
2023-02-28 星期二 45
2022 年表格：
日期 星期几 人数
2022-02-01 星期二 14
2022-02-02 星期三 19
2022-02-03 星期四 12
2022-02-04 星期五 18
2022-02-05 星期六 14
2022-02-06 星期日 19
2022-02-07 星期一 0
2022-02-08 星期二 7
2022-02-09 星期三 9
...
2022-02-28 星期一 8
期望的结果：
日期 星期几 2023 2022
2023-02-01 星期三 4 19
2023-02-02 星期四 17 12
2023-02-03 星期五 4 18
2023-02-04 星期六 0 14
2023-02-05 星期日 22 19
2023-02-06 星期一 33 0
2023-02-07 星期二 12 7
2023-02-08 星期三 3 9
...
2023-02-28 星期二 45
这是我想出来的解决方法，现在它运行得很好。但我一直在思考是否有更好的方法来实现这一点。
英文:
Im braking my head of an elegant solution to join feb '23 with feb '22.
With php I'm gettin that the first day of feb '23 as wednesday and the first wednesday of feb '22 was the 2022-02-02, I would like to join them by day of the week to something like this.
2023 table:
date dayoftheweek people
2023-02-01 Wednesday 4
2023-02-02 Thursday 17
2023-02-03 Friday 4
2023-02-04 Saturday 0
2023-02-05 Sunday 22
2023-02-06 Monday 33
2023-02-07 Tuesday 12
2023-02-08 Wednesday 3
…
2023-02-28 Tuesday 45
2022 table:
date dayoftheweek people
2022-02-01 Tuesday 14
2022-02-02 Wednesday 19
2022-02-03 Thursday 12
2022-02-04 Friday 18
2022-02-05 Saturday 14
2022-02-06 Sunday 19
2022-02-07 Monday 0
2022-02-08 Tuesday 7
2022-02-09 Wednesday 9
…
2022-02-28 Monday 8
desired result:
date dayofthweek 2023 2022
2023-02-01 Wednesday 4 19
2023-02-02 Thursday 17 12
2023-02-03 Friday 4 18
2023-02-04 Saturday 0 14
2023-02-05 Sunday 22 19
2023-02-06 Monday 33 0
2023-02-07 Tuesday 12 7
2023-02-08 Wednesday 3 9
…
2023-02-28 Tuesday 45
this is what I came up with and its working fine now:
SET @mon_last_year:= 0;
SET @tue_last_year:= 0;
SET @wed_last_year:= 0;
SET @thu_last_year:= 0;
SET @fri_last_year:= 0;
SET @sat_last_year:= 0;
SET @sun_last_year:= 0;
SET @mon_this_year:= 0;
SET @tue_this_year:= 0;
SET @wed_this_year:= 0;
SET @thu_this_year:= 0;
SET @fri_this_year:= 0;
SET @sat_this_year:= 0;
SET @sun_this_year:= 0;
SELECT
fecha2023,
day_name_count_this_year,
fecha2022,
day_name_count_last_year,
total_this_year,
total_last_year
FROM
(
SELECT
fecha AS 'fecha2023',
CONCAT(day_name,day_count) AS day_name_count_this_year,
total AS total_this_year
FROM
(
SELECT
fecha,
DATE_FORMAT(fecha,'%W') AS day_name,
CASE
WHEN DAYOFWEEK(fecha) = 1 THEN @sun_this_year:= @sun_this_year+1
WHEN DAYOFWEEK(fecha) = 2 THEN @mon_this_year:= @mon_this_year+1
WHEN DAYOFWEEK(fecha) = 3 THEN @tue_this_year:= @tue_this_year+1
WHEN DAYOFWEEK(fecha) = 4 THEN @wed_this_year:= @wed_this_year+1
WHEN DAYOFWEEK(fecha) = 5 THEN @thu_this_year:= @thu_this_year+1
WHEN DAYOFWEEK(fecha) = 6 THEN @fri_this_year:= @fri_this_year+1
WHEN DAYOFWEEK(fecha) = 7 THEN @sat_this_year:= @sat_this_year+1
END AS day_count,
total
FROM
(
SELECT
fecha,
SUM(total_gasto) AS total
FROM
gastos
WHERE
fecha >= '2023-02-01'
AND
fecha <= '2023-02-28'
GROUP BY fecha
) ty_
) ty_
) ty_
LEFT JOIN
(
SELECT
fecha AS 'fecha2022',
CONCAT(day_name,day_count) AS day_name_count_last_year,
total AS total_last_year
FROM
(
SELECT
fecha,
DATE_FORMAT(fecha,'%W') AS day_name,
CASE
WHEN DAYOFWEEK(fecha) = 1 THEN @sun_last_year:= @sun_last_year+1
WHEN DAYOFWEEK(fecha) = 2 THEN @mon_last_year:= @mon_last_year+1
WHEN DAYOFWEEK(fecha) = 3 THEN @tue_last_year:= @tue_last_year+1
WHEN DAYOFWEEK(fecha) = 4 THEN @wed_last_year:= @wed_last_year+1
WHEN DAYOFWEEK(fecha) = 5 THEN @thu_last_year:= @thu_last_year+1
WHEN DAYOFWEEK(fecha) = 6 THEN @fri_last_year:= @fri_last_year+1
WHEN DAYOFWEEK(fecha) = 7 THEN @sat_last_year:= @sat_last_year+1
END AS day_count,
total
FROM
(
SELECT
fecha,
SUM(total_gasto) AS total
FROM
gastos
WHERE
fecha >= '2022-02-01'
AND
fecha <= '2022-02-28'
GROUP BY fecha
) ly_
) ly_
) ly_
ON(day_name_count_this_year = day_name_count_last_year)
its returning the desired result really fast but I keep wondering if theres a better way to achieve this.
答案1
得分: 1
"First Friday" 中某个月的日期将满足 DAYOFMONTH(date) % 7 = 0。
因此
选择...
从 fecha2022 中选择
加入 fecha2023
在 DAYOFWEEK(fecha2023.data)
= DAYOFWEEK(fecha2022.data)
和 DAYOFMONTH(fecha2023.data) MOD 7
= DAYOFMONTH(fecha2022.data) MOD 7
其中...
不需要任何 CASE 表达式。
英文:
The "First Friday" of some month will have a DAYOFMONTH(date) % 7 = 0.
So
SELECT ...
FROM fecha2022
JOIN fecha2023
ON DAYOFWEEK(fecha2023.data)
= DAYOFWEEK(fecha2022.data)
AND DAYOFMONTH(fecha2023.data) MOD 7
= DAYOFMONTH(fecha2022.data) MOD 7
WHERE ...
No need for any CASE expression.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论