除某些值外的分组依据 [英] Group By Except For Certain Value

问题描述

我正在尝试创建一个 (sqlite) 查询，该查询将执行 GROUP BY 但不会对值为unknown"的任何内容进行分组.例如，我有一张桌子:

I'm trying to create a (sqlite) query that will perform a GROUP BY but will not group anything with the value 'unknown'. For example, I have the table:

id |   name  | parent_id | school_id |
 1 | john    |   1       |    1      |
 2 | john    |   1       |    1      |
 3 | john    |   1       |    1      |
 4 | nick    |   2       |    2      |
 5 | nick    |   2       |    2      |
 6 | nick    |   3       |    3      |
 7 | bob     |   4       |    4      |
 8 | unknown |   5       |    5      |
 9 | unknown |   5       |    5      |
 10| unknown |   5       |    5      |

使用'GROUP BY name, parent_id, school_id'的正确查询，我需要返回以下行:

With the proper query with 'GROUP BY name, parent_id, school_id' I need the following rows returned:

id |   name  | parent_id | school_id |
 1 | john    |   1       |    1      |
 3 | nick    |   2       |    2      |
 4 | nick    |   3       |    3      |
 5 | bob     |   4       |    4      |
 6 | unknown |   5       |    5      |
 7 | unknown |   5       |    5      |
 8 | unknown |   5       |    5      |

任何帮助将不胜感激.谢谢！

Any help would be greatly appreciated. Thanks!

推荐答案

你不能用一个语句轻松做到这一点，但你可以UNION两个语句的结果

You can't easily do this with one statement but you can UNION the results of two statements

• GROUP 列出所有but unknown
• 添加(UNION)所有unknown
的列表

• GROUP the list of all but unknown
• Add (UNION) the list of all unknown

SQL 语句

SELECT MIN(id), name, parent_id, school_id
FROM   YourTable
WHERE  name <> 'unknown'
GROUP BY
       name, parent_id, school_id
UNION ALL
SELECT id, name, parent_id, school_id
FROM   YourTable
WHERE  name = 'unknown'

请注意，我假设您在结果中发布了错误的 unknown id

这篇关于除某些值外的分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！