create table a
(
书号 int,
书名 varchar(10),
年份 int,
期数 int,
刊种 varchar(10)
)insert a 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 1 , '电脑', 2004 , 6 , '月刊'
union all select 3 , '读者', 2004 , 1 , '月刊'
union all select 3 , '读者', 2004 , 3 , '月刊'
union all select 3 , '读者', 2004 , 4 , '月刊'
union all select 3 , '读者', 2004 , 5 , '月刊'
union all select 3 , '读者', 2004 , 6 , '月刊'select c.书名,c.书号,c.书名,c.年份,c.刊种,[期数]=c.期数+1
from (
select * from a ) c,(select 书号,书名,年份,[期数]=max(期数),刊种 from a group by 书号,书名,年份,刊种) b
where c.书号=b.书号 and c.书名=b.书名 and c.年份=b.年份 and c.刊种=b.刊种 and c.期数<b.期数
and not exists(
select * from a where 书名=c.书名 and 书号=c.书号 and c.年份=b.年份 and c.刊种=b.刊种 and 期数=c.期数+1)
(
书号 int,
书名 varchar(10),
年份 int,
期数 int,
刊种 varchar(10)
)insert a 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 1 , '电脑', 2004 , 6 , '月刊'
union all select 3 , '读者', 2004 , 1 , '月刊'
union all select 3 , '读者', 2004 , 3 , '月刊'
union all select 3 , '读者', 2004 , 4 , '月刊'
union all select 3 , '读者', 2004 , 5 , '月刊'
union all select 3 , '读者', 2004 , 6 , '月刊'select c.书名,c.书号,c.书名,c.年份,c.刊种,[期数]=c.期数+1
from (
select * from a ) c,(select 书号,书名,年份,[期数]=max(期数),刊种 from a group by 书号,书名,年份,刊种) b
where c.书号=b.书号 and c.书名=b.书名 and c.年份=b.年份 and c.刊种=b.刊种 and c.期数<b.期数
and not exists(
select * from a where 书名=c.书名 and 书号=c.书号 and c.年份=b.年份 and c.刊种=b.刊种 and 期数=c.期数+1)
from (
select * from a
union all
select distinct 书号,书名,年份,0,刊种 from a
) c,(select 书号,书名,年份,[期数]=max(期数),刊种 from a group by 书号,书名,年份,刊种) b
where c.书号=b.书号 and c.书名=b.书名 and c.年份=b.年份 and c.刊种=b.刊种 and c.期数<b.期数
and not exists(
select * from a where 书名=c.书名 and 书号=c.书号 and c.年份=b.年份 and c.刊种=b.刊种 and 期数=c.期数+1)
insert into #test 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 1,'電腦','2004',6,'月刊' union all
select 3,'讀者','2004',1,'月刊' union all
select 3,'讀者','2004',3,'月刊' union all
select 3,'讀者','2004',4,'月刊' union all
select 3,'讀者','2004',5,'月刊' union all
select 3,'讀者','2004',6,'月刊'
select '書號'=T1.bno,'書名'=T1.bname,'年份'=T1.yy,'期数'=T1.sn -1 ,'刊種'=T1.typ from #test T1
where not exists(select 1 from #test T2 where (T1.sn - 1 = T2.sn ) and T1.bno = T2.bno)
and T1.sn - 1 > 0 /*
書號 書名 年份 期数 刊種
----------- ---------- ---- ----------- ----------
1 電腦 2004 4 月刊
3 讀者 2004 2 月刊(2 row(s) affected)*/
from
(
select *,
(select min(期数) from qikan where qikan.期数>A.期数 and qikan.书号=A.书号) as 下一个期数
from
qikan A
) T
where 下一个期数 is not null and (下一个期数-期数)>=2------------------------------------------------------电脑 2004 4 月刊 3
读者 2004 2 月刊 1
(
书号 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.书号)-------------------------------------------------电脑 2004 4 月刊 1
电脑 2004 6 月刊 3
读者 2004 2 月刊 3
读者 2004 3 月刊 1
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.书号)
--------------------------
select a.[期数],b.[书号],c.[年份]
from
(select 1 as [期数]
union
select 2 as [期数]
union
select 3 as [期数]
union
select 4 as [期数]
union
select 5 as [期数]
union
select 6 as [期数]) a,
(select distinct [书号] from ) b,
(select distinct [年份] from ) c
--------------------------然后再从表中查找出应当有,却又没有的记录:
--------------------------
select
a.[书号],
(select distinct [书名] from [表A] where [书号]=a.[书号]) as [书名],
a.[年份],
a.[期数],
(select distinct [刊种] from [表A] where [书号]=a.[书号]) as [书名]
from
(
select a.[期数],b.[书号],c.[年份]
from
(select 1 as [期数]
union
select 2 as [期数]
union
select 3 as [期数]
union
select 4 as [期数]
union
select 5 as [期数]
union
select 6 as [期数]
) a,
(select distinct [书号] from ) b,
(select distinct [年份] from ) c
) a
where not exists (select *
from [表A]
where [书号]=a.[书号] and [期数]=a.[期数] and [年份]=a.[年份]
)
--------------------------
更正为:
(select distinct [刊种] from [表A] where [书号]=a.[书号]) as [刊种]
其实思路就是先,选出应该有的,然后用not in ,就是差的
叫做:满集合数据的差还有那些:
行变列,
分组排名,
分组排序,
列变行
(
书号 int,
书名 varchar(10),
年份 int,
期数 int,
刊种 varchar(10)
)insert into #T select 1,'电脑',2004,1,'月刊'
insert into #T select 1,'电脑',2004,2,'月刊'
insert into #T select 1,'电脑',2004,3,'月刊'
insert into #T select 1,'电脑',2004,5,'月刊'
insert into #T select 1,'电脑',2004,6,'月刊'
insert into #T select 3,'读者',2004,1,'月刊'
insert into #T select 3,'读者',2004,3,'月刊'
insert into #T select 3,'读者',2004,4,'月刊'
insert into #T select 3,'读者',2004,5,'月刊'
insert into #T select 3,'读者',2004,6,'月刊'select
a.*,b.*
from
(select distinct 书号,书名,刊种 from #T) a
cross join
(select distinct 年份,期数 from #T) b
left join
#T c
on
a.书号=c.书号 and a.书名=c.书名 and b.年份=c.年份 and b.期数=c.期数
where
c.书号 is null
(
书号 int,
书名 varchar(10),
年份 int,
期数 int,
刊种 varchar(10)
)insert a 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 1 , '电脑', 2004 , 6 , '月刊'
[union] all select 3 , '读者', 2004 , 1 , '月刊'
[union] all select 3 , '读者', 2004 , 3 , '月刊'
[union] all select 3 , '读者', 2004 , 4 , '月刊'
[union] all select 3 , '读者', 2004 , 5 , '月刊'
[union] all select 3 , '读者', 2004 , 6 , '月刊'/*如果缺1个号码*/
select 书号,书名,年份,(max(期数)+max(期数)/max(期数))*max(期数)/2-sum(期数) as 期数,刊种 from a
group by 书号,书名,年份,刊种
drop table a书号 书名 年份 期数 刊种
----------- ---------- ----------- ----------- ----------
1 电脑 2004 4 月刊
3 读者 2004 2 月刊(所影响的行数为 2 行)
就是否 select 0 as 关联,1 as 期数 一直到 select 0 as 关联,52 as 期数
这样连带月刊也产生了52期?
数据多的话要查很久吧?
RETURNS @tb table(关联 int,期数 int)
BEGIN
declare @i int
set @i=1
while @i<=@QiCount
begin
insert into @tb select 0,@i
set @i=@i+1
end
return
END--//--6期
select * from dbo.FunGetAllQiSu(6)--//--12期
select * from dbo.FunGetAllQiSu(12)--//--54期
select * from dbo.FunGetAllQiSu(52)
from
(
select N.书号,N.书名,N.年份,T.期数,N.刊种
from
(
select distinct 书号,书名,年份,刊种,0 as 关联
from qikan
) N,
(select * from dbo.FunGetAllQiSu(6) --//声称六期的数据
) T
where T.关联=N.关联
) TmpA
where 期数 not in (select 期数 from qikan where qikan.书号=TmpA.书号)
(
书号 int,
书名 varchar(20),
年份 int,
期数 int,
刊种 varchar(20)
)insert a select 1, '电脑', 2004 , 1 , '月刊'
[union] all select 1 , '电脑', 2004 , 2 , '月刊'
[union] all select 1 , '电脑', 2004 , 3 , '月刊'
[union] all select 1 , '电脑', 2004 , 6 , '月刊'
[union] all select 2 , '电脑', 2004 , 1 , '周刊'
[union] all select 2 , '电脑', 2004 , 4 , '周刊'
[union] all select 2 , '电脑', 2004 , 5 , '周刊'
[union] all select 2 , '电脑', 2004 , 9 , '周刊'
[union] all select 2 , '电脑', 2004 , 12 , '周刊'
[union] all select 3 , '读者', 2004 , 3 , '月刊'
[union] all select 3 , '读者', 2004 , 6 , '月刊'
[union] all select 4 , '美女', 2004 , 1 , '周刊'
[union] all select 4 , '美女', 2004 , 5 , '周刊'
[union] all select 4 , '美女', 2004 , 8 , '周刊'
[union] all select 4 , '美女', 2004 , 11 , '周刊'
gocreate function f_mun(@c1 varchar(30),@c2 int,@c3 varchar(30))
returns varchar(2000)
as
begin
declare @i int,@m varchar(2000)
declare @b1 table([id] int)
declare @re table(书号 int,书名 varchar(20),年份 int,期数 int,刊种 varchar(20))
select @i=max(期数) from a where 书名=@c1 and 年份=@c2 and 刊种=@c3while(@i>0)
begin
insert @b1 values (@i)
set @i=@i-1
end
insert @re
select distinct 书号,书名,年份,b.[id] as 期数,刊种 from a,@b1 b where 书名=@c1 and 年份=@c2 and 刊种=@c3
delete from @re where 期数 in (select 期数 from a where 书名=@c1 and 年份=@c2 and 刊种=@c3)
select @m=''
select @m=@m+','+cast(期数 as varchar) from @re
return(stuff(@m,1,1,''))
end
go
select 书号,书名,年份,dbo.f_mun(书名,年份,刊种) as qs,刊种 into #1 from a group by 书号,书名,年份,刊种select top 50 id=identity(int,1,1) into #
from syscolumns a,syscolumns bselect 书号,书名,年份,期数=cast(substring(a.qs,b.[id],charindex(',',a.qs+',',b.id)-b.id) as varchar(10)),刊种
from #1 a,# b
where b.[id]<=len(a.qs)
and charindex(',',','+a.qs,b.id)=b.id
drop table #
drop table #1
drop function dbo.f_mun
drop table a书号 书名 年份 期数 刊种
----------- -------------------- ----------- ---------- --------------------
1 电脑 2004 4 月刊
1 电脑 2004 5 月刊
2 电脑 2004 2 周刊
2 电脑 2004 3 周刊
2 电脑 2004 6 周刊
2 电脑 2004 7 周刊
2 电脑 2004 8 周刊
2 电脑 2004 10 周刊
2 电脑 2004 11 周刊
3 读者 2004 1 月刊
3 读者 2004 2 月刊
3 读者 2004 4 月刊
3 读者 2004 5 月刊
4 美女 2004 2 周刊
4 美女 2004 3 周刊
4 美女 2004 4 周刊
4 美女 2004 6 周刊
4 美女 2004 7 周刊
4 美女 2004 9 周刊
4 美女 2004 10 周刊(所影响的行数为 20 行)因为我看到楼主需要分别周刊和月刊,所以写了个函数和过程来完成这个问题,稍微有点复杂了!但是应该能够很明确的找到缺号。
(select 1 as [期数]
union
select 2 as [期数]
union
....
就行了!!而且在函数中尽量不去进行复杂的多表查询 ,这样会影响执行性能
from
(
select N.书号,N.书名,N.年份,T.期数,N.刊种
from
(
select distinct 书号,书名,年份,刊种,0 as 关联
from qikan
where 刊种='月刊'
) N,
(select * from dbo.FunGetAllQiSu(6) --//声称月刊半年六期的数据
) T
where T.关联=N.关联
union all select N.书号,N.书名,N.年份,T.期数,N.刊种
from
(
select distinct 书号,书名,年份,刊种,0 as 关联
from qikan
where 刊种='周刊'
) N,
(select * from dbo.FunGetAllQiSu(52) --//声称周刊一年52期的数据
) T
where T.关联=N.关联 ) TmpA
where 期数 not in (select 期数 from qikan where qikan.书号=TmpA.书号)
就是按照沙子兄的查询方法
对年份其实是不能查询清楚的
比如
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 , '月刊'
union all select 3 , '读者', 2005 , 6 , '月刊'2005年只来了第6期
但查询结果却是1 电脑 2004 4 半月刊
1 电脑 2004 6 半月刊
3 读者 2004 2 月刊
3 读者 2004 3 月刊
3 读者 2005 2 月刊
3 读者 2005 3 月刊
create table t
(
书号 int,
书名 varchar(10),
年份 int,
期数 int,
刊种 varchar(10)
)insert t 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 1 , '电脑', 2004 , 6 , '月刊'
union all select 3 , '读者', 2004 , 1 , '月刊'
union all select 3 , '读者', 2004 , 3 , '月刊'
union all select 3 , '读者', 2004 , 4 , '月刊'
union all select 3 , '读者', 2004 , 5 , '月刊'
union all select 3 , '读者', 2004 , 6 , '月刊'create table t1
(
书号 int,
书名 varchar(10),
年份 int,
期数 int,
刊种 varchar(10)
)----用游标测试
declare
@a int,
@b varchar(10),
@d int,
@e varchar(10),
@i int
declare xy cursor for
select distinct 书号,书名,年份,刊种
from topen xy
fetch next from xy
into @a,@b,@d,@ewhile (@@fetch_status=0)
begin
set @i=1
while(@i<6)
begin
if( @i not in (select 期数 from t where 书号=@a))
insert into t1 select @a,@b,@d,@i,@e
set @i=@i+1
end
fetch next from xy
into @a,@b,@d,@e
endclose xy
deallocate xyselect *
from t1drop table t
drop table t1
用游标解决问题