sql - 根据两个不同的列选择哪个值更大

Question

我想选择哪个速率更大，并将其输入到基于 UNION ALL 结果的单个查询结果中。例如，员工 200 的基本工资为 25 美元，但他从事的工作的基本工资为 10.00。那么他每小时的工资应该是 25 点。员工 100 的基本费率为 10.00，但该职位的基本费率为 25.00。所以他每小时也应该得到 25 美元。我想为每位员工选择最高的费率。与这个想法类似的东西。从...中选择 EmployeeID、RATE_A 或 RATE_B。这是我整理的一些数据

 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

Answer 1

使用 case 表达式，您可以通过单个查询(无需联合)来完成此操作:

SELECT  e.EmployeeID, 
        CASE WHEN ISNULL(ew.Rate, 0.0) > ISNULL(jw.Rate, 0.0) THEN 
            ew.Rate 
        ELSE 
            jw.Rate 
        END As Rate
FROM Employee e
LEFT JOIN TimeCards t On e.EmployeeID = t.EmployeeID
LEFT JOIN WageRate ew ON e.RateCode = ew.RateCode
LEFT JOIN WageRate jw ON t.JobRateCode = jw.RateCode

See a live demo on rextester.