如何组合CASE、INNER JOIN和GROUP BY

Question

我正在处理一个问题；我需要连接两个表，根据它们的ID进行分组，并使用CASE语句来比较这两个表的值。我一直在尝试使用一个临时表，然后从中选择。

目的是测试CORE中的值是否与MART中的值相对应。

理想情况下，我希望有一个查询，在那里我可以看到列CORE_X_MART，并可以在上面使用where语句。

分组依据是必不可少的，否则我在临时表中会有重复的ID。

我的代码：

drop table if exists #tNDWH_4034
select a.ID, b.ID, a.col2 as MART_Value, b.col2 as CORE_Value,
case when a.col2 = b.col2 then 'Match' else 'Mismatch' end as CORE_X_MART
into #tNDWH_4034
from       tab1 as a
inner join tab2 as b on a.ID = b.ID
where a.CurrentFlag = 1
group by a.ID, b.ID;

select * from #tNDWH_4034
where CORE_X_MART = 'Mismatch';

我使用的是SQL服务器。

Answer 1

您不需要临时表。您可以使用派生表来达到目的，将它们放在一个查询中。

SELECT * FROM
(select a.ID, b.ID, a.col2 as MART_Value, b.col2 as CORE_Value,
case when a.col2 = b.col2 then 'Match' else 'Mismatch' end as CORE_X_MART
from tab1 as a
inner join tab2 as b 
on a.ID = b.ID
where a.CurrentFlag = 1
group by a.ID, b.ID) as t
WHERE t.CORE_X_MART = 'Mismatch'

Answer 2

可能：

select a.ID, b.ID, a.col2 as MART_Value, b.col2 as CORE_Value,

case when a.col2 = b.col2 then 'Match' else 'Mismatch' end as CORE_X_MART

into #tNDWH_4034

from tab1 as a
inner join tab2 as b on (a.ID = b.ID)

where a.CurrentFlag = 1

group by a.ID, b.ID, a.col2, b.col2
,case when a.col2 = b.col2 then 'Match' else 'Mismatch' end --this line is probably not required

Answer 3

您也不需要临时表、分组依据或案例。您只是在查找不匹配，所以只需使用不等于运算符<>来筛选结果。

select distinct a.ID, a.col2 as MART_Value, b.col2 as CORE_Value
from       tab1 as a
inner join tab2 as b on a.ID = b.ID
where a.CurrentFlag = 1
and a.col2 <> b.col2