MySQL 按 id 字段上的 x 个字符进行分组
我有一个状态很差的客户端数据库，但我必须使用它。我们有一组产品 id，每个 id 都由产品的属性组成。我想要做的是选择那些与前 4 或 5 个字符匹配的 id，并将它们作为所选产品的变体放在下拉框中。
我尝试过“group by subtr(0,6)”，但这似乎只返回组中的第一个匹配项。我需要的是有单独的查询，只返回具有匹配的前 x 个字符的所有单独行。
因此，例如，我想要返回以下查询： 12345xx 12345年 12345zz
然后另一个将返回： 56789xx 56789yy 56789zz
以下查询将仅返回：
12345xx
，然后返回
56789yy
select tbl_item.Description, tbl_item.`Description 2`, tbl_item.`Inventory Posting Group`, tbl_item.No_, tbl_item.`Web Description`, tbl_item.`Web Headers`, tbl_item.`Unit Price`, tbl_item.`Sales (Qty_)`, tbl_item.`Product Group Code`
from tbl_item
where tbl_item.`Product Group Code` = ':shopprodid' and tbl_item.`Vendor No_` = '$vendor_no'
group by substr(0,6)
I have a client database that is in a pretty poor state, but I have to work with it. We have a set of product id's and each id is made up of the attributes of the product. What i'd like to do is select those id's that match the first 4 or 5 characters and have them in a drop down box as variants of a selected product.
I've tried 'group by subtr(0,6)', but this seems to return only the first match in the group. What I need is to have separate queries, that ONLY return ALL the individual rows that have a matching first x characters.
So, for example, I want to have queries that will return:
12345xx
12345yy
12345zz
and then another that will return:
56789xx
56789yy
56789zz
This following query would return only:
12345xx
and then
56789yy for example
select tbl_item.Description, tbl_item.`Description 2`, tbl_item.`Inventory Posting Group`, tbl_item.No_, tbl_item.`Web Description`, tbl_item.`Web Headers`, tbl_item.`Unit Price`, tbl_item.`Sales (Qty_)`, tbl_item.`Product Group Code`
from tbl_item
where tbl_item.`Product Group Code` = ':shopprodid' and tbl_item.`Vendor No_` = '$vendor_no'
group by substr(0,6)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
解决方案是将 substr 和 LIKE 放在 WHERE 子句中（不需要对任何内容进行分组）。顺便说一句，在 MySql 中，子字符串从索引 1 开始，而不是 0。
The solution is to put the substr and LIKE in the WHERE clause (no need to group anything). And btw, in MySql the substring starts at index 1, not 0.
substr(0,6)是什么意思？正确的语法我想你的意思是：
纠正它。
对于您想要的结果，您根本不需要 substr，请使用如下查询：
What do you mean by
substr(0,6)? the correct syntax isI suppose you mean:
correct that.
And for the results you want you don't need the substr at all, use a query like this:
尝试如下操作；
Try something like the following;