基于两个不同的列选择哪个值更大

Question

我希望能够选择哪个费率更高，并在基于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

- BrianMichaels

---

如果您不将标识列用作代理键，那么在表中拥有标识列的意义是什么呢？我的意思是，如果您的“Employee”表中已经有了“EmployeeId”，为什么还需要一个“ID”列呢？ - Zohar Peled

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.

- Zohar Peled

Answer 2

SELECT e.employeeid,
CASE 
    WHEN wr_tc.rate > wr_emp.rate
        THEN wr_tc.rate
    ELSE wr_emp.rate
    END AS rate
FROM Employee e
INNER JOIN wagerate wr_emp ON wr_emp.ratecode = e.RateCode
INNER JOIN timecards tc ON tc.employeeid = e.employeeid
INNER JOIN wagerate wr_tc ON wr_tc.RateCode = tc.jobratecode

- ttallierchio

Answer 3

你觉得这样怎么样？

 SELECT 
    e.EmployeeID,
    tc.Hours,
    PayRate = CASE WHEN wr1.Rate > wr2.Rate THEN wr1.Rate ELSE wr2.Rate END,
    TotalPay = tc.Hours * CASE WHEN wr1.Rate > wr2.Rate THEN wr1.Rate ELSE wr2.Rate END
 FROM 
    #Employee e
    JOIN #WageRate wr1
        ON e.RateCode = wr1.RateCode
    JOIN #TimeCards tc
        ON e.EmployeeID = tc.EmployeeID
    JOIN #WageRate wr2
        ON tc.JobRateCode = wr2.RateCode;

结果...

EmployeeID  Hours                                   PayRate                                 TotalPay
----------- --------------------------------------- --------------------------------------- ---------------------------------------
200         8.00                                    25.00                                   200.0000
100         8.00                                    25.00                                   200.0000

- Jason A. Long

Answer 4

不使用CASE逻辑来完成这个任务：

SQL Fiddle

查询：

SELECT s.EmployeeID, s.Hours, s.jobRate, s.wageRate
  , ( SELECT max(r) FROM (VALUES (s.jobRate),(s.wageRate)) AS value(r)) AS maxRate
FROM (
  SELECT e.EmployeeID, t.Hours
      , COALESCE(w1.Rate,0) AS jobRate
      , COALESCE(w2.Rate,0) AS wageRate
  FROM TimeCards t
  INNER JOIN Employee e ON t.EmployeeID = e.EmployeeID
     LEFT OUTER JOIN WageRate w1 ON e.RateCode = w1.RateCode          
  LEFT OUTER JOIN WageRate w2 ON t.jobRateCode = w2.RateCode
) s
;

结果：

| EmployeeID | Hours | jobRate | wageRate | maxRate |
|------------|-------|---------|----------|---------|
|        100 |     8 |      10 |       25 |      25 |
|        200 |     8 |      25 |       10 |      25 |

- Shawn