Combining select queries without same count of rows

huangapple go评论59阅读模式
英文:

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

Combining select queries without same count of rows

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

Combining select queries without same count of rows

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

dbfiddle

英文:

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


dbfiddle

huangapple
  • 本文由 发表于 2023年5月13日 22:46:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76243306.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定