我使用的是 SQL 服務器而不是（MYSQL），并試圖檢索其部門中或組中的最低多重平均工資。
我可以使用以下查詢按 (group by) 檢索所有部門的工資平均值：
select d.Dname,avg(e.salary) as averagr_salary
from Employee e, Departments d
where d.Dnum = e.dno
group by e.Dno,d.Dname
類似資料的結果（Department_Name 和average_salary）：
name avg
DP1 139
DP2 1050
DP3 1250
我只想檢索這些多重平均值中的最小值：
name avg
DP1 139
我試過這個查詢，但它顯示錯誤：
select d.Dname,min(avg(e.salary)) as averagr_salary
from Employee e, Departments d
where d.Dnum = e.dno
group by e.Dno,d.Dname
錯誤：
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
你可以幫幫我嗎
uj5u.com熱心網友回復：
它應該是這樣的：
Select tbl.Dname,min(averagr_salary) as averagr_salary
from
(
select d.Dname,avg(e.salary) as averagr_salary
from Employee e
inner join Departments d
on d.Dnum = e.dno
group by e.Dno,d.Dname
) as tbl
group by tbl.Dname
order by averagr_salary ASC limit 1;
演示：https : //www.db-fiddle.com/f/vhqJXYFy52xRtVBc97R1EL/1
對于 SQL Server：
Select top 1 tbl.Dname,min(averagr_salary) as averagr_salary
from
(
select d.Dname,avg(e.salary) as averagr_salary
from Employee e
inner join Departments d
on d.Dnum = e.dno
group by e.Dno,d.Dname
) as tbl
group by tbl.Dname
order by averagr_salary ASC;
演示：https : //dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=303b9fab3403397b1df8ee79b9b216a6
uj5u.com熱心網友回復：
小提琴（更新）：https : //dbfiddle.uk/?rdbms = sqlserver_2019&fiddle =bb4d5b2989e06f92bcb8c39742798ba4
一些 stackoverflow UI 控制元件的行為很奇怪。許多正常控制元件、評論、編輯控制元件等在大約 12 小時內都無法使用。
此解決方案是標準 SQL，可與 SQL Server（最新版本）一起使用：
另請注意，當有多個組的平均值與最小值匹配時，該邏輯有效，而不僅僅是一個。
WITH cte AS (
SELECT d.Dname
, AVG(e.salary) AS average_salary
, RANK() OVER (ORDER BY AVG(e.salary)) AS n
FROM Employee e
JOIN Departments d
ON d.Dnum = e.Dno
GROUP BY e.Dno, d.Dname
)
SELECT Dname, average_salary
FROM cte
WHERE n = 1
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/324407.html
上一篇：有沒有辦法為mysql中的所有列找到唯一的值計數和空百分比？
下一篇：訪問上月查詢