为列的每个不同类型提取多个相似行 [英] Extracting Multiple Similar Rows For Each Distinct Type Of A Column

问题描述

输入

+--------+------+------+
|  col1  | col2 | col3 |
+--------+------+------+
| apple  | d    |   10 |
| apple  | d    |   44 |
| apple  | e    |   55 |
| orange | d    |   99 |
| orange | c    |   33 |
| orange | d    |   10 |
| banana | e    |   55 |
| banana | d    |   10 |
+--------+------+------+

所需输出

+--------+------+------+
|  col1  | col2 | col3 |
+--------+------+------+
| apple  | d    |   10 |
| orange | d    |   10 |
| banana | d    |   10 |
+--------+------+------+

我们将检查 col2 和 col3 是否有 N 种不同类型的水果.

We will be checking col2 and col3 for N different type of fruits.

我们只想列出那些 col2 和 col3 值相同并且所有水果都存在该行的那些

We want to list only those those where col2 and col3 values are same and the row is present for all fruits

你可以这样想:-

第一步

区分所有不同类型的水果:-

Separate out all the different type of fruits:-

苹果:-

+-------+------+------+
| col1  | col2 | col3 |
+-------+------+------+
| apple | d    |   10 |
| apple | d    |   44 |
| apple | e    |   55 |
+-------+------+------+

橙色:-

+--------+------+------+
|  col1  | col2 | col3 |
+--------+------+------+
| orange | d    |   99 |
| orange | c    |   33 |
| orange | d    |   10 |
+--------+------+------+

香蕉:-

+--------+------+------+
|  col1  | col2 | col3 |
+--------+------+------+
| banana | e    |   55 |
| banana | d    |   10 |
+--------+------+------+

第 2 步:-

现在只选择那些行

• 有相同的 col2 &col3 值

和

• 它存在于所有类型的水果中.

观察:-

'apple e 55' 和 'banana e 55' 具有相同的 col2 和 col3 值，但它未被选中，因为不存在 'orange e 55'.

'apple e 55' and 'banana e 55' have same col2 and col3 values but it is not selected because 'orange e 55' is not present.

如果您使用的是临时表，请确保它应该是通用的.它应该支持N个水果.

If you are using temporary tables then please make sure that it should be generic. It should support N number of fruits.

注意:- 这不是学生 :D 的作业.我用简单的话解释它，因为它是一个漫长而冗长的查询的一部分，我对如何解决它没有任何想法.我一直在使用创建临时表的技术，但我遇到了一些问题.它不是通用的.所以，我相信这个问题可能有更好的解决方案.

Note:- This is not an assignment of a student :D . I'm explaining it in simple words because it is a part of a long and lengthy query and I've ZERO idea of how to tackle it. I've been using a technique of creating temporary tables, but I was facing some problems. It was not made generic. So, I believe there might be some better solution of this problem.

推荐答案

基本上，你可以通过这个查询获得所有水果中存在的记录，而不管name，

Basically, you can get the record that is present in all fruits regardless of name via this query,

SELECT  col2, col3
FROM    tableName
GROUP   BY col2, col3
HAVING  COUNT(*) = (SELECT COUNT(DISTINCT col1) FROM tableName)

• SQLFiddle 演示

一组两列的记录总数:col2和col3必须等于水果总数.SELECT COUNT(DISTINCT col1) FROM tableName.

The total number of record in a group of two columns: col2 and col3 must be equal to the total number of fruits. SELECT COUNT(DISTINCT col1) FROM tableName.

所以要获得所有水果中存在组合的所有记录，我们需要将它与表本身JOIN.

So to get all the records that has a combination present in all fruits, we need to JOIN it with the table itself.

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  col2, col3
            FROM    tableName
            GROUP   BY col2, col3
            HAVING  COUNT(*) = (SELECT COUNT(DISTINCT col1) FROM tableName)
        ) b ON a.col2 = b.col2 AND
                a.col3 = b.col3

• SQLFiddle 演示

这篇关于为列的每个不同类型提取多个相似行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！