[求助]如何查出表中缺少的记录？

Question

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)

Answer 1

select c.书号,c.书名,c.年份,c.刊种,[期数]=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)

Answer 2

create table #test (bno int,bname varchar(10),yy char(4),sn int ,typ varchar(10))
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)*/

Answer 3

select 书号,书名,年份,期数+1 as 期数,刊种 
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

Answer 4

--//哈哈！！我这个能查所以差了的哈1！！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.书号)-------------------------------------------------电脑 2004 4 月刊 1
电脑 2004 6 月刊 3
读者 2004 2 月刊 3
读者 2004 3 月刊 1

Answer 5

这样看的明白点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.书号)

Answer 6

之所以判定为缺少某一期，其实这总期数是已知的假定这是一年的杂志，而每两月都有一期，那么每年都是六期。使用交叉查询，查出所有应当存在的杂志及其期数:
--------------------------
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.[年份]
                 )
--------------------------

Answer 7

(select distinct [刊种] from [表A] where [书号]=a.[书号]) as [书名]
更正为:
(select distinct [刊种] from [表A] where [书号]=a.[书号]) as [刊种]

Answer 8

就是哈！！
其实思路就是先，选出应该有的，然后用not in ，就是差的

Answer 9

我觉得这个可以归入Sql统计分析的一个专题
叫做：满集合数据的差还有那些：
 
 行变列，
 分组排名，
 分组排序，
 列变行

Answer 10

create table #T
(
 书号 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

Answer 11

>libin_ftsafe(子陌红尘) select distinct 年份,期数 from #T) b如果#T中没有出现期数6或者5，这样都查不出差6期或者5期了这样就和lz的要求不符！

Answer 12

呵呵！先想了一种用公式求只有一个补号的时候的情景！至于要缺一号以上的嘛！我想可以通过字符比较的方式也许能较好的解决这个问题，比如把最大数字求到，然后得到一个1,2,……,n的集合，然后收集存在的1，2，3……，然后用substring和charindex语句循环摸去存在的，剩下不存在的，然后再分解。以上是我一点愚见！:([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   ,      '月刊'/*如果缺1个号码*/
select 书号,书名,年份,(max(期数)+max(期数)/max(期数))*max(期数)/2-sum(期数) as 期数,刊种  from a 
group by 书号,书名,年份,刊种
drop table a书号          书名         年份          期数          刊种         
----------- ---------- ----------- ----------- ---------- 
1           电脑         2004        4           月刊
3           读者         2004        2           月刊（所影响的行数为 2 行）

Answer 13

如果刊种是周刊的话
就是否   select 0 as 关联,1 as 期数 一直到    select 0 as 关联,52 as 期数
这样连带月刊也产生了52期？
数据多的话要查很久吧？

Answer 14

如果刊种是周刊的话这个Easy的很!!定义一个函数返回一个表!!CREATE FUNCTION FunGetAllQiSu(@QiCount int = 1)
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)

Answer 15

然后查询就写成这样select * 
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.书号)

Answer 16

[create] table a
(
 书号 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 行）因为我看到楼主需要分别周刊和月刊，所以写了个函数和过程来完成这个问题，稍微有点复杂了！但是应该能够很明确的找到缺号。

Answer 17

风云的函数怎么这么复扎??我觉得函数能够实现动态的 
               (select 1 as [期数]
               union 
               select 2 as [期数]
               union
               ....
 就行了!!而且在函数中尽量不去进行复杂的多表查询 ,这样会影响执行性能 

Answer 18

如果周刊和月刊混在一起这样解决也非常方便！！select * 
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.书号)

Answer 19

not in 的确是效率不高哈!!不过这问题不用Not in 还能用什么啦??

Answer 20

发现一个新问题
就是按照沙子兄的查询方法
对年份其实是不能查询清楚的
比如
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 月刊

Answer 21

跨年的话,就要加上年的条件!!你吧那个Sql的原理弄清楚了1!自己改一下应该没有什么问题的1!!

Answer 22

对了，zbyy() 你一万条执行时间大概是多久啊？

Answer 23

---测试数据
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
用游标解决问题