每当以下查询中的 id 更改时,我都会尝试将变量值设置为零。
我试图在没有性能问题的单个查询中实现这一点。因为在实际查询中,它有超过 10,000 条记录。这是我到目前为止所拥有的
示例表:
create table Test(id integer, title varchar(100));
insert into Test(id, title) values(1, "Hello");
insert into Test(id, title) values(1, "Hello");
insert into Test(id, title) values(1, "Hello");
insert into Test(id, title) values(1, "Hello");
insert into Test(id, title) values(2, "Hello");
insert into Test(id, title) values(2, "Hello");
insert into Test(id, title) values(1, "Hello");
这是我的查询:
select a0.id,a0.title,@a:=@a+a0.id as val from
(select * from Test order by id) as a0
left join
(select id, @a:=0 from Test group by id) as a1
on
a0.id=a1.id
-- 执行上述查询得到的输出:
+----+-------+-----+
| id | title | val |
+----+-------+-----+
| 1 | Hello | 1 |
| 1 | Hello | 2 |
| 1 | Hello | 3 |
| 1 | Hello | 4 |
| 1 | Hello | 5 |
| 2 | Hello | 7 |
| 2 | Hello | 9 |
+----+-------+-----+
——我想要达到的目标:
+----+-------+-----+
| id | title | val |
+----+-------+-----+
| 1 | Hello | 1 |
| 1 | Hello | 2 |
| 1 | Hello | 3 |
| 1 | Hello | 4 |
| 1 | Hello | 5 |
| 2 | Hello | 2 |
| 2 | Hello | 4 |
+----+-------+-----+
使用另一个变量来保存上一行的 ID。然后可以测试当前ID是否相同。
当它更改时,重置@a
为当前 ID(不是0
)。
SELECT a0.id, a0.title, @a := IF(a0.id = @last_id, @a + a0.id, a0.id) AS val, @last_id := a0.id
FROM (SELECT * FROM Test ORDER BY id) AS a0
CROSS JOIN (SELECT @a := 0, @last_id := null) AS vars
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句