减去两列的计数

Question

我有下表

Table A
UploadDate  Destroy
12/23/2020   1
12/31/2025   0
11/11/2020   1
12/16/2021   1

我大概有1000排像这样。我需要写一个SQL语句，其中计数(UploadDate)- destroy=1的破坏计数所以在上面的情况下，它将是4-3。我希望查询返回1。贝德洛是create语句：

CREATE TABLE [dbo].[Table_A](
    [UploadDate] [datetime] NULL,
    [Destroyed] [bit] NULL
) ON [PRIMARY]
GO

下面是一些示例数据的insert语句：

INSERT INTO [dbo].[Table_A]
           ([UploadDate]
           ,[Destroyed])
     VALUES
           ('12/23/2020'
           ,1)



INSERT INTO [dbo].[Table_A]
           ([UploadDate]
           ,[Destroyed])
     VALUES
           ('12/22/2020'
           ,0)

INSERT INTO [dbo].[Table_A]
           ([UploadDate]
           ,[Destroyed])
     VALUES
           ('12/31/2020'
           ,0)
        

INSERT INTO [dbo].[Table_A]
           ([UploadDate]
           ,[Destroyed])
     VALUES
           ('11/11/2020'
           ,1)


         INSERT INTO [dbo].[Table_A]
           ([UploadDate]
           ,[Destroyed])
     VALUES
           ('12/16/2021'
           ,1)

到目前为止，这就是我所拥有的：

select count(uploadDate) - count(Destroyed)
from document
where destroyed=1

Answer 1

您可以使用COUNT(CASE WHEN destroyed = 1 THEN 1 END)查找destroyed = 1所在的行数。

SELECT COUNT(*) - 
       COUNT(CASE WHEN destroyed = 1 THEN 1 
END) AS RES
FROM Table_A

请参阅来自db<>fiddle的演示。