我希望能够选择哪个费率更高,并在基于UNION ALL的结果中输入它,以便获得单个查询结果。例如,员工200每小时的基本费率为25美元,但他所从事的工作的基本费率为10.00美元。那么他应该每小时获得25.00美元。员工100的基本费率为10.00美元,但工作的基本费率为25.00美元。因此,他也应该每小时获得25.00美元。我想选择每个员工的最高费率。类似于这个想法。SELECT EmployeeID, RATE_A或RATE_B from .... 这是我整理的一些数据
CREATE Table WageRate(
[ID] [int] IDENTITY(1,1) NOT NULL,
[RateCode] int NULL,
[Rate] Decimal (10,2) NULL
)
INSERT INTO WageRate( RateCode,Rate)
Values (1,10.00), (2,15.00), (3,20.00), (4,25.00)
Create Table Employee(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] int NULL,
[RateCode] int NULL
)
Insert Into Employee (EmployeeID,RateCode)
Values (100,1), (200,4)
Create Table TimeCards(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] int NULL,
[Hours] Decimal (10,2) NULL,
[JobRateCode] int NULL
)
Insert Into TimeCards (EmployeeID,[Hours],JobRateCode)
Values (100,8.00,4), (200,8.00,1)
SELECT t1.Employeeid ,(t0.Rate) as [Rate_A] ,Null FROM WageRate t0
INNER JOIN Employee t1 ON t1.RateCode= t0.RateCode
INNER JOIN TimeCards t2 on t1.EmployeeID = t2.EmployeeID
UNION ALL
SELECT t4.Employeeid ,Null,(t3.Rate) As [Rate_B] FROM WageRate t3
INNER JOIN TimeCards t4 on t4.JobRateCode = t3.RateCode
INNER JOIN Employee t5 ON t4.EmployeeID= t5.EmployeeID