如何在sql server中使用select*和group by subquery

Question

我有员工工资明细表记录，列为

Id  Name        Year    Month   Salary
1   ABC         2021    Jan     50000
2   PQR         2021    Jan     40000
3   KLM         2021    Feb     45000
4   LMN         2021    Jan     55000
5   LMN         2022    Jan     20000
6   ABC         2022    Feb     25000
7   ABC         2022    Jan     2500
8   ABC         2022    Dec     60000
9   LMN         2022    Nov     70000

现在我想找到哪个员工从加入中获得的工资超过100000，并显示员工的所有数据

--找出哪位员工到目前为止的工资超过100000

select  name,sum(salary) as AnnualSalary from tblEmpsalary
group by Name
having sum(Salary)>100000 --this query works

--但下面的查询没有显示任何数据，(我想显示总工资超过100000的员工的所有数据(

SELECT id, name,Month,Year, SUM(Salary) AS TotalSales
FROM tblEmpsalary
GROUP BY name,Id,Month,Year,Salary
having SUM(Salary)>100000;

Answer 1

SELECT T.ID,T.Name,T.Year,T.Month,T.Salary
FROM tblEmpsalary AS T
JOIN
(
select  ID
from tblEmpsalary
group by ID
having sum(Salary)>100000
)AS X ON T.ID=X.ID

Answer 2

您可以为此使用窗口函数

SELECT
id,
name,
Month,
Year,
TotalSales
FROM (
SELECT *,
SUM(Salary) OVER (PARTITION BY name) AS TotalSales
FROM tblEmpsalary e
) e
WHERE e.TotalSales > 100000;

Answer 3

请尝试以下查询，其中一个查询用于分组，另一个查询正在加入以获取员工详细信息：

SELECT TS.id, TS.name, TS.Month,Year, TS.Salary, ATS.TotalSales FROM 
(SELECT Month, Year, SUM(Salary) AS TotalSales 
FROM tblEmpsalary 
GROUP BY Month,Year,Salary
HAVING SUM(Salary)>100000
) AS ATS
LEFT OUTER JOIN tblEmpsalary TS on ATS.Month = TS.Month and ATS.Year = TS.Year
ORDER BY TS.name, TS.Id, TS.Month, TS.Year, ATS.TotalSales