选择未设置标志的所有记录

Question

如何找到未将bookingNo标志设置为0的所有hasPaid？

不能让它起作用。我应该在HasPaid标志未设置为1的情况下预订2-4-8。

非常感谢

    if object_id('tempdb..#testFlag') is not null
        drop table #testFlag

    create table #testFlag (BookingNo int,  HasPaid bit)


    insert into #testFlag (BookingNo, HasPaid)
      select 1, 0 union ALL
      select 1, 1 union ALL
      select 2, 0 union ALL
      select 3, 0 union ALL
      select 3, 1 union ALL
      select 3, 0 union ALL
      select 4, 0 union ALL
      select 4, 0 union ALL
      select 4, 0 union ALL  
      select 5, 0 union ALL
      select 5, 1 union ALL
      select 6, 0 union ALL
      select 6, 1 union ALL
      select 7, 0 union ALL
      select 7, 1 union ALL
      select 8, 0 

Answer 1

您可以将GROUP BY与基于CASE的HAVING一起使用，如下所示。

查询

  SELECT BookingNo FROM #testFlag
  GROUP BY BookingNo
  HAVING SUM(CASE WHEN HasPaid = 1 THEN 1 ELSE 0 END) = 0

输出

BookingNo
2
4
8

Answer 2

select BookingNo 
  from #testFlag  
except
select BookingNo 
  from #testFlag 
 where HasPaid = 1

例外

如果您想在那时使用组

SELECT BookingNo FROM #testFlag
 GROUP BY BookingNo
HAVING MAX(cast(HasPaid as int)) = 0;

Answer 3

我想问题在于复制的BookingNo。如果您想选择所有BookingNo，其中HasPaid不是0，请尝试如下：

  select distinct BookingNo from #testFlag t1 where HasPaid != 0
  and not exists (select 1
  from #testFlag t2 where HasPaid = 0 and t2.BookingNo = t1.BookingNo
  )

如果您想选择不在BookingNo 1的所有HasPaid，请尝试如下：

  select distinct BookingNo from #testFlag t1 where HasPaid != 1
  and not exists (select 1
  from #testFlag t2 where HasPaid = 1 and t2.BookingNo = t1.BookingNo
  )