我有这个表(worker)
ID NAME ST SALARY DEP_ID
----- -------------------- -- --------- ---------
1 Arun MH 55000 22
2 Manish MP 53000 11
3 Rahul GJ 45000 22
我想获得平均薪水,在这个平均值中,我不想包括最低薪水。
我写了这个查询
select AVG(w.salary)
from Worker w
where w.salary >(select MIN(w.salary) from Worker w)
可以在不使用子查询的情况下完成此操作?
我不知道这是否是sql
中最好的praticsSelect (SUM(w.salary)-MIN(w.salary))/(COUNT(w.salary)-1) from Worker w
好吧,平均值只是总和,所以您可以做:
select (SUM(w.salary) - MIN(w.salary)) / (COUNT(w.salary) - 1)
from Worker w
编辑:根据注释,上述解决方案如果最小一个以上,则不得不排除所有最小值。为了在不使用子查询的情况下解决这个问题,我只能想到使用光标(看起来像过度杀伤)。但是,以下内容适合以下要求:
DECLARE @min int = NULL
DECLARE @numMin int = 1
DECLARE @sum int = 0
DECLARE @current int
DECLARE @count int = 0
DECLARE db_cursor CURSOR FOR
SELECT salary
FROM Worker
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @current
WHILE @@FETCH_STATUS = 0
BEGIN
IF @min IS NULL BEGIN
SET @min = @current
END
ELSE IF @current < @min BEGIN
SET @min = @current
SET @numMin= 1
END
ELSE IF @current = @min BEGIN
SET @numMin= @numMin + 1
END
SET @sum = @sum + @current
SET @count = @count + 1
FETCH NEXT FROM db_cursor INTO @current
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT (@sum - @numMin * @min) / (@count - @numMin)
我认为没有子查询就无法真正准确地做到这一点。问题是多个记录可能具有最低工资。实际上,所有薪水都可能是最低,甚至可能无法定义平均值。
您可以做:
select AVG(w.salary)
from (select w.*, min(w.salary) over () as minsalary
from Worker w
) w
where w.salary > minsalary;
仍然有一个子查询,但在where
子句中没有。