我有兩張桌子 -
員工 ：
EmployeeNo | EmployeeName | EmployeeDesignation | CompanyID
12345 | ABC | Doctor | 3434
4545 | XYZ | Engineer | 3434
部 ：
DepartmentNo | EmployeeNo | Location
0808 | 12345 | Loc1
0989 | 12345 | Loc2
我想列印員工詳細資訊以及部門表中的位置計數。輸出應該是這樣的 -
EmployeeNo| EmployeeName| EmployeeDesignation| Count(Location)
12345 | ABC | Doctor | 2
4545 | XYZ | Engineer |
當我使用內部聯接時，它顯示的是 employeeId12345而不是4545. 4545我知道是這樣，因為部門表的相應 EmployeeNo 列中沒有資料。我知道我們可以使用交叉連接來組合沒有公共列的表，但它在計數列中給了我一個垃圾號。
我的內部連接查詢 -
select Employee.EmployeeNo, Employee.EmployeeName, Employee.EmployeeDesignation, count(Department.Location) as count
from Employee inner join Department on
Employee.EmployeeNo = Department.EmployeeNo
where Employee.CompanyID like '3434'
group by Employee.EmployeeNo, Employee.EmployeeName, Employee.EmployeeDesignation
我的交叉連接查詢
select Employee.EmployeeNo, Employee.EmployeeName, Employee.EmployeeDesignation, count(Department.Location) as count
from Employee, Department
where Employee.CompanyID like '3434'
group by Employee.EmployeeNo, Employee.EmployeeName, Employee.EmployeeDesignation
uj5u.com熱心網友回復：
使用LEFT OUTER JOIN：
SELECT e.EmployeeNo,
e.EmployeeName,
e.EmployeeDesignation,
COUNT(d.Location) as count
FROM Employee e
LEFT OUTER JOIN Department d
ON (e.EmployeeNo = d.EmployeeNo)
WHERE e.CompanyID like '3434'
GROUP BY
e.EmployeeNo,
e.EmployeeName,
e.EmployeeDesignation
其中，對于樣本資料：
CREATE TABLE Employee (
EmployeeNo PRIMARY KEY,
EmployeeName,
EmployeeDesignation,
CompanyID
) AS
SELECT 12345, 'ABC', 'Doctor', 3434 FROM DUAL UNION ALL
SELECT 4545, 'XYZ', 'Engineer', 3434 FROM DUAL;
CREATE TABLE Department (
DepartmentNo PRIMARY KEY,
EmployeeNo,
Location
) AS
SELECT '0808', 12345, 'Loc1' FROM DUAL UNION ALL
SELECT '0989', 12345, 'Loc2' FROM DUAL;
ALTER TABLE Department ADD CONSTRAINT department__employeeno__fk
FOREIGN KEY (EmployeeNo) REFERENCES Employee(EmployeeNo);
輸出：
員工號 員工姓名 員工指定 數數 4545 XYZ 工程師 0 12345 美國廣播公司 醫生 2
此外，如果EmployeeNo是主鍵，那么您不需要在GROUP BY子句中包含非主鍵列，并且可以聚合以獲取這些列：
SELECT e.EmployeeNo,
MAX(e.EmployeeName) AS EmployeeName,
MAX(e.EmployeeDesignation) AS EmployeeDesignation,
COUNT(d.Location) as count
FROM Employee e
LEFT OUTER JOIN Department d
ON (e.EmployeeNo = d.EmployeeNo)
WHERE e.CompanyID like '3434'
GROUP BY
e.EmployeeNo
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/432315.html