在select语句中使用视图

1tu0hz3e 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(445)

当试图使用视图来查找有关数据库中表的信息时，我似乎无法在select语句中使用视图。

CREATE VIEW Num_projects AS SELECT ENumber, COUNT(*) AS Projects FROM WorksOn Group BY ENumber;

SELECT * FROM Num_projects;

CREATE VIEW Same_num_projects AS SELECT Employee.ENumber, Name FROM Employee JOIN Num_Projects WHERE Projects IN( SELECT Projects FROM Num_projects WHERE ENumber = 00101);

SELECT * FROM Same_num_projects;

这是处理上述代码的输出：

--------------
CREATE VIEW Same_num_projects AS SELECT Employee.ENumber, Name FROM Employee JOIN Num_Projects WHERE Projects IN( SELECT Projects FROM Num_projects WHERE ENumber = 00101)
--------------

ERROR 1146 (42S02): Table 'csit115.Num_Projects' doesn't exist
--------------
SELECT * FROM Same_num_projects
--------------

ERROR 1146 (42S02): Table 'csit115.Same_num_projects' doesn't exist
--------------

为什么我的观点没有被认可？

mysql

来源：https://stackoverflow.com/questions/52791255/using-a-view-in-a-select-statement

2条答案

按热度按时间

mzmfm0qo1#

这是通过改变 JOIN Num_Projects 至 JOIN Num_projects

赞(0）回复(0）举报 2021-06-18

gfttwv5a2#

它不区分大小写。创建第二个视图时未定义连接条件，缺少此条件 ON Employee.ENumber = Num_Projects.ENumber . 如果没有on条件，就不能形成内部联接。

CREATE VIEW Num_projects
AS
SELECT
    ENumber
  , COUNT(*) AS Projects
FROM WorksOn
GROUP BY
    ENumber;

SELECT
    *
FROM Num_projects;

CREATE VIEW Same_num_projects
AS
SELECT
    Employee.ENumber
  , Name
FROM Employee
JOIN Num_Projects ON Employee.ENumber = Num_Projects.ENumber
WHERE Projects IN (
    SELECT
        Projects
    FROM Num_projects
    WHERE ENumber = 00101
);

SELECT
    *
FROM Same_num_projects;

赞(0）回复(0）举报 2021-06-18

我来回答

在select语句中使用视图

2条答案

相关问题

热门标签

最新问答