MYSQL：将 GROUP BY 与字符串文字一起使用

发布于 2024-09-27 07:49:02 字数 308 浏览 4 评论 0原文

我有包含这些列的下表：

shortName, fullName, ChangelistCount

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

我尝试了以下内容：

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

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

谢谢！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情释 2024-10-04 07:49:02

也许您想要这样的东西：

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

要理解的关键思想是像 fullName LIKE foo AND fullName LIKE bar 这样的表达式是整个表达式必须计算为 TRUE 或 < code>FALSE，所以你只能从中得到两个组。

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

请记住，这不会特别快。如果您有一个非常大的数据集，您应该探索其他存储数据的方法，这些方法不需要 LIKE 比较来进行分组。

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.

回复收藏 0 原文

一瞬间的火花 2024-10-04 07:49:02

您必须使用子查询来派生您想要最终分组的列值：

    FROM (SELECT SUBSTR(fullname, ?)AS derived_column
            FROM YOUR_TABLE ) x
GROUP BY x.derived_column

You'd have to use a subquery to derive the column values you'd like to ultimately group on:

    FROM (SELECT SUBSTR(fullname, ?)AS derived_column
            FROM YOUR_TABLE ) x
GROUP BY x.derived_column

回复收藏 0 原文

來不及說愛妳 2024-10-04 07:49:02

使用“when/then”条件或使用另一个临时表，其中包含您希望查找和分组的所有匹配项。来自我的数据库的样本。

在这里，我想根据地址字段内的城市对所有用户进行分组。

SELECT ut.* , c.city, ua.*
FROM  `user_tracking` AS ut
LEFT JOIN cities AS c ON ut.place_name LIKE CONCAT(  "%", c.city,  "%" )
LEFT JOIN users_auth AS ua ON ua.id = ut.user_id

Either use when/then conditions or Have another temporary table containing all the matches you wish to find and group. Sample from my database.

Here I wanted to group all users based on their cities which was inside address field.

SELECT ut.* , c.city, ua.*
FROM  `user_tracking` AS ut
LEFT JOIN cities AS c ON ut.place_name LIKE CONCAT(  "%", c.city,  "%" )
LEFT JOIN users_auth AS ua ON ua.id = ut.user_id

回复收藏 0 原文

~没有更多了~