mysql 分组 字符串,MYSQL：对字符串文字使用GROUP BY

I have the following table with these columns:

shortName, fullName, ChangelistCount

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.

I tried something along the lines of:

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

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

Thanks!

解决方案

Perhaps you want something like:

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

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.

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

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.