如何找到不同的组合？

9udxz4iz 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(268)

共有3个表：

CREATE TABLE Employee(
  EID mediumint PRIMARY KEY AUTO_INCREMENT,
  EName varchar(100) DEFAULT NULL,
  EPincode mediumint unsigned NULL
);

eid是每个员工的唯一员工id；

CREATE TABLE Project(
  PID mediumint PRIMARY KEY AUTO_INCREMENT,
  PName varchar(30) DEFAULT NULL,
  MID mediumint UNIQUE NOT NULL,
  FOREIGN KEY (MID) REFERENCES Employee(EID)
);

mid是负责项目的员工的经理id。

CREATE TABLE Works_On(
  EID mediumint NOT NULL,
  PID mediumint NOT NULL,
  FOREIGN KEY (EID) REFERENCES Employee(EID),
  FOREIGN KEY (PID) REFERENCES Project(PID)
);

此表包含所有员工及其相关项目。
一个员工可以从事多个项目。一个项目可以由多个员工完成。每个项目都有一个经理。经理可以有一个或多个下属。
我想找一个由雇员姓名、项目名称和经理姓名组成的表。请帮忙。
这是我迄今为止编写的查询，但它不起作用：

SELECT DISTINCT A.EName AS "Employee Name"
               ,A.PName AS "Project Name"
               ,B.EName AS "Manager Name" 
FROM (SELECT P.PName
            ,E.EName 
      from Works_On W 
      NATURAL JOIN Project P 
      NATURAL JOIN Employee E) A,
(SELECT E.EName 
 from Project P 
 INNER JOIN Employee E ON P.MID = E.EID) B

mysql Database

来源：https://stackoverflow.com/questions/51241982/how-to-find-distinct-combinations

3条答案

按热度按时间

7z5jn7bk1#

select A.Employee_name,
       A.project_name,
       B.manager_name  from
      (
        SELECT
        P.PName AS project_name,
        E.EName AS Employee_name,
        E.EID,P.PID
        FROM Works_On W
        INNER JOIN Project P ON W.PID = P.PID
        INNER JOIN Employee E ON W.EID = E.EID

        ) as A           

       left join
       (

        SELECT
        E.EName AS manager_name,
        E.EID,P.PID
        FROM Project P
        INNER JOIN Employee E ON P.MID = E.EID

        ) B on A.PID=B.PID

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

mepcadol2#

SELECT P.PName AS ProjectName,
       M.EName AS ManagerName, 
       E.EName AS EmployeeName 
FROM Project P 
     LEFT JOIN Employee M ON (P.MID=M.EID) 
     LEFT JOIN Works_On WO on (P.PID=WO.PID) 
     LEFT JOIN Employee E on (E.EID=WO.EID);

成功了。

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

pu82cl6c3#

你可以试试下面的查询-

SELECT
    E.EName,
    P.PName,
    M.EName
FROM Employee E 
    INNER JOIN Employee M ON M.EID = E.EID
    INNER JOIN Project P ON E.EID = P.MID
    INNER JOIN Works_On W ON W.EID = E.EID AND W.PID = P.PID

这也许对你有帮助。

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

我来回答

如何找到不同的组合？

3条答案

相关问题

热门标签

最新问答