use student
/*
drop table #registerdetaillearning
drop table #v_usertotalcredit
drop table #achievementsys
drop table #xuankecredit
drop table #table
*/
--建立临时表
select * into #registerdetaillearning from registerdetaillearning a
where isnull(a.xj_level,a.stu_level)='200609'

select * into #v_usertotalcredit from v_usertotalcredit

select userid into #achievementsys from achievementsys where left(examid,6)=200707

select a.userid as userid,sum(isnull(b.studycredit,0)) as xuankecredit into #xuankecredit
from v_xuankehistory a left join subjectcoursesys b on a.courseid=b.courseid group by a.userid

--执行查询
select identity(int,1,1) as 序号, a.fdzname as 服务站,a.studentname as 姓名,a.userid as 用户名,a.studykind as 层次,
a.subject as 专业,a.telephone as 电话,a.phonenum as 手机,e.xuankecredit as 选课学分,
isnull(b.totalcredit,0) as 已取得学分
into #table --//www.
from #registerdetaillearning a left join #v_usertotalcredit b on a.userid=b.userid
left join #xuankecredit e on a.userid=e.userid
where b.totalcredit between 1 and 10 and a.userid not in (select userid from #achievementsys)
order by 服务站

select * from #table