我是 mysql 的新手。我在购物车表中有如下记录
id code
1 100
2 101
3 102
4 100
5 100
6 101
我的例外输出如下:-
id code serial_number
1 100 1
2 101 2
3 102 3
4 100 1
5 100 1
6 101 2
我想要属于同一组的相同序列号,即代码。谁能帮我怎么办?
您的组的定义是从code = 100
。这有点棘手。在 MySQL 8+ 中,您可以使用窗口函数来定义组,然后row_number()
来分配serial_number
:
select c.*,
row_number() over (partition by grp order by id) as serial_number
from (select c.*,
sum( code = 100 ) over (order by id) as grp
from carts c
) c;
这在 MySQL 的早期版本中要复杂得多。
您可以使用子查询来识别组:
select c.*,
(select count(*)
from carts c2
where c2.id <= c.id and c2.code = 100
) as grp
from carts c;
然后您可以使用变量来获取您想要的信息:
select c.*,
(@rn := if(@g = grp, @rn + 1,
if(@g := grp, 1, 1)
)
) as serial_number
from (select c.*,
(select count(*)
from carts c2
where c2.id <= c.id and c2.code = 100
) as grp
from carts c
order by grp, id
) c cross join
(select @g := -1, @rn := 0) params;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)