我不知道从哪里开始建立此查询。
我有以下简单的行：
event, date, value, months left
foo 01/06/2018 700 7
bar 01/08/2018 50 5
我想要以下输出：
event, date, value, months left
foo 01/06/2018 100 7
foo 01/07/2018 100 7
foo 01/08/2018 100 7
foo 01/09/2018 100 7
foo 01/10/2018 100 7
foo 01/11/2018 100 7
foo 01/12/2018 100 7
bar 01/08/2018 10 5
bar 01/09/2018 10 5
bar 01/10/2018 10 5
bar 01/11/2018 10 5
bar 01/12/2018 10 5
我该如何实现？我真的不知道。我发现了一些类似的问题，但并没有真正理解。
谢谢。
最佳答案
通过创建具有月份数字1到12的表，我能够做到这一点。
> select * from events;
+-------+------------+--------+
| event | event_date | value |
+-------+------------+--------+
| foo | 2018-06-01 | 700.00 |
| bar | 2018-08-01 | 50.00 |
+-------+------------+--------+
> select * from months;
+--------------+
| month_number |
+--------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+--------------+
> select event, concat(2018, '-', LPAD(month_number, 2, '00'), '-01') as curr_date, value / (13 - month(event_date)) as value, (13 - month_number) as months_remain from events, months where month_number >= month(event_date) order by event, months_remain desc;
+-------+------------+------------+---------------+
| event | curr_date | value | months_remain |
+-------+------------+------------+---------------+
| bar | 2018-08-01 | 10.000000 | 5 |
| bar | 2018-09-01 | 10.000000 | 4 |
| bar | 2018-10-01 | 10.000000 | 3 |
| bar | 2018-11-01 | 10.000000 | 2 |
| bar | 2018-12-01 | 10.000000 | 1 |
| foo | 2018-06-01 | 100.000000 | 7 |
| foo | 2018-07-01 | 100.000000 | 6 |
| foo | 2018-08-01 | 100.000000 | 5 |
| foo | 2018-09-01 | 100.000000 | 4 |
| foo | 2018-10-01 | 100.000000 | 3 |
| foo | 2018-11-01 | 100.000000 | 2 |
| foo | 2018-12-01 | 100.000000 | 1 |
+-------+------------+------------+---------------+
关于mysql - 将每月费用除以一年中剩余的月份数，然后将其分成多行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52338149/