原题目
答题
楼主选用的mysql数据库来做这题,创建题目所需表及数据
CREATE TABLE `t_test_3` (
`A` int(11) ,
`B` int(11)
) ;
INSERT INTO `t_test_3` VALUES (1, 1);
INSERT INTO `t_test_3` VALUES (2, 1);
INSERT INTO `t_test_3` VALUES (3, 1);
INSERT INTO `t_test_3` VALUES (4, 1);
INSERT INTO `t_test_3` VALUES (5, 0);
INSERT INTO `t_test_3` VALUES (6, 0);
INSERT INTO `t_test_3` VALUES (7, 0);
INSERT INTO `t_test_3` VALUES (8, 0);
INSERT INTO `t_test_3` VALUES (9, 1);
INSERT INTO `t_test_3` VALUES (10, 1);
INSERT INTO `t_test_3` VALUES (11, 1);
INSERT INTO `t_test_3` VALUES (12, 1);
1.思路
看题可知
a.下一行B列值与上一行B列值不同时,C列值默认为1;
b.下一行B列值与上一行B列值相同时,C列值在上一行的基础上加1
实现思路
a.在当前行,先拿到下一行的B列值,作为第三列比较列Q3,第一行默认为0
b.再用B列值与Q3列值做比对,相同就在上一行的C列值上加1;不同就默认为1(这里用到了一个累计变量值@total)
2.实现
SELECT
tmp.Q1 AS A,
tmp.Q2 AS B,
CASE WHEN tmp.Q2 = tmp.Q3 THEN @total := @total + 1
ELSE @total := 1 END as C
FROM
(
select t1.A as Q1, t1.B as Q2, coalesce(t2.B2, 0) as Q3
from
(select A,B,1 as C from t_test_3) t1
LEFT JOIN
(select A + 1 as A2, B as B2 from t_test_3) t2
ON t1.A = t2.A2 ORDER BY t1.A asc
) tmp,
(SELECT @total := 1) AS T1
ORDER BY
tmp.Q1;
3.验证
新增行数据
INSERT INTO `t_test_3` VALUES (13, 2);
INSERT INTO `t_test_3` VALUES (14, 2);
INSERT INTO `t_test_3` VALUES (15, 9);
INSERT INTO `t_test_3` VALUES (16, 1);
结果
与题目所要结果一样,如果更好的方法或思路,欢迎留言交流