MySQL-组内计数器 [英] MySQL - Counter within group
本文介绍了MySQL-组内计数器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想根据升序变量为组中的每一行添加一个计数器.我有一个解决方案,但如果组中的某些变量相等,则它不起作用:
I would like to add a counter for each row within a group according an ascending variable. I have a solution but it does not work if some variable within groups are equal:
CREATE TABLE tb (
g CHAR(1)
, x INTEGER
);
INSERT INTO tb (g, x)
VALUES
('a',1)
, ('a',2)
, ('a',10)
, ('b',1)
, ('b',1)
, ('b',10)
;
SELECT g,x, (SELECT COUNT(*)
FROM tb b
WHERE a.g = b.g
AND a.x > b.x
) + 1 AS counter
FROM tb a
;
我得到的是:
--------------------
| g | x | counter |
--------------------
| a | 1 | 1 |
| a | 2 | 2 |
| a | 10 | 3 |
| b | 1 | 1 |
| b | 1 | 1 |
| b | 10 | 3 |
--------------------
但是我想得到:
--------------------
| g | x | counter |
--------------------
| a | 1 | 1 |
| a | 2 | 2 |
| a | 10 | 3 |
| b | 1 | 1 |
| b | 1 | 2 |
| b | 10 | 3 |
--------------------
有什么主意可以解决这个问题吗?非常感谢您的提示.
佐丹奴
Any idea how I can solve this problem? Thanks a lot for hints.
giordano
推荐答案
用户定义的变量在解决此问题时变得很方便.这应该起作用:
User Defined Variables become handy when solving this issues. This should work:
select g, x, counter from (
select g, x,
@counter := if (g = @prev_g, @counter + 1, 1) counter,
@prev_g := g
from tb, (select @counter := 0, @prev_g := null) init
order by g, x
) s
如果您真的不介意第四栏,那么可以安全地删除外部选择.这样可以大大提高性能.
If you don't really mind the fourth column then you could safely remove the outer select. That would improve performance a lot.
这篇关于MySQL-组内计数器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文