左加入不起作用，或者我不知道如何使用它

Question

我正在尝试生成一个带有左联接的查询。这个想法是我可以得到：

JOBA,SCRIPTA
JOBA,SCRIPTC

因为我只需要具有相同列A(job(和不同列B(bat(的行

我正在尝试左联接，但它不起作用。你能帮我为什么我的sql语句是错的吗？

create table definition
(
job varchar(256),
bat varchar(256)
);
INSERT INTO definition (job,bat)
VALUES
('JOBA','SCRIPTA'),
('JOBA','SCRIPTA'),
('JOBB','SCRIPTB'),
('JOBA','SCRIPTC'),
('JOBB','SCRIPTB');

with duplicates as 
(
select distinct a.job,a.bat from definition a where job in (
SELECT job
FROM definition
GROUP BY job
HAVING COUNT(*) > 1) order by job desc
),
duplicatetwofields as 
(
SELECT job, bat
FROM definition
GROUP BY job, bat
HAVING COUNT(*) > 1 order by job desc
) 
select a.job,a.bat from duplicates a left join  duplicatetwofields b
on (a.job=b.job)
--and a.bat=b.bat) 
where a.bat is null or a.job is null;

Answer 1

GROUPBY和COUNT()会让你达到目的，但你最初问题中的代码并不是你想要的：

SELECT job
FROM definition
GROUP BY job
HAVING COUNT(*) > 1

您将job值放入组中，然后在组计数大于1时提取该值。JOBA和JOBB都存在不止一次，因此每个job组都有不止一个成员。这意味着JOBA和JOBB都会被返回。你想要的是这样的东西：

SELECT job
FROM definition
GROUP BY job
HAVING COUNT(DISTINCT(bat)) > 1

这将job放入组中，但仅当每个job有多个不同的bat时才返回它们。此查询仅返回JOBA，因为JOBA是唯一具有1个以上不同bat值的组。

您也不需要使用第二个CTE来消除重复项。只需在最终查询中使用distinct即可获得唯一的结果。考虑到问题的参数，这样的东西应该可以工作(我使用了子查询，如果你愿意，可以使用CTE(：

SELECT DISTINCT d.*
FROM definition d
JOIN
(
SELECT job
FROM definition
GROUP BY job
HAVING COUNT(DISTINCT(bat)) > 1
) x
ON d.job = x.job

DbFiddle

EDIT:DbFiddle与ISNULL()一起处理空值。

Answer 2

您可以使用EXIST:

select distinct d.*
from definition d
where exists (
select 1 from definition
where job = d.job and bat <> d.bat
)

或者：

select distinct *
from definition
where job in (
select job
from definition
group by job
having count(distinct bat) > 1
) 

请参阅演示
结果：

> job  | bat    
> :--- | :------
> JOBA | SCRIPTA
> JOBA | SCRIPTC