sql - SQL-使用Sum命令将同一个人的薪水相乘

我正试图将那些至少治疗2名患者的员工的工资提高10％。我的问题是，每治疗一名患者，薪水首先乘以2，然后最后乘以10％。例如，如果该员工的年薪为25.000并治疗3个人，则新工资为82.500。

select distinct t.empNbr, e.Salary, sum(e.Salary*1.1) as NewSalary from Treats t
inner join Employee e
on e.empNbr=t.empNbr
WHERE t.empNbr IN
(
SELECT empNbr
  FROM Treats
  GROUP BY empNbr
  HAVING COUNT(*) >= 2)
group by t.empNbr, e.Salary

最佳答案

交叉申请应有助于：

SELECT  e.empNbr,
        e.Salary,
        e.Salary*1.1 as NewSalary
FROM Employee e
CROSS APPLY (
    SELECT  empNbr
    FROM Treats
    WHERE e.empNbr = empNbr
    GROUP BY empNbr
    HAVING COUNT(*) > 1
    ) as t

t部分得到我们需要的empNbr。然后我们从empNbr表中选择salary和Employee并进行数学:)

另一种方式：

SELECT  TOP 1 WITH TIES
                        e.empNbr,
                        e.Salary,
                        e.Salary*1.1 as NewSalary
FROM Employee e
INNER JOIN Treats t
    ON e.empNbr = t.empNbr
ORDER BY
    CASE WHEN COUNT(t.empNbr) OVER (PARTITION BY t.empNbr ORDER BY t.empNbr) > 1 THEN 1 ELSE 0 END DESC,
        ROW_NUMBER() OVER (PARTITION BY t.empNbr ORDER BY t.empNbr)

关于sql - SQL-使用Sum命令将同一个人的薪水相乘，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39684854/