There is a table which look like this:
有一个表格如下：
I need to retrieve only highlighted records. And I need a query which should work on bigger table where are millions of records are exists.
我只需要检索突出显示的记录。我需要一个查询，它应该在更大的表上工作，其中存在数百万条记录。
Criteria:
There are 4 sets, 1st and 3rd have the similar values but 2nd and 4th sets have different values
标准：有4组，第1组和第3组具有相似的值，但第2组和第4组具有不同的值
Edit:
I made slight modification in the table( ID column added). How can we achieve the same with the ID column?
编辑：我在表中稍作修改（添加了ID列）。我们如何通过ID列实现相同的目标？
2 个解决方案
#1
3
return only this kind of set where 1 or more different value exists in the set
只返回这种集合，其中集合中存在1个或多个不同的值
create table #ab
(
col1a int,
colb char(2)
)
insert into #ab
values
(1,'a'),
(1,'a'),
(1,'a'),
(2,'b'),
(2,'c'),
(2,'c')
select id,col1a,colb
from #ab
where col1a in (
Select col1a from #ab group by col1a having count (distinct colb)>1)
Regarding the performance over millions of rows,i would probably check the execution plan and deal with it.with my sample data set and my query ,Distinct sort takes nearly 40% of cost..with millions of rows,it can probably go to tempdb as well..so i suggest below index which can eliminate more rows
关于数百万行的性能，我可能会检查执行计划并处理它。使用我的示例数据集和我的查询，Distinct sort占用了近40％的成本......有数百万行，它可能会转到tempdb以及..所以我建议下面的索引可以消除更多的行
create index nci on #ab(colb)
include(col1a)
#2
0
You can also achieve it using INNER JOIN instead of IN as it is million rows query.
您也可以使用INNER JOIN而不是IN来实现它，因为它是百万行查询。
SELECT f.colA,f.colB
FROM
filtertable f
INNER JOIN
(
SELECT colA
FROM filtertable
GROUP BY colA
HAVING COUNT(DISTINCT colB)>1
) f1
ON f.colA = f1.colA
#1
3
return only this kind of set where 1 or more different value exists in the set
只返回这种集合，其中集合中存在1个或多个不同的值
create table #ab
(
col1a int,
colb char(2)
)
insert into #ab
values
(1,'a'),
(1,'a'),
(1,'a'),
(2,'b'),
(2,'c'),
(2,'c')
select id,col1a,colb
from #ab
where col1a in (
Select col1a from #ab group by col1a having count (distinct colb)>1)
Regarding the performance over millions of rows,i would probably check the execution plan and deal with it.with my sample data set and my query ,Distinct sort takes nearly 40% of cost..with millions of rows,it can probably go to tempdb as well..so i suggest below index which can eliminate more rows
关于数百万行的性能，我可能会检查执行计划并处理它。使用我的示例数据集和我的查询，Distinct sort占用了近40％的成本......有数百万行，它可能会转到tempdb以及..所以我建议下面的索引可以消除更多的行
create index nci on #ab(colb)
include(col1a)
#2
0
You can also achieve it using INNER JOIN instead of IN as it is million rows query.
您也可以使用INNER JOIN而不是IN来实现它，因为它是百万行查询。
SELECT f.colA,f.colB
FROM
filtertable f
INNER JOIN
(
SELECT colA
FROM filtertable
GROUP BY colA
HAVING COUNT(DISTINCT colB)>1
) f1
ON f.colA = f1.colA