MYSQL:将GROUP BY与字符串文字一起使用 [英] MYSQL: Using GROUP BY with string literals

问题描述

我在下表中有这些列:

shortName, fullName, ChangelistCount

有没有办法在其fullName中按字符串文字对它们进行分组?全名代表文件目录，因此我想显示某些父文件夹的结果，而不是单个文件.

Is there a way to group them by a string literal within their fullName? The fullname represents file directories, so I would like to display results for certain parent folders instead of the individual files.

我尝试了以下方法:

GROUP BY fullName like "%/testFolder/%" AND fullName like "%/testFolder2/%"

但是它只能真正按照第一场比赛分组....

However it only really groups by the first match....

谢谢！

推荐答案

也许您想要类似的东西:

Perhaps you want something like:

GROUP BY IF(fullName LIKE '%/testfolder/%', 1, IF(fullName LIKE '%/testfolder2/%', 2, 3))

要理解的关键思想是，像fullName LIKE foo AND fullName LIKE bar这样的表达式是整个表达式必须计算为TRUE或FALSE，因此您只能从中得到两个组.

The key idea to understand is that an expression like fullName LIKE foo AND fullName LIKE bar is that the entire expression will necessarily evaluate to either TRUE or FALSE, so you can only get two total groups out of that.

使用IF表达式返回几个 个不同的值之一，将使您获得更多的分组.

Using an IF expression to return one of several different values will let you get more groups.

请记住，这不会特别快.如果数据集非常大，则应该探索其他不需要LIKE比较就可以进行分组的数据存储方式.

Keep in mind that this will not be particularly fast. If you have a very large dataset, you should explore other ways of storing the data that will not require LIKE comparisons to do the grouping.

这篇关于MYSQL:将GROUP BY与字符串文字一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！