英文:
Combining select queries without same count of rows
问题
I try to combine two select query, but I get Error
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists
First select query:
SELECT
dbo.Employees.Name,
dbo.Employees.Salary
FROM
dbo.Employees
Second select query:
SELECT
dbo.Employees.Name AS Employee,
dbo.Employees.Salary AS Salary,
dbo.Salary.Bonus AS Bonus,
dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary
FROM
dbo.Employees
INNER JOIN
dbo.Salary ON dbo.Employees.Id = dbo.Salary.Employee
WHERE
dbo.Salary.Year = 2023
AND dbo.Salary.Month = 5
The problem is that I need to combine these two queries. If there are not enough employees in the second query, I need to display them anyway.
I need to display absolutely all employees, regardless of the date.
英文:
I try to combine two select query, but I get Error
> All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists
First select query:
SELECT
dbo.Employees.Name,
dbo.Employees.Salary
FROM
dbo.Employees
Second select query:
SELECT
dbo.Employees.Name AS Employee,
dbo.Employees.Salary AS Salary,
dbo.Salary.Bonus AS Bonus,
dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary
FROM
dbo.Employees
INNER JOIN
dbo.Salary ON dbo.Employees.Id = dbo.Salary.Employee
WHERE
dbo.Salary.Year = 2023
AND dbo.Salary.Month = 5
The problem is that I need to combine these two queries. If there are not enough employees in the second query, I need to display them anyway.
I need to display absolutely all employees, regardless of the date
I tried to use UNION and EXCEPT
But I get above error
答案1
得分: 2
SELECT
dbo.Employees.Name AS 员工,
dbo.Salary.forPurchase AS 采购,
dbo.Salary.forProduction AS 生产,
dbo.Salary.forSale AS 销售,
dbo.Employees.Salary AS 工资,
dbo.Salary.Bonus AS 奖金,
dbo.Employees.Salary + dbo.Salary.Bonus AS 奖金工资,
COALESCE(dbo.Salary.Issued, '工资未发放') AS 已发放
FROM
dbo.Employees
FULL OUTER JOIN
dbo.Salary
ON Salary.Employee = Employees.Id
AND Salary.Year = 2023
AND Salary.Month = 5
英文:
SELECT
dbo.Employees.Name AS Employee,
dbo.Salary.forPurchase AS Purchases,
dbo.Salary.forProduction AS Productions,
dbo.Salary.forSale AS Sales,
dbo.Employees.Salary AS Salary,
dbo.Salary.Bonus AS Bonus,
dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary,
COALESCE(dbo.Salary.Issued, 'salary was not paid') AS Issued
FROM
dbo.Employees
FULL OUTER JOIN
(
dbo.Salary
INNER JOIN
dbo.Months
ON Salary.Month = Months.Id
AND Salary.Year = 2023
AND Salary.Month = 5
)
ON Employees.Id = Salary.Employee
But, as you don't actually use the Months
table, it can be simplified to...
SELECT
dbo.Employees.Name AS Employee,
dbo.Salary.forPurchase AS Purchases,
dbo.Salary.forProduction AS Productions,
dbo.Salary.forSale AS Sales,
dbo.Employees.Salary AS Salary,
dbo.Salary.Bonus AS Bonus,
dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary,
COALESCE(dbo.Salary.Issued, 'salary was not paid') AS Issued
FROM
dbo.Employees
FULL OUTER JOIN
dbo.Salary
ON Salary.Employee = Employees.Id
AND Salary.Year = 2023
AND Salary.Month = 5
答案2
得分: 1
I realized that you want the whole employee to calculate her salary if it is in the salary table
SELECT
dbo.Employees.Name AS 员工,
dbo.Employees.Salary AS 工资,
dbo.Salary.Bonus AS 奖金,
dbo.Employees.Salary + dbo.Salary.Bonus AS 奖金工资
FROM
dbo.Employees
LEFT JOIN
dbo.Salary ON dbo.Employees.Id = dbo.Salary.Employee and dbo.Salary.Year = 2023
AND dbo.Salary.Month = 5
英文:
I realized that you want the whole employee to calculate her salary if it is in the salary table
SELECT
dbo.Employees.Name AS Employee,
dbo.Employees.Salary AS Salary,
dbo.Salary.Bonus AS Bonus,
dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary
FROM
dbo.Employees
left JOIN
dbo.Salary ON dbo.Employees.Id = dbo.Salary.Employee and dbo.Salary.Year = 2023
AND dbo.Salary.Month = 5
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论