mysql - 特定值的序列 MySql 表

Question

我正在尝试计算具有值的连续行数。举一个简单的例子，考虑抛出 2 个骰子，骰子的值是 1-12。我想获得具有相同总数的连续行的最高序列的值表。下面是表定义、填充表的存储过程和执行存储过程的命令:

CREATE TABLE `Dice_throws` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `Dice1` tinyint(1) DEFAULT NULL,
  `Dice2` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`idx`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
---------------------------------
DELIMITER $$
CREATE PROCEDURE InsertDiceThrows(IN NumRows INT)
    BEGIN
        DECLARE i INT;
        SET i = RAND(12345); /* use the same seed */
        SET i = 1;
        START TRANSACTION;
        WHILE i <= NumRows DO
            INSERT INTO Dice_throws(Dice1, Dice2) VALUES (CEIL(RAND() * 6), CEIL(RAND() * 6));
            SET i = i + 1;
        END WHILE;
        COMMIT;
    END$$
DELIMITER ;
-----------------------------
call InsertDiceThrows(2000);

如果选择总计列表 (SELECT Dice1, Dice2, Dice1+Dice2 FROM Dice_throws order by idx;) 那么我将得到 2000 行，例如 2， 5,12,6,7,4,8,8,8,5,11,6,6,..等。最后我想要一个像这样的表:

Tot  Seq
1    2
2    2
3    4
4    3
5    7
6    8
7    9
8    4
9    2
10   1
11   2
12   1

所以这表明在连续行中总数为 4 的最大序列为 3，在连续 8 行中总数为 6 等。所以我想找到每个可能结果的总数相同的最高滚动序列.我显然可以在 PHP 中做到这一点(我已经使它与 PHP 一起工作)，只需选择合计然后循环遍历结果集，但我想知道是否有一些好的方法可以完全在 SQL 中做到这一点。感谢您提供任何提示或解决方案。

Answer 1

尝试以下操作:

CREATE TABLE test (rid int auto_increment PRIMARY KEY, v int not null);
INSERT test (v) VALUES (2),(2),(3),(3),(3),(2),(4),(4),(4),(4),(4),(4),(2),(2),(2),(2),(1);

SELECT * FROM test;

CREATE TABLE chunks (cid int auto_increment PRIMARY KEY, v int not null, c int not null, s int not null);
INSERT chunks(cid,v,c,s)
SELECT null,t1.v,t1.rid AS c, 0
FROM test t1
LEFT JOIN test t2 ON t1.rid=t2.rid-1 AND t1.v=t2.v
WHERE t2.rid IS NULL;

UPDATE chunks c1 LEFT JOIN chunks c2 ON c1.cid=c2.cid+1
SET c1.s=CASE WHEN c2.c IS NULL THEN 1 ELSE c2.c+1 END;
SELECT * FROM chunks;

SELECT v.v,MAX(v.cnt) AS max_cnt
FROM (
  SELECT c.c, MIN(t1.v) AS v, COUNT(*) AS cnt
  FROM test t1
  INNER JOIN chunks c
  ON t1.rid between c.s and c.c
  GROUP BY c.c) v
GROUP BY v.v;