只需要对查询中的特定行进行分组 [英] Need to group only specific rows in a query

问题描述

请考虑以下简化的 MySQL 表:

Please consider the following simplified MySQL table:

id  | docID  | docTypeID | makeID | desc
1   | 1      | 1         | 1      | doc 1
2   | 1      | 1         | 2      | doc 2
3   | 2      | 2         | 1      | doc 3
4   | 3      | 3         | 2      | doc 4
5   | 1      | 1         | 4      | doc 5
6   | 2      | 2         | 5      | doc 6
7   | 4      | 1         | 1      | doc 7

在这个例子中，docID、docTypeID 和 makeID 都是外键.

In this example docID, docTypeID, and makeID are all foreign keys.

当一个 docTypeID 为 1 的文档与多个 make 相关联时，我需要将这些文档组合在一起并返回一个特殊的 make 描述('multiple make found').

When a doc of docTypeID 1 is associated with multiple makes, I need to group those docs together and return a special make description ('multiple makes found').

我需要为这个示例表返回的是:

What I need to return for this example table would be:

id  | docID  | docTypeID | makeID                | desc
1   | 1      | 1         | multiple makes found  | doc 1
3   | 2      | 2         | 1                     | doc 3
4   | 3      | 3         | 2                     | doc 4
6   | 2      | 2         | 5                     | doc 6
7   | 4      | 1         | 1                     | doc 7

请注意，在 id 7 的情况下，只找到一个 make，因此没有执行分组.

Note that in the case of id 7, only a single make is found so there is no grouping performed.

我不确定如何根据标准或是否可能限制分组.我希望这里的 SQL 天才之一可以提供帮助.如果需要任何其他信息，请告诉我.谢谢.

I'm not sure how to limit grouping based on criteria or if that is even possible. I'm hopeful one of the SQL geniuses here can assist. Please let me know if any additional info is required. Thanks.

推荐答案

你可以写:

SELECT MIN(id) AS id,
       docID,
       docTypeID,
       CASE WHEN COUNT(1) > 1
            THEN 'multiple makes found'
            ELSE MIN(makeID)
        END AS makeID,
       MIN(desc) AS desc
  FROM simplified_mysql_table
 GROUP
    BY docID,
       docTypeID
       CASE WHEN docTypeID = 1
            THEN 0
            ELSE id
        END
;

(注意 MIN(id) 和 MIN(desc) 可能不是来自同一个底层记录.如果这是一个问题，那么你需要调整上面有点.)

(N.B. The MIN(id) and MIN(desc) may not come from the same underlying record. If that's a problem, then you'll need to tweak the above a bit.)

这篇关于只需要对查询中的特定行进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！