一行返回值，其中一行中缺少另一个范围中的另一个值

Question

我有一个表：

BookType    Version
A           2011
A           2012
A           2013
B           2011
B           2012
B           2013
B           2014
C           2011
C           2012
C           2014
D           2011
D           2012
D           2013
D           2014

我想选择2011年至2014年每年没有版本的基本类型。因此，它将返回：

A
C

我该怎么做？

Answer 1

您可以使用

SELECT BookType
FROM BookTypes
EXCEPT
SELECT BookType
FROM YourTable
WHERE Version BETWEEN 2011 AND 2014
GROUP BY BookType
HAVING COUNT(DISTINCT Version) = 4

Answer 2

假设没有由BookType和Version组成的唯一键或主键，使用NOT EXISTS的查询有点长，但看起来像：

SELECT DISTINCT t.BookType
FROM   MyTable t
WHERE  NOT EXISTS (SELECT 1 FROM MyTable a
                   WHERE  a.BookType = t.BookType
                   AND    a.Version = 2011)
OR     NOT EXISTS (SELECT 1 FROM MyTable a
                   WHERE  a.BookType = t.BookType
                   AND    a.Version = 2012)
OR     NOT EXISTS...

处理问题的另一种方法可能是创建表(可能正在使用通用表格(并使用EXCEPT：

;WITH Versions AS
(
    SELECT 2011 AS Version
    UNION ALL
    SELECT 2012
    UNION ALL
    SELECT 2013
    UNION ALL
    SELECT 2014
),
Missing AS
(
    SELECT t.BookType, v.Version
    FROM   MyTable t, Versions v
    EXCEPT
    SELECT t.BookType, t.Version
    FROM   MyTable t
)
SELECT DISTINCT m.BookType
FROM   Missing m

Answer 3

我假设桌子名称在这里。这样的查询可能会有所帮助。

SELECT BookType, 
count(*) FROM BOOKS 
where Version >= 2011 AND Version <= 2014 
GROUP BY 1 HAVING count(*) < 4;

我希望这会有所帮助！

Answer 4

这应该与所有示例数据一起使用。另外，如果两年之间都缺少一年，那么它也有效。

    declare @t table(BookType varchar(50),Versions int)
insert into @t VALUES
('A',2011),('A',2012),('A',2013),('B',2011)
,('B',2012),('B',2013),('B',2014),('C',2011),('C',2015)
,('C',2012),('C',2014),('D',2011),('D',2012)
,('D',2013),('D',2014)
declare @startyear int=2011
declare @tillyear int=2014
;With cte1 AS(
select * 
,(Versions-@startyear) rn2
,ROW_NUMBER()over(partition by booktype order by versions)rn
from @t
)
select booktype from cte1 c1
where not exists(
select c.booktype
from cte1 c
where c.booktype=c1.booktype
group by c.booktype
having max(rn2)=(@tillyear-@startyear)
and min(rn2)=0)
and rn=1