公司数据库中的客户和分支查询

Question

我的数据库中有以下四个表：

雇员

PK: employeeID;     
FK: empBranch references BRANCH;
FK: empSupervisor references EMPLOYEE

​

​

支部

PK: branchNumber
FK: branchManager references EMPLOYEE

​

​

客户

PK: customerID

​

​

命令

PK: orderNumber
FK: customerID references CUSTOMER; 
FK: salesPerson references EMPLOYEE

​

​

现在我要列举这两件事：

1. 只向销售员所在城市所在城市的客户销售产品的销售人员的Employee_ID和姓名。
2. 向销售人员所在城市所在城市的每一位客户销售产品的销售人员的Employee_ID和姓名。

请帮助我回答这两个问题。

Answer 1

只向销售员所在城市所在城市的客户销售产品的销售人员的Employee_ID和姓名。

SELECT DISTINCT
  e.employeeid,
  e.emplname || e.empfname AS salesperson_name
FROM
  orders o
  INNER JOIN customer c ON o.customerid = c.customerid
  INNER JOIN branch b ON o.salesperson = b.branchmanager
  INNER JOIN employee e ON o.salesperson = e.employeeid
WHERE 
  c.custcity = b.branchcity -- salesperson's branch located in the same city as customer

Answer 2

由于您没有指定特定的RDBMS，所以这是为SQL服务器编写的。

1. 只向销售员所在城市所在城市的客户销售产品的销售人员的Employee_ID和姓名。 从订单中选择e.EmployeeID，e.EMPFNAME +‘’+ e.EMPLNAME名称，从o.CustomerID = c.CustomerId的订单中选择o.SalesPerson +‘+ e.Emplname名称，o.CustomerID=c.CustomerId，o.SalesPerson ON o.SalesPerson= e.Employeeid JOIN o.SalesPerson b ON e.EMPBRANCH = b.BranchManager GROUP BY e.Employeeid，e.Empfname +’‘+e.EMPLNAME有计数(当c.custCity <> b.BranchCity 1结束时)=0-没有在员工所在城市以外的销售
2. 向销售人员所在城市所在城市的每一位客户销售产品的销售人员的Employee_ID和姓名。

每个城市的计数有很多种方法，但我使用了CTE。

    WITH CusPerCity AS     -- get client counts per city
    (
    SELECT
        CustCity,
        COUNT(Distinct c.CustomerId) CustomersPerCity
    FROM Orders o
    JOIN Customer c 
        ON o.customerid = c.customerid
    GROUP BY CustCity
    )   


    SELECT 
        e.EmployeeID,
        e.Empfname + ' ' + e.Emplname Name

    FROM Orders o
    JOIN Customer c 
        ON o.CustomerID = c.CustomerId
    JOIN Employee e 
        ON o.SalesPerson = e.Employeeid
    JOIN Branch b
        ON e.EMPBRANCH = b.BranchManager
    JOIN CusPerCity cus 
        ON b.BranchCity = cus.CustCity
    WHERE c.CustCity = b.BranchCity
    GROUP BY e.Employeeid, e.EMPFNAME + ' ' + e.EMPLNAME, CustomersPerCity
    HAVING COUNT(DISTINCT c.customerID) = cus.CustomersPerCity --number of unique customers sold to equals then number of customers in a city