mysql - 使用来自多个表的数据创建复杂的 mysql 视图

Question

我对 mysql 数据库非常陌生，我必须使用下表创建 2 个复杂视图

[ 2

以下是我迄今为止尝试过的，但仍然无法获得所需的视图。我创建了一个新表来保存季度信息。另外，我创建了触发更新

在时间表表中发生新插入时的 year_quarter_period

CREATE TABLE `year_quarter_period` (
  `quarter` SMALLINT PRIMARY KEY AUTO_INCREMENT,
  `from_date` timestamp,
  `to_date` timestamp,
  `timesheet_id` int
);

ALTER TABLE `timesheet` ADD FOREIGN KEY (`email_id`) REFERENCES `resource` (`email`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `timesheet` ADD FOREIGN KEY (`project_name`) REFERENCES `project` (`name`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `revenue` ADD FOREIGN KEY (`project_name`) REFERENCES `project` (`name`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `resource_cost` ADD FOREIGN KEY (`email_id`) REFERENCES `resource` (`email`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `year_quarter_period` ADD FOREIGN KEY (`timesheet_id`) REFERENCES `timesheet` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

这是我迄今为止尝试创建的第一个视图

SELECT project.name as Project, 
       year_quarter_period.quarter as Quarter,
       resource.role as Role ,
       timesheet.hours as Efforts
  FROM project as project,
       year_quarter_period as year_quarter_period,
       resource as resource, 
       timesheet as timesheet
 WHERE resource.role = (SELECT role from resource) 
  AND year_quarter_period.quarter = (SELECT quarter from year_quarter_period)

但我得到以下结果，这显然是不正确的

自过去 1 天以来，我一直坚持这一点。有人可以帮助我吗

标签： mysqlviewrdbms