在查询语句中增加序号字段(sql server)

最新推荐文章于 2024-12-09 17:19:29 发布

原创最新推荐文章于 2024-12-09 17:19:29 发布 · 4.2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #table #join #电话 #手机

数据库技术专栏收录该内容

99 篇文章

订阅专栏

本文通过一个具体的SQL脚本示例介绍了如何使用T-SQL创建并操作临时表，包括从现有表中筛选数据到临时表、联表查询及最终的数据汇总展示等步骤。

直接贴个例子

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
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