表A
KSH(考生号) CJ(成绩) ZYDM(专业代码)
1001 601 01
1002 602 02
...表B
ZYDM(专业代码) BL(比例) FSX(分数线) ZYRS(专业容纳人数)
01 120 600 120
02 110 599 60
....规则:每个专业都要求一个分数线,考生的成绩必须大于等于分数线
如果专业过线人数较多,则不能超过比例数,比例指的是百分比,120意思即为120%,比例字段的值大于等于100
如果多名考生成绩相同,那么同分考生都应该分配到该专业里,即使超过比例也要分配。请教用一个sql语句取出每个专业应该分配的人数。
KSH(考生号) CJ(成绩) ZYDM(专业代码)
1001 601 01
1002 602 02
...表B
ZYDM(专业代码) BL(比例) FSX(分数线) ZYRS(专业容纳人数)
01 120 600 120
02 110 599 60
....规则:每个专业都要求一个分数线,考生的成绩必须大于等于分数线
如果专业过线人数较多,则不能超过比例数,比例指的是百分比,120意思即为120%,比例字段的值大于等于100
如果多名考生成绩相同,那么同分考生都应该分配到该专业里,即使超过比例也要分配。请教用一个sql语句取出每个专业应该分配的人数。
我想请教在oracle中用sql语句怎么取结果
然后在看一下SQL写出来的结果是什么也一起贴出来!
这种可以用分析函数row_number() 来实现!
cosio兄,能否给一下完整的sql语句,我刚转到oracle上,对函数不太熟。
(select a.ksh,cj,zydm,b.bl,zyrs,rank()over(partition by a.zydm order by a.cj desc)rk
from a inner join b on a.zydm=b.zydm and a.cj>=b.fsx)
where rk<=zyrs*bl
group by zydm
试试这个
(select a.ksh,cj,zydm,b.bl,zyrs,rank()over(partition by a.zydm order by a.cj desc)rk
from a inner join b on a.zydm=b.zydm and a.cj>=b.fsx)
where rk<=trunc(zyrs*bl/100)
group by zydm
更改下
稍微测试了下,可以
SELECT ZYDM, COUNT(1) COUNTS
FROM (SELECT DENSE_RANK() OVER(PARTITION BY B.ZYDM ORDER BY A.CJ) DRN,
COUNT(1) OVER(PARTITION BY B.ZYDM) COUNTS,
B.*
FROM A, B
WHERE A.ZYDM = B.ZYDM
AND A.CJ >= B.FSX)
WHERE DRN <= DECODE(SIGN(ZYRS*BL/COUNTS-COUNTS),-1, ZYRS*BL/COUNTS, COUNTS)
GROUP BY ZYDM;
select a.zydm,a.ksh,a.cj,row_number() over(partition by a.zydm order by a.cj desc) r
from a
)
select ma.zydm,count(1) from
(
select nvl(min(a1.cj), 0) cj , b.zydm
from a1, b
where b.zydm = a1.zydm(+)
and a1.r <= b.zyrs * (b.bl / 100)
group by b.zydm
)ma,
a1
where a1.zydm(+) = ma.zydm
and a1.cj(+) >=ma.cj
group by ma.zydm
select zydm, count(1)
from (select a.ksh,
a.cj,
a.zydm,
b.bl,
b.zyrs,
rank() over(partition by a.zydm order by a.cj desc) rk
from a, b
where a.zydm = b.zydm
and a.cj >= b.fsx)
where rk <= trunc(zyrs * bl / 100)
group by zydm