如何在 SQL 中按块进行分组? [英] How to do group by chunk wise in SQL?

问题描述

我有下表，My_Table，结构如下，

I've following table, My_Table, with structure as follows,

Name   | Address
----------------
Test1  | abc
Test1  | abc
Test2  | abc
Test1  | xyz
Test2  | abc
Test3  | abc
Test4  | tyu

我需要如下输出，

Name   | Address
----------------
Test2  | abc
Test3  | abc

让我解释一下输出.输出是按块对数据进行分组，如果第二列的所有分组数据基于 Name 相同且等于 abc，则选择该行.

Let me explain you the output. The output is grouping data in chunks and if second column has all the grouped data based on Name is same and it is equal to abc then select that row.

详细说明:Test1 有 2 个不同的值，其中一个不等于 abc，因此它不在输出中.Test2 和 Test3 有一个不同的值，它等于 abc，因此在输出中.Test4 有一个不同但不等于 abc，因此不在输出中.

Explanation in detail: Test1 has 2 distinct values and one of them is not equal to abc, hence it is not in output. Test2 and Test3 has one distinct value and it is equal to abc, hence in output. Test4 has one distinct but it is not equal to abc, hence not in output.

我尝试过的，

all = select distinct(Name) from My_Table
invalid = select distinct(Name) from My_Table where Address != 'abc'

现在，所需的输出是all - invalid(减去结果集).我使用 2 个查询实现了这一点.这可以在一个查询中实现吗?如果是，如何?

Now, desired output is all - invalid (subtracting result set). I achieved this using 2 queries. can this be achieved in one query? If yes, how?

注意:我使用 JAVA 查询 MYSQL DB，因此我可以减去结果集.我的表很大，因此我想优化查询数量！

Note: I'm using JAVA for query MYSQL DB, hence I can subtract result sets. My table size is huge, hence I want to optimize number of queries!

推荐答案

select name, max(address)
from tablename
group by name
having max(address) = min(address)
   and max(address) = 'abc'

这篇关于如何在 SQL 中按块进行分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！