sql请教 MSSQL语句

qxyywy 发布于 2011/07/28 12:44
阅读 189
收藏 0
有表 info_job(id,station,min_salary,max_salary)

我想知道按职位(station)分组,分组数量大于2的min_salary,max_salary(要求min_salary和max_salary:分组统计时按值升序,为奇数取中间数,为偶数取中间两数平均数)
数据大概是这样的
1 程序员 2000 5000 
2 销售 2800 4000
3 会计 2300 6000
4 销售 2000 5000 
5 销售 2500 4000
6 会计 1500 6000
7 程序员 3000 5000 
8 财务 2500 4000

9 会计 5500 6000

不要用proc 要用SQL语句

这是我之前写的

DECLARE @jinfo TABLE (
job_id INT,
min_salary INT,
max_salary INT,
station VARCHAR(64),
rownumber int
)

DECLARE @jcount TABLE (
station VARCHAR(64),
countnum int
)

INSERT INTO @jinfo
SELECT job_id,min_salary,max_salary,station ,row_number() over(partition by station order by station,min_salary asc) rowNumber FROM info_job ij WHERE ij.station IN (
SELECT * FROM (
SELECT station FROM info_job WHERE min_salary>500 ) temp GROUP BY temp.station
HAVING COUNT(*)>1
)AND ij.min_salary>500

INSERT INTO @jcount
SELECT station,COUNT(*) FROM @jinfo GROUP BY station


SELECT AVG(min_salary) jobmin,AVG(max_salary) jobmax,(AVG(min_salary)+AVG(max_salary))/2 jobavg,station FROM (
SELECT a.*,b.countnum FROM @jinfo a LEFT JOIN @jcount b ON a.station=b.station
) c WHERE (c.countnum%2=1 AND c.rownumber=(c.countnum/2+1))
or (c.countnum%2!=1 AND ((c.rownumber=(c.countnum/2+1))
OR (c.rownumber=(c.countnum/2)))

) GROUP BY station 

这条SQL的问题在薪资排序问题上出了错 是按的min_salary 拍的序 对于 min_salary 的数据时正确的 但max_salary的值就有可以能不对了

加载中
返回顶部
顶部