显示if子句是否从SQL中找到结果

Question

我在tableexample中有以下示例数据(im使用MSSQL(：

<1><1><1><1><1><1><1><1><1><1>

ID	日期	标签
15551	2021-11-10
1551	2021-11-09	0
15551	2021-11-10
12123	2021-11-09
12123	2021-11-09
15551	2021-11-10
12123	2021-11-10
74141	2021-11-10
1234	2021-11-10
111111	2021-11-10
74141	2021-11-10
1234	2021-11-10	0

select t.ID ,
(select  count(*)
from tbl t2 
where t2.ID = t.ID and 
t2.date = cast(getdate() as date) and t2.tag = 1) checkfoundentry 
from tbl t 
where t.ID in (15551,12123,12345,74141)

我使用innerquery来计算您指定的筛选器。。。

Answer 1

您可以使用window functions来计算每个ID 的标准

with x as (
select * ,
Count(*) over(partition by id) cnt, 
Sum(tag) over(partition by id) tg, 
Count(case when date !='2021-11-10' then 1 end) over(partition by id) dt
from t
where t.ID in (15551,12123,12345,74141)
)
select distinct id, 
case when dt>0 and cnt=tg then 1 else 0 end CheckFoundEntry
from x;

Answer 2

您也可以使用下面的子查询，

SELECT sq.colID,
MAX(sq.checkfoundentry) AS checkfoundentry
FROM(
SELECT t.colID
, t.coldate
, t.coltag
, CASE WHEN t.coldate <> cast(GETDATE() AS date) AND t.coltag = 1 THEN 1 ELSE 0 END AS  checkfoundentry
--, CASE WHEN ((t.coldate <> cast(getdate() AS date)) OR t.coltag)  1 else 0 end --as checkfoundentry
FROM dbo.table1 t
WHERE t.colID IN (15551,12123,12345,74141)
) AS sq
GROUP BY sq.colID