如何为mysql中的每个组提供相同的值 [英] how to give same value for each group in 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 演示

这篇关于如何为mysql中的每个组提供相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！