1. --利用临时表 select [ID]=identity(int,1,1),* into # from 表 --利用表变量 declare @tb table ( ID int identity, student_id varchar(10), subject_id varchar(10), score int ) insert @tb(student_id,subject_id,score) select * from 表 --临时添加identity列 exec('alter table 表 add ID int identity') //其他操作 exec('alter table 表 drop column ID')2. insert 临时表 select * from 表 t where not exists(select 1 from 临时表 where student_id=t.student_id and subject_id=t.subject_id and score=t.score )
第一个问题用(student_id,subject_id)作主鍵, 第二个问题 insert into temp SELECT * FROM TABLE1 WHERE [ID] NOT IN(SELECT [ID] FROM TEMP)
update studentsjs set score=86 from studentsjs where (student_id,subject_id) in (select top 7 (student_id,subject_id) from studentsjs) and id not in (select top 6 (student_id,subject_id) from studentsjs);我把(student_id,subject_id)当作主建,他的提示错误是“,”错误,我改成英文输入法还是不可以阿,怎么回事,帮忙说下
--利用临时表
select [ID]=identity(int,1,1),* into # from 表 --利用表变量
declare @tb table
(
ID int identity,
student_id varchar(10),
subject_id varchar(10),
score int
)
insert @tb(student_id,subject_id,score)
select * from 表 --临时添加identity列
exec('alter table 表 add ID int identity')
//其他操作
exec('alter table 表 drop column ID')2. insert 临时表
select * from 表 t
where
not exists(select 1 from 临时表
where student_id=t.student_id and
subject_id=t.subject_id and
score=t.score
)
第二个问题
insert into temp
SELECT * FROM TABLE1 WHERE [ID] NOT IN(SELECT [ID] FROM TEMP)
set score=86
from studentsjs
where (student_id,subject_id) in
(select top 7 (student_id,subject_id) from studentsjs)
and id not in
(select top 6 (student_id,subject_id) from studentsjs);我把(student_id,subject_id)当作主建,他的提示错误是“,”错误,我改成英文输入法还是不可以阿,怎么回事,帮忙说下