27,579
社区成员
发帖
与我相关
我的任务
分享
...FROM # WHERE id>=aa.id and ==>FROM # WHERE id<=aa.id and ...
select b.*,a.*
from 表B b left join 表A a on b.职位代码=a.职位代码 and b.考区代码=substring(a.准考证号,3,2)
and (select count(1) from 表A where 职位代码=b.职位代码 and substring(准考证号,3,2)=b考区代码 and (成绩1+成绩2)>(a.成绩1+a.成绩2))<b.该职位招录人数*3
and a.成绩1+a.成绩2>=100
order by a.成绩1+a.成绩2 desc , a.成绩1 desc
??
SELECT *,id=IDENTITY(INT,1,1) INTO # FROM a
SELECT *,SUBSTRING(准考证号,3,2) q,(SELECT COUNT(1) FROM # WHERE id>=aa.id and SUBSTRING(准考证号,3,2)=SUBSTRING(aa.准考证号,3,2) and 职位代码=aa.职位代码 ) o into #1
FROM # aa
SELECT q 考区代码,aa.职位代码,count(1) 该职位上线人数 FROM #1 aa INNER JOIN b bb
ON aa.q=bb.考区代码 and AND=bb.职位代码
WHERE aa.o<=bb.该职位招录人数*3 and (成绩1+成绩2)>=100
GROUP BY q,职位代码
DROP TABLE #1,#