如何为mysql中的每个组提供相同的值

本文介绍了如何为mysql中的每个组提供相同的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我从昨天开始创建 mysql 查询就被困住了.我有一个像下面这样的桌车:-

i am stuck from creating mysql query from yesterday. i have one table carts like below :-

id material_code 1 ABC100 2 DEF200 3 ABC100 4 ABC100 5 DEF200

材料代码将是 16 位数字，为了更好地理解，我已将其缩小.我想输出如下内容:-

Material code will 16 digits long i have taken small for better understand. i want output something like this below:-

id material_code item_number 1 ABC100 1 2 DEF200 2 3 ABC100 1 4 ABC100 1 5 DEF200 2

如您所见，ABC100 正在重复，因此它的 item_number 为 1，DEF200 为 2，依此类推.谁能帮助我如何实现这一目标?我在谷歌上搜索过什么都没有找到.

As you see ABC100 is repeating so its having item_number 1 and DEF200 is 2 and so on. Can anyone help me how to achieve this?. I have searched on google nothing found.

推荐答案

对于早于 8 的 MySQL 版本，您可以使用变量来模拟 ROW_NUMBER() 功能.此处的子查询根据material_code 是否与前一行相同来分配项目编号，处理按material_code 排序的数据.然后我们SELECT子查询中的所有内容，再次按id排序.

For versions of MySQL earlier than 8, you can use variables to simulate ROW_NUMBER() functionality. The subquery here assigns an item number according to whether the material_code is the same as the previous row, working with data sorted by material_code. We then SELECT everything from the subquery, ordering by id again.

SELECT id, material_code, item_number FROM (SELECT id, @i := (CASE WHEN @m = material_code THEN @i ELSE @i + 1 END) AS item_number, @m := material_code AS material_code FROM carts JOIN (SELECT @m := '', @i := 0) v ORDER BY material_code) c ORDER BY id

输出:

id material_code item_number 1 ABC100 1 2 DEF200 2 3 ABC100 1 4 ABC100 1 5 DEF200 2

dbfiddle 演示