mysql - 如何使用现有字段(表的另一个字段)的一些偏移量(表的字段)在 View 中创建新字段？

Question

我有表 Contracts:

CREATE TABLE `Contracts` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `supplier_id` INT(11) NOT NULL,
    `shop_id` INT(11) NOT NULL,
    `conclusionDate` DATE NOT NULL,
    `period` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `FK_Contracts_Shop` FOREIGN KEY (`shop_id`) REFERENCES `shop` (`id`),
    CONSTRAINT `FK_Contracts_Suppliers` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
)

我想创建这样的东西:

SELECT 
    c.id, 
    s.name as supplierName,
    c.conclusionDate, 
    c.conclusionDate+year(c.period) as cancelDate, 
    c.period
FROM
    Contracts as c
INNER JOIN
    Suppliers as s
ON 
    s.id = c.supplier_id 

这是我目前的观点代码。结果，我想要其中取消日期是从结束日期(conclusionDate 字段 — 日期类型)开始以年(period 字段 — int 类型)偏移的日期的行。

昨天，我看到了这样的例子，但我现在找不到了:(请帮助我。

预期结果:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ id + supplierName + conclusionDate + cancelDate + period +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 1  + bob1         + 2014-05-05     + 2015-05-05 + 1      +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 2  + bob2         + 2014-01-01     + 2018-05-05 + 4      +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 3  + bob3         + 2012-08-03     + 2017-08-03 + 5      +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Answer 1

通过使用 interval 和您的 conclusionDate 添加您的周期列来试试这个

c.conclusionDate + interval c.period year as cancelDate

Demo