学生表T1:
年级 班级 姓名 学号
初一 1班 AA 01
初一 1班 BB 02
初一 2班 CC 03
初二 1班 DD 04
初二 2班 EE 05
初二 3班 FF 06
初三 1班 WW 07
...
班级排除表T2:
年级 班级
初二 1班
初一 2班
..
说明:我要查询到的结果是:显示T1中除了T2表中的班级外的所有学生记录并且记录的学号字段加上序号(序号从1开始依次递增1),即: 年级 班级 姓名 学号
初一 1班 AA 011
初一 1班 BB 022
初二 1班 DD 043
初二 3班 FF 064
初三 1班 WW 075 表T1和T2都是动态的数据.
年级 班级 姓名 学号
初一 1班 AA 01
初一 1班 BB 02
初一 2班 CC 03
初二 1班 DD 04
初二 2班 EE 05
初二 3班 FF 06
初三 1班 WW 07
...
班级排除表T2:
年级 班级
初二 1班
初一 2班
..
说明:我要查询到的结果是:显示T1中除了T2表中的班级外的所有学生记录并且记录的学号字段加上序号(序号从1开始依次递增1),即: 年级 班级 姓名 学号
初一 1班 AA 011
初一 1班 BB 022
初二 1班 DD 043
初二 3班 FF 064
初三 1班 WW 075 表T1和T2都是动态的数据.
if object_id('tempdb.dbo.#学生表') is not null drop table #学生表
create table #学生表 (年级 varchar(4),班级 varchar(3),姓名 varchar(2),学号 varchar(2))
insert into #学生表
select '初一','1班','AA','01' union all
select '初一','1班','BB','02' union all
select '初一','2班','CC','03' union all
select '初二','1班','DD','04' union all
select '初二','2班','EE','05' union all
select '初二','3班','FF','06' union all
select '初三','1班','WW','07'
--> 测试数据: #班级排除表T2
if object_id('tempdb.dbo.#班级排除表') is not null drop table #班级排除表
create table #班级排除表 (年级 varchar(4),班级 varchar(3))
insert into #班级排除表
select '初二','1班' union all
select '初一','2班'
goselect s.* into #
from #学生表 s
where not exists(select * from #班级排除表 c where s.年级=c.年级 and s.班级=c.班级)select 年级,班级,姓名,学号+ltrim((select count(*) from # where 学号<=a.学号)) as 学号
from # as ago
drop table #学生表,#班级排除表,#
/*
年级 班级 姓名 学号
---- ---- ---- --------------
初一 1班 AA 011
初一 1班 BB 022
初二 2班 EE 053
初二 3班 FF 064
初三 1班 WW 075(5 row(s) affected)
*/
where not exists (
select 1 from T2
where T2.年级=T1.年级 and T2.班级=T1.班级
)
--> 测试数据: #学生表
if object_id('tempdb.dbo.#学生表') is not null drop table #学生表
create table #学生表 (年级 varchar(4),班级 varchar(3),姓名 varchar(2),学号 varchar(2))
insert into #学生表
select '初一','1班','AA','01' union all
select '初一','1班','BB','02' union all
select '初一','2班','CC','03' union all
select '初二','1班','DD','04' union all
select '初二','2班','EE','05' union all
select '初二','3班','FF','06' union all
select '初三','1班','WW','07'
--> 测试数据: #班级排除表T2
if object_id('tempdb.dbo.#班级排除表') is not null drop table #班级排除表
create table #班级排除表 (年级 varchar(4),班级 varchar(3))
insert into #班级排除表
select '初二','1班' union all
select '初一','2班'
gowith CTE as
(
select s.*
from #学生表 s
where not exists(select * from #班级排除表 c where s.年级=c.年级 and s.班级=c.班级)
)
select 年级,班级,姓名,学号+ltrim(row_number() over(order by 学号)) as 学号
from CTEgo
drop table #学生表,#班级排除表
/*
年级 班级 姓名 学号
---- ---- ---- --------------------------
初一 1班 AA 011
初一 1班 BB 022
初二 2班 EE 053
初二 3班 FF 064
初三 1班 WW 075(5 row(s) affected)
*/
not exists (
select 1 from T2
where 年级=a1.年级 and 班级=a1.班级
)
)as varchar)
from T1 a
where not exists (
select 1 from T2
where 年级=a.年级 and 班级=a.班级
)
if object_id('tempdb.dbo.#学生表') is not null drop table #学生表
create table #学生表 (年级 varchar(4),班级 varchar(3),姓名 varchar(2),学号 varchar(2))
insert into #学生表
select '初一','1班','AA','01' union all
select '初一','1班','BB','02' union all
select '初一','2班','CC','03' union all
select '初二','1班','DD','04' union all
select '初二','2班','EE','05' union all
select '初二','3班','FF','06' union all
select '初三','1班','WW','07'
--> 测试数据: #班级排除表T2
if object_id('tempdb.dbo.#班级排除表') is not null drop table #班级排除表
create table #班级排除表 (年级 varchar(4),班级 varchar(3))
insert into #班级排除表
select '初二','1班' union all
select '初一','2班'
go
select 年级,班级,姓名,学号=a.学号+cast((select count(1) from #学生表 a1 where 学号<=a.学号 and
not exists (
select 1 from #班级排除表
where 年级=a1.年级 and 班级=a1.班级
)
)as varchar)
from #学生表 a
where not exists (
select 1 from #班级排除表
where 年级=a.年级 and 班级=a.班级
)--结果
年级 班级 姓名 学号
---- ---- ---- --------------------------------
初一 1班 AA 011
初一 1班 BB 022
初二 2班 EE 053
初二 3班 FF 064
初三 1班 WW 075(所影响的行数为 5 行)
--> 测试数据: #学生表
if object_id('tempdb.dbo.#学生表') is not null drop table #学生表
create table #学生表 (年级 varchar(4),班级 varchar(3),姓名 varchar(2),学号 varchar(2))
insert into #学生表
select '初一','1班','AA','01' union all
select '初一','1班','BB','02' union all
select '初一','2班','CC','03' union all
select '初二','1班','DD','04' union all
select '初二','2班','EE','05' union all
select '初二','3班','FF','06' union all
select '初三','1班','WW','07'
--> 测试数据: #班级排除表T2
if object_id('tempdb.dbo.#班级排除表') is not null drop table #班级排除表
create table #班级排除表 (年级 varchar(4),班级 varchar(3))
insert into #班级排除表
select '初二','1班' union all
select '初一','2班'
go
select 年级,班级,姓名,
学号=学号+(select ltrim(count(1)) from #学生表 a where not exists(select * from #班级排除表 c where a.年级=c.年级 and a.班级=c.班级) and 姓名<=s.姓名)
from #学生表 s
where not exists(select * from #班级排除表 c where s.年级=c.年级 and s.班级=c.班级)
insert into #T1
select '初一','1班','AA','01' union all
select '初一','1班','BB','02' union all
select '初一','2班','CC','03' union all
select '初二','1班','DD','04' union all
select '初二','2班','EE','05' union all
select '初二','3班','FF','06' union all
select '初三','1班','WW','07'
create table #T2 (年级 varchar(4),班级 varchar(3))
insert into #T2
select '初二','1班' union all
select '初一','2班'
goSELECT 年级,班级,姓名,学号,IDENTITY(smallint, 1, 1) AS [ID]
into #t FROM #T1 WHERE NOT EXISTS(SELECT *FROM #T2 WHERE #T2.年级=#T1.年级 and #T2.班级=#T1.班级) select 年级,班级,姓名,学号+cast(ID as varchar(10)) as 学号 from #t
create table #学生表 (年级 varchar(4),班级 varchar(3),姓名 varchar(2),学号 varchar(2))
insert into #学生表
select '初一','1班','AA','01' union all
select '初一','1班','BB','02' union all
select '初一','2班','CC','03' union all
select '初二','1班','DD','04' union all
select '初二','2班','EE','05' union all
select '初二','3班','FF','06' union all
select '初三','1班','WW','07'
--> 测试数据: #班级排除表T2
if object_id('tempdb.dbo.#班级排除表') is not null drop table #班级排除表
create table #班级排除表 (年级 varchar(4),班级 varchar(3))
insert into #班级排除表
select '初二','1班' union all
select '初一','2班'
go
select 年级,班级,姓名,学号=a.学号+cast((select count(1) from #学生表 a1 where 学号<=a.学号 and
not exists (
select 1 from #班级排除表
where 年级=a1.年级 and 班级=a1.班级
)
)as varchar)
from #学生表 a
where not exists (
select 1 from #班级排除表
where 年级=a.年级 and 班级=a.班级
)--结果
年级 班级 姓名 学号
---- ---- ---- --------------------------------
初一 1班 AA 011
初一 1班 BB 022
初二 2班 EE 053
初二 3班 FF 064
初三 1班 WW 075(所影响的行数为 5 行)海阔天空的 就可以
if object_id('tempdb.dbo.#t2') is not null drop table #t2
if object_id('tempdb.dbo.#tmp') is not null drop table #tmpcreate table #t1 (NJ char(4) ,BJ char(4),XM char(2),XH char(3))
create table #t2 (NJ nvarchar(4),BJ nvarchar(4))insert into #t1 values('初一','1班','aa','01')
insert into #t1 values('初一','1班','bb','02')
insert into #t1 values('初一','2班','cc','03')
insert into #t1 values('初二','1班','dd','04')
insert into #t1 values('初二','2班','ee','05')
insert into #t1 values('初二','3班','ff','06')
insert into #t1 values('初三','1班','ww','07')
insert into #t2 values('初二','1班')
insert into #t2 values('初一','2班')select *,identity(int,1,1) as id into #tmp from #t1
where nj+bj not in (select nj+bj from #t2)select nj as 年级,bj as 班级,xm as 姓名,rtrim(xh)+rtrim(id) as 编号 from #tmp
年级 班级 姓名 编号
初一 1班 aa 011
初一 1班 bb 022
初二 2班 ee 053
初二 3班 ff 064
初三 1班 ww 075
if object_id('tempdb.dbo.#t1') is not null drop table #t1
if object_id('tempdb.dbo.#t2') is not null drop table #t2
if object_id('tempdb.dbo.#tmp') is not null drop table #tmp create table #t1 (NJ char(4) ,BJ char(4),XM char(2),XH char(3))
create table #t2 (NJ nvarchar(4),BJ nvarchar(4)) insert into #t1 values('初一','1班','aa','01')
insert into #t1 values('初一','1班','bb','02')
insert into #t1 values('初一','2班','cc','03')
insert into #t1 values('初二','1班','dd','04')
insert into #t1 values('初二','2班','ee','05')
insert into #t1 values('初二','3班','ff','06')
insert into #t1 values('初三','1班','ww','07')
insert into #t2 values('初二','1班')
insert into #t2 values('初一','2班') select *,identity(int,1,1) as id into #tmp from #t1
where nj+bj not in (select nj+bj from #t2) select nj as 年级,bj as 班级,xm as 姓名,rtrim(xh)+rtrim(id) as 编号 from #tmp