使用having子句tsql细化数据元素

Question

我试图拉一个数据集，只有当有两个qualifier存在时才返回记录。我尝试过左连接，在临时表中填充数据，然后操作一些东西，然后是许多having子句(导致子查询选择和其他组)。如果您能帮助我进一步做些什么，我将不胜感激。

查询:

Select E,  CASE WHEN QUALIFER = '1' THEN 'NAME1' WHEN QUALIFER = '2' then 'NAME2' ELSE 'FINALNAME' END AS TYPE, count(rt.ID) 'Number '
from TABLE_ONE  co (nolock) 
join TABLE_TWO rt (nolock) 
on co.ID = rt.ID
where co.E in (select * from #tempEmail)
AND convert(date,co.INSERTED_TIMESTAMP)between '1/1/2020' and '8/15/2020'
AND TRANS_STATUS = 'APPROVED'
group by E, QUALIFER
order by E, QUALIFER

当前记录集:

E                       TYPE    Number
FAKEEMAIL1@Gmail        NAME1   1
FAKEEMAIL1@Gmail        NAME2   1
[e-mail]   Name1   21

理想结果集:

E                   TYPE    Number
FAKEEMAIL1@Gmail    NAME1   1
FAKEEMAIL1@Gmail    NAME2   1

谢谢。

Answer 1

让我们尝试下面的查询。我使用临时表使事情对我的头脑更简单。

if object_id('tempdb.dbo.#email') is not null drop table #email
create table #email
(
email varchar(50),
typeValue varchar(15),
Number int
)

insert into #email(email, typeValue, Number)
Select 
E,  
CASE WHEN QUALIFER = '1' THEN 'NAME1' WHEN QUALIFER = '2' then 'NAME2' ELSE 'FINALNAME' END AS TYPE, 
count(rt.ID) 'Number '
from TABLE_ONE  co (nolock) 
join TABLE_TWO rt (nolock) 
on co.ID = rt.ID
where co.E in (select * from #tempEmail)
AND convert(date,co.INSERTED_TIMESTAMP)between '1/1/2020' and '8/15/2020'
AND TRANS_STATUS = 'APPROVED'
group by E, QUALIFER

select a.email, a.typeValue
from #email a
inner join
(
select email, typeValue, rank() over (partition by email order by typeValue) as typeCount
from #email t
) as b
on b.email = a.email
and b.typeCount > 1