有表 info_job(id,station,min_salary,max_salary)我想知道按职位(station)分组,分组数量大于2的min_salary,max_salary(要求min_salary和max_salary:分组统计时按值升序,为奇数取中间数,为偶数取中间两数平均数)
结果是:
1 程序员 2000 5000
2 销售 2500 4000
3 会计 1500 6000非常感谢。
结果是:
1 程序员 2000 5000
2 销售 2500 4000
3 会计 1500 6000非常感谢。
解决方案 »
- 数据库连接失败
- 高手帮我看看这段存储过程为什么会很慢
- 求一个SQL语句,急!!!
- SQL server 7.0 和2000 之间的日期数据格式是否不同?
- 批量更新主键值续 (--循环) 高手请进
- 我设计的一张表中有S_time这个字段,现在要求输入两个不同的时间,找出在这两个时间段中共有多少条记录,不知道该怎么做了,因为这两个时间段
- 在线急求!
- 怎么select 自定义函数会返回两条记录的?
- 关于SQL查询连续日期记录的问题
- 在asp,net中与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。
- job执行ssis包报错code:0x1000007。急急急
- 跨服务器连接数据库报错
用T-SQL慢慢编程吧,^_^!
insert info_job
select 1 ,'程序员', 2000 ,5000 union all
select 2 ,'销售', 2800 ,4000 union all
select 3 ,'会计', 2300 ,6000 union all
select 4 ,'销售', 2000 ,5000 union all
select 5 ,'销售', 2500 ,4000 union all
select 6 ,'会计', 1500 ,6000 union all
select 7 ,'程序员', 3000 ,5000 union all
select 8 ,'财务', 2500 ,4000 union all
select 9 ,'会计', 5500 ,6000
;with cte as
(
select *,
min1=row_number() over (partition by station order by min_salary),
max1=row_number() over (partition by station order by max_salary)
from
info_job
)
select station,
(case when count(*)%2=0
then (select sum(min_salary)/2 from cte b
where a.station=b.station
and b.min1 between max(a.min1)/2 and max(a.min1)/2+1)
else
(select min_salary from cte b
where a.station=b.station
and b.min1= max(a.min1)/2+1)
end) as min_salary,
(case when count(*)%2=0
then (select sum(max_salary)/2 from cte b
where a.station=b.station
and b.max1 between max(a.max1)/2 and max(a.max1)/2+1)
else
(select max_salary from cte b
where a.station=b.station
and b.max1= max(a.max1)/2+1)
end) as max_salary
from
cte a
group by station
having count(*)>1
/*
station min_salary max_salary
-------- ----------- -----------
程序员 2500 5000
会计 2300 6000
销售 2500 4000(3 行受影响)
*/
create table info_job(id int,station varchar(8),min_salary int,max_salary int)
insert info_job
select 1 ,'程序员', 2000 ,5000 union all
select 2 ,'销售', 2800 ,4000 union all
select 3 ,'会计', 2300 ,6000 union all
select 4 ,'销售', 2000 ,5000 union all
select 5 ,'销售', 2500 ,4000 union all
select 6 ,'会计', 1500 ,6000 union all
select 7 ,'程序员', 3000 ,5000 union all
select 8 ,'财务', 2500 ,4000 union all
select 9 ,'会计', 5500 ,6000goselect aa.station
,case when bb._count%2=0 then bb.min_salary else aa.min_salary end
,case when bb._count%2=0 then bb.max_salary else aa.max_salary end
from
(
select station,row_number() over(partition by station order by min_salary) ids,min_salary,max_salary
from info_job bb group by station,min_salary,max_salary
) aa
inner join
(
select station,count(*) _count,count(*)/2+1 ids,avg(min_salary) min_salary,avg(max_salary) max_salary from info_job group by station having count(*) > 1
)bb on aa.station = bb.station and aa.ids = bb.ids
,case when bb._count%2=0 then bb.min_salary else aa.min_salary end
,case when bb._count%2=0 then bb.max_salary else aa.max_salary end
from
(
select station,row_number() over(partition by station order by min_salary) ids,min_salary,max_salary
from info_job bb group by station,min_salary,max_salary
) aa
inner join
(
select station,count(*) _count,count(*)/2+1 ids,avg(min_salary) min_salary,avg(max_salary) max_salary from info_job group by station having count(*) > 1
)bb on aa.station = bb.station and aa.ids = bb.ids这个是你回的啊 嗯 我想把min_salary+max_salary查出来 嗯 可不能使用别名 怎么解决呢
select aa.station
,case when bb._count%2=0 then bb.min_salary else aa.min_salary end a
,case when bb._count%2=0 then bb.max_salary else aa.max_salary end b,
case when bb._count%2=0 then bb.min_salary+bb.max_salary else aa.min_salary+aa.max_salary end
from
(
select station,row_number() over(partition by station order by min_salary) ids,min_salary,max_salary
from info_job bb group by station,min_salary,max_salary
) aa
inner join
(
select station,count(*) _count,count(*)/2+1 ids,avg(min_salary) min_salary,avg(max_salary) max_salary from info_job group by station having count(*) > 1
)bb on aa.station = bb.station and aa.ids = bb.ids