代码之家 › 专栏 › 技术社区 › SomeStranger314

Mysql视图,其中值根据日期拆分为多行

mysql-function view date mysql

SomeStranger314 · 技术社区 · 4 年前

MySql版本:5.6.47

假设我有一张这样的桌子:

[from:Datetime][to:Datetime][amount:Decimal(10,2)]
[2020/01/15   ][2020/02/15 ][300                 ]

我想从中创建一个这样的视图:

[period:char(7)][amount:Decimal(10,2)]
[2020/01       ][150                 ]
[2020/02       ][150                 ]

起始日期和结束日期在单个月份中分开。该金额乘以特定月份的天数,再乘以从到之间的总天数。从到可以跨越n个月。

这可能吗,还是我在浪费时间研究这个?

0 回复 | 直到 4 年前

Akina 4 年前

假设一个范围内的月数不超过100:

SELECT id,
       datefrom,
       datetill,
       amount,
       monthstart,
       monthfinish,
       amount * (DATEDIFF(LEAST(datetill, monthfinish), GREATEST(datefrom, monthstart)) + 1) / (DATEDIFF(datetill, datefrom) + 1) monthamount
FROM ( SELECT test.*,
              (test.datefrom - INTERVAL DAY(test.datefrom) - 1 DAY) + INTERVAL numbers.num MONTH monthstart,
              LAST_DAY((test.datefrom - INTERVAL DAY(test.datefrom) - 1 DAY) + INTERVAL numbers.num MONTH) monthfinish
       FROM test
       JOIN ( SELECT t1.num*10+t2.num num
              FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1
              JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
            ) numbers
       HAVING monthstart <= test.datetill
          AND monthfinish >= test.datefrom
     ) subquery
ORDER BY id, monthstart;

fiddle

PS。如果最后一位数字的总和不匹配,不要感到惊讶。

推荐文章

Badal Solanki · 如何在MySQL中对字符串进行算术运算?

1 年前

Saleh Mehdiyev · 我想重构我的Laravel代码以遵守SOLID原则

1 年前

hello_programmers · Mysql从其他表输出一列

1 年前

Community wiki · 这个MySQL语句出了什么问题?

1 年前

Community wiki · 优化从同一表中提取的多列的查询