当全部为零时排除多个和行

Question

如果所有3个和列都为零，我想在查询结果中排除一行。

Select Name
,sum(case when cast(Date as date) <= Convert(datetime, '2014-05-01') then 1 else 0 end) as 'First'
,sum(case when cast(Date as date) <= Convert(datetime, '2014-04-01') then 1 else 0 end) as 'Second'
,sum(case when cast(Date as date) <= Convert(datetime, '2013-05-01') then 1 else 0 end) as 'Third'
FROM [dbo].[Posting]
inner join dbo.Names on Name.NameId = Posting.NameId
where active = 1 
group by Name
order by Name

Answer 1

这可能对您有用：

select * from
( 
.......your query......
) as t
where First <> 0 or Second <> 0 or Third <> 0

Answer 2

您可以使用以下查询：

with temp as 
(Select Name
,sum(case when cast(Date as date) <= Convert(datetime, '2014-05-01') then 1 else 0 end) as 'First'
,sum(case when cast(Date as date) <= Convert(datetime, '2014-04-01') then 1 else 0 end) as 'Second'
,sum(case when cast(Date as date) <= Convert(datetime, '2013-05-01') then 1 else 0 end) as 'Third'
FROM [dbo].[Posting]
inner join dbo.Names on Name.NameId = Posting.NameId
where active = 1 
group by Name
order by Name)
select * from temp where [first]+[second]+[third]=0

Answer 3

您可以重复having子句中的表达式：

having sum(case when cast(Date as date) <= Convert(datetime, '2014-05-01') then 1 else 0 end) > 0 or
       sum(case when cast(Date as date) <= Convert(datetime, '2014-04-01') then 1 else 0 end) > 0 or
       sum(case when cast(Date as date) <= Convert(datetime, '2013-05-01') then 1 else 0 end) > 0

然而，您可以更简单地将条件写为：

having sum(case when cast(Date as date) <= '2014-05-01' then 1 else 0 end) > 0 or
       sum(case when cast(Date as date) <= '2014-04-01' then 1 else 0 end) > 0 or
       sum(case when cast(Date as date) <= '2013-05-01' then 1 else 0 end) > 0

或者，因为第一个包含了另外两个：

having sum(case when cast(Date as date) <= '2014-05-01' then 1 else 0 end) > 0

或者，更简单地说：

having min(date) <= '2014-05-01'

此外，您应该只对字符串和日期名称使用单引号。列别名不要使用单引号（这可能会导致混淆和问题）。选择不需要转义的名称。如果的有一个麻烦的名称，请使用方括号。

Answer 4

您不需要Convert date到datetime。

cast(Date as date)将列日期值转换为yyyy-MM-dd格式的

with CTE
as
(select 
sum(case when cast(Date as date) <=  '2014-05-01' then 1 else 0 end) as 'First'
,sum(case when cast(Date as date) <=  '2014-04-01' then 1 else 0 end) as 'Second'
,sum(case when cast(Date as date) <=  '2013-05-01' then 1 else 0 end) as 'Third' from myTable
)
where CTE.First=0 and CTE.Second=0 and CTE.Third=0