给你一个求期刊缺刊的问题的解答,你看明白了你这个问题就OK了create table qikan
(
书号 int,
书名 varchar(10),
年份 int,
期数 int,
刊种 varchar(10)
)insert qikan select 1, '电脑', 2004 , 1 , '月刊'
union all select 1 , '电脑', 2004 , 2 , '月刊'
union all select 1 , '电脑', 2004 , 3 , '月刊'
union all select 1 , '电脑', 2004 , 5 , '月刊'
union all select 3 , '读者', 2004 , 1 , '月刊'
union all select 3 , '读者', 2004 , 4 , '月刊'
union all select 3 , '读者', 2004 , 5 , '月刊'
union all select 3 , '读者', 2004 , 6 , '月刊'select *
from
(
select N.书号,N.书名,N.年份,T.期数,N.刊种
from
(
select distinct 书号,书名,年份,刊种,0 as 关联
from qikan
) N,
(select 0 as 关联,1 as 期数
union all
select 0 as 关联,2 as 期数
union all
select 0 as 关联,3 as 期数
union all
select 0 as 关联,4 as 期数
union all
select 0 as 关联,5 as 期数
union all
select 0 as 关联,6 as 期数
) T
where T.关联=N.关联
) TmpA
where 期数 not in (select 期数 from qikan where qikan.书号=TmpA.书号)
(
书号 int,
书名 varchar(10),
年份 int,
期数 int,
刊种 varchar(10)
)insert qikan select 1, '电脑', 2004 , 1 , '月刊'
union all select 1 , '电脑', 2004 , 2 , '月刊'
union all select 1 , '电脑', 2004 , 3 , '月刊'
union all select 1 , '电脑', 2004 , 5 , '月刊'
union all select 3 , '读者', 2004 , 1 , '月刊'
union all select 3 , '读者', 2004 , 4 , '月刊'
union all select 3 , '读者', 2004 , 5 , '月刊'
union all select 3 , '读者', 2004 , 6 , '月刊'select *
from
(
select N.书号,N.书名,N.年份,T.期数,N.刊种
from
(
select distinct 书号,书名,年份,刊种,0 as 关联
from qikan
) N,
(select 0 as 关联,1 as 期数
union all
select 0 as 关联,2 as 期数
union all
select 0 as 关联,3 as 期数
union all
select 0 as 关联,4 as 期数
union all
select 0 as 关联,5 as 期数
union all
select 0 as 关联,6 as 期数
) T
where T.关联=N.关联
) TmpA
where 期数 not in (select 期数 from qikan where qikan.书号=TmpA.书号)
declare @t table(序号 int ,字母 varchar(10))
insert into @t select 1,'a'
union all select 2,'b'
union all select 3,'c'
union all select 5,'d'
union all select 6,'e'
union all select 9,'f'
--更新
update @t set 序号=(select sum(1) from @t where 字母<=a.字母) from @t a
--查询结果
select * from @t
--结果
序号 字母
----------- ----------
1 a
2 b
3 c
4 d
5 e
6 f(所影响的行数为 6 行)
create table test2(id int,a varchar(10))insert into test2(id,a) select 1,'a';
insert into test2(id,a) select 2,'b';
insert into test2(id,a) select 3,'c';
insert into test2(id,a) select 4,'d';
insert into test2(id,a) select 5,'e';select * from test2结果:
id a
1 a
2 b
3 c
4 d
5 edelete test2 where id = 2
select * from test2结果:
id a
1 a
3 c
4 d
5 ecreate table #t(id int identity(1,1) primary key clustered,a int)
insert into #t(a) select id from test2 order by id
update test2 set id = #t.id from #t where test2.id = #t.aselect * from test2结果:
id a
1 a
2 c
3 d
4 e