T-SQL:有条件的无效去除

Question

我只需要选择没有 Status为null的Room_IDs。

例如：

table_a

  Room_Id   Status    Inspection_Date
  -----------------------------------
    1        NULL        5/15/2015
    2        occupied    5/21/2015
    2        NULL        1/19/2016
    1        occupied   12/16/2015
    4        NULL        3/25/2016
    3        vacant      8/27/2015
    1        vacant      4/17/2016
    3        vacant     12/12/2015
    3        vacant      3/22/2016
    4        vacant       2/2/2015
    4        vacant      3/24/2015

我的结果应该像这样：

  Room_Id  Status  Inspection_Date
  -----------------------------------
    3       vacant      8/27/2015
    3       vacant     12/12/2015
    3       vacant      3/22/2016

因为Room_ID'3'没有Status为null

Answer 1

如何做的快速示例：

DECLARE @tTable TABLE(
Room_Id INT,
Status VARCHAR(20),
Inspection_Date DATETIME)
INSERT INTO @tTable VALUES 
 (1, NULL, '5/15/2015'),
 (1,NULL,        '5/15/2015'),
 (2,'occupied',  '5/21/2015'),
 (2,NULL,        '1/19/2016'),
 (1,'occupied',  '12/16/2015'),
 (4,NULL,        '3/25/2016'),
 (3,'vacant',    '8/27/2015'),
 (1,'vacant',    '4/17/2016'),
 (3,'vacant',    '12/12/2015'),
 (3,'vacant',    '3/22/2016'),
 (4,'vacant',    '2/2/2015'),
 (4,'vacant',    '3/24/2015')
 SELECT * FROM @tTable T1
  WHERE Room_Id NOT IN (SELECT Room_ID FROM @tTable WHERE Status IS NULL)

给出：

Room_Id |   Status |    Inspection_Date         |
-------------------------------------------------
3       |   vacant |    2015-08-27 00:00:00.000
3       |   vacant |    2015-12-12 00:00:00.000
3       |   vacant |    2016-03-22 00:00:00.000

Answer 2

尝试一下：

 SELECT *
 FROM Table1 
 WHERE Room_ID NOT IN 
 (
   SELECT DISTINCT Room_ID
   FROM Table1
   WHERE Status IS NULL
   )

子查询返回唯一的房间ID列表，该列表一次或另一次具有无效状态。外部查询查看该列表，并说"返回 * room_id不是子查询中的一个。

如果您想在SQL小提琴中尝试，则是架构：

CREATE TABLE Table1
    (Room_ID int, Status varchar(8), Inspection_Date datetime)
;
INSERT INTO Table1
    (Room_ID, Status, Inspection_Date)
VALUES
    (1, NULL, '2015-05-15 00:00:00'),
    (2, 'occupied', '2015-05-21 00:00:00'),
    (2, NULL, '2016-01-19 00:00:00'),
    (1, 'occupied', '2015-12-16 00:00:00'),
    (4, NULL, '2016-03-25 00:00:00'),
    (4, 'vacant', '2015-08-27 00:00:00'),
    (1, 'vacant', '2016-04-17 00:00:00'),
    (3, 'vacant', '2015-12-12 00:00:00'),
    (3, 'vacant', '2016-03-22 00:00:00'),
    (4, 'vacant', '2015-02-02 00:00:00'),
    (4, 'vacant', '2015-03-24 00:00:00'),
    (2, NULL, '2015-05-22 00:00:00')
;

Answer 3

作为Hashman的替代方案，我只想在not in上使用not exists进行这些类型的查询。

创建一些测试数据

请注意，我只是保留了所有事物的日期，因为这不是问题。

create table #table_a (
    Room_Id int,
    Status varchar(32),
    Inspection_Date date);
insert #table_a (Room_Id, Status, Inspection_Date)
    values
        (1, null, getdate()),
        (2, 'occupied', getdate()),
        (2, null, getdate()),
        (1, 'occupied', getdate()),
        (4, null, getdate()),
        (3, 'vacant', getdate()),
        (1, 'vacant', getdate()),
        (3, 'vacant', getdate()),
        (3, 'vacant', getdate()),
        (4, 'vacant', getdate()),
        (4, 'vacant', getdate());

查询

select *
from #table_a t1
where not exists (
    select *
    from #table_a t2
    where t1.Room_Id = t2.Room_Id
        and Status is null);

结果

Room_Id     Status                           Inspection_Date
----------- -------------------------------- ---------------
3           vacant                           2016-06-17
3           vacant                           2016-06-17
3           vacant                           2016-06-17

Answer 4

您可以使用CTE，而不存在如下代码

WITH    bt
      AS ( SELECT   RoomId ,
                    Status,
                    Inspection_Date
           FROM     dbo.Table_1
         )
SELECT  *
FROM    bt AS a
WHERE   NOT EXISTS ( SELECT 1
                     FROM   bt
                     WHERE  bt.RoomId = a.RoomId
                            AND bt.Status IS NULL );