科目表 id 年级 科目
1 1 语文
2 1 数学
3 2 语文
教师科目表 id user 科目id
1 张三 1
2 李四 2
要求学年升级
教师科目表变更为
id user 科目id
1 张三 3
说明(当年级升级时,张三一年级语文变为二年级语文,李四二年级没有数学,删除李四科目表的记录)
1 1 语文
2 1 数学
3 2 语文
教师科目表 id user 科目id
1 张三 1
2 李四 2
要求学年升级
教师科目表变更为
id user 科目id
1 张三 3
说明(当年级升级时,张三一年级语文变为二年级语文,李四二年级没有数学,删除李四科目表的记录)
id 年级 科目
1 1 语文
2 1 数学
3 2 语文
教师科目表
id user 科目id
1 张三 1(为科目表ID)
2 李四 2
第一年张三科目为语文,第二年发生变更科目表的一年级语文变为二年级语文,数学二年级没有所以删除数学
教师科目表变更为
id user 科目id
1 张三 3(为科目表ID)
这样可不可以看懂
go
create table [科目表]([id] int,[年级] int,[科目] varchar(4))
insert [科目表]
select 1,1,'语文' union all
select 2,1,'数学' union all
select 3,2,'语文'
if object_id('[教师科目表]') is not null drop table [教师科目表]
go
create table [教师科目表]([id] int,[user] varchar(4),[科目id] int)
insert [教师科目表]
select 1,'张三',1 union all
select 2,'李四',2
-->查询
select * into # from 教师科目表;
go
truncate table 教师科目表;
goinsert 教师科目表
select row_number() over(order by getdate()),b.[user],a.id
from 科目表 a,# b,科目表 c
where b.科目id=c.id and a.年级=c.年级+1 and a.科目=c.科目
goselect * from 教师科目表/**
id user 科目id
----------- ---- -----------
1 张三 3(1 行受影响)
**/
--测试数据
declare @科目表 table(id int,年级 int,科目 varchar(20))
insert @科目表
select 1, 1, '语文' union all
select 2, 1, '数学' union all
select 3, 2, '语文'declare @教师科目表 table(id int,[user] varchar(20),科目id int)
insert @教师科目表
select 1, '张三',1 union all
select 2, '李四',2--执行查询
--方法1
select a.id,a.[user],(select id from @科目表 r where exists (select 1 from @科目表 where a.科目id=id and 科目=r.科目 and 年级+1=r.年级)) 科目id
from @教师科目表 a,@教师科目表 b
where a.id=b.id and exists
(
select id from @科目表 r where exists (select 1 from @科目表 where a.科目id=id and 科目=r.科目 and 年级+1=r.年级)
)--方法2
select *
from (
select a.id,a.[user],(select id from @科目表 r where exists (select 1 from @科目表 where a.科目id=id and 科目=r.科目 and 年级+1=r.年级)) 科目id
from @教师科目表 a
) t where 科目id is not null/*结果
id user 科目id
----------- -------------------- -----------
1 张三 3(1 行受影响)id user 科目id
----------- -------------------- -----------
1 张三 3(1 行受影响)
*/
if object_id('[科目表]') is not null drop table [科目表]
go
create table [科目表]([id] int,[年级] int,[科目] varchar(4))
insert [科目表]
select 1,1,'语文' union all
select 2,1,'数学' union all
select 3,2,'语文'
if object_id('[教师科目表]') is not null drop table [教师科目表]
go
create table [教师科目表]([id] int,[user] varchar(4),[科目id] int)
insert [教师科目表]
select 1,'张三',1 union all
select 2,'李四',2create proc proc_1
@user varchar(20),
@level int
as
update 教师科目表 set 科目id = b.id
from 科目表 b
where [user]=@user and b.年级=@level and b.科目=(select 科目 from 科目表 where id=科目id )
delete from 教师科目表 where 教师科目表.[user]=@user and not exists (select * from 科目表 where 科目表.年级=@level and 科目表.id = 教师科目表.科目id)
goexec proc_1 '张三',2select * from [教师科目表]