如何使用内连接过滤两个表中的值。
有两个表 Employee 和 SalaryIncrement:
样本数据:
CREATE TABLE Employee
(
EmployeeId INT IDENTITY(1,1) NOT NULL,
FullName VARCHAR(255) NOT NULL,
MonthlySalary MONEY NOT NULL
PRIMARY KEY(EmployeeId)
)
INSERT INTO Employee (FullName, MonthlySalary) VALUES ('John', 12000.00)
INSERT INTO Employee (FullName, MonthlySalary) VALUES ('Lee', 20000.00)
CREATE TABLE SalaryIncrement
(
SalaryIncrementId INT IDENTITY(1,1) NOT NULL,
SalaryIncreaseFrom DATE NOT NULL,
AmountOfSalaryIncrease MONEY NOT NULL,
EmployeeId INT NOT NULL
PRIMARY KEY(SalaryIncrementId)
)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2012-02-05', 5000.00, 2)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2012-02-05', 1000.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2013-05-05', 2000.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2013-04-05', 1000.00, 2)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2014-08-08', 1500.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2016-03-01', 500.00, 1)
从上面的两个表中,我需要检索如下图所示的数据。
我还需要显示额外的两列Old Monthly Salary
并按New Monthly Salary
增加的工资金额计算。
结果:
首先要做的是在 SalaryIncrement 上进行自我 JOIN。这将用于计算
SUM
SalaryIncrease。询问:
然后这个查询只需要 to
JOIN
并且Employee
它可以在 SUM 和当前 MonthlySalary 之间进行数学运算(示例链接)。询问:
输出: