大家帮忙看看啊！！！

Question

select a.employee_id,a.employee_name,a.sex,a.street,a.city from employee a,(select * from employee where employee_id in (select employee_id from manages) and employee_id in (select employee_id from works where company_id=mycompanyid)) b where a.employee_id not in (select employee_id from manages) and a.employee_id in (select employee_id from works where company_id=mycompanyid) and (a.city=b.city and a.street=b.street) 上面的查询假设COMPANYID确定，已经很复杂了，让我想想如何把公司再加入。

Answer 1

下面的语句经过运行通过，但没有使用数据测试SELECT a.*, c.company_id AS company_id
FROM dbo.employee a INNER JOIN
      dbo.works c ON a.employee_id = c.employee_id INNER JOIN
          (SELECT d .*, w.company_id AS company_id
         FROM employee d INNER JOIN
               works w ON d .employee_id = w.employee_id
         WHERE d .employee_id IN
                   (SELECT employee_id
                  FROM manages)) b ON a.city = b.city AND a.street = b.street AND 
      c.company_id = b.company_id
WHERE (a.employee_id NOT IN
          (SELECT employee_id
         FROM manages))

Answer 2

select * from(select Employee.employee_id,
                     Employee.employee_name,
                     Employee.city,
                     Works.company_id,
                     Manages.manger_id
from Employee left outer join Works on
                          Employee.employee_id = Works.employee_id
                              left outer join Manages on 
                          Employee.employee_id = Manages.employee_id) as A,
             (select Employee.city,
                     Works.company_id,
                     Manages.manger_id
from Employee left outer join Works on
                          Employee.employee_id = Works.employee_id
                              left outer join Manages on 
                          Employee.employee_id = Manages.employee_id
            group by Employee.city,
                     Works.company_id,
                     Manages.manger_id
              having count(1) > 1) as B
where a.city = b.city and 
      a.company_id = b.company_id and 
      a.manger_id = b.manger_id  

Answer 3

上面select * from改成select a.* from