我从昨天开始创建 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 2dbfiddle 演示
更多推荐
如何为mysql中的每个组提供相同的值
发布评论