Demo表的创建：

CREATE TABLE `historydata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Loid` char(7) NOT NULL,
  `R1` int(11) DEFAULT NULL,
  `R2` int(11) DEFAULT NULL,
  `R3` int(11) DEFAULT NULL,
  `R4` int(11) DEFAULT NULL,
  `R5` int(11) DEFAULT NULL,
  `R6` int(11) DEFAULT NULL,
  `Blue` int(11) DEFAULT NULL,
  `BlueSunday` varchar(10) DEFAULT NULL,
  `SalesAmount` int(11) DEFAULT NULL,
  `FirstCount` int(11) DEFAULT NULL,
  `FisrtBonus` int(11) DEFAULT NULL,
  `SecCount` int(11) DEFAULT NULL,
  `SecBonus` int(11) DEFAULT NULL,
  `PoolAmount` int(11) DEFAULT NULL,
  `lottime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Loid` (`Loid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

 

1. 查找id不连续的行： https://www.cnblogs.com/zunzunQ/p/14190222.html

SELECT 
    id, COUNT(*)
FROM
    lottery.historydata a
WHERE
    NOT EXISTS( SELECT 
            1
        FROM
            lottery.historydata b
        WHERE
            a.id + 1 = b.id)

2. 查找blue不连续行的最大差值

SELECT 
    *, MAX(id - nextId)
FROM
    (SELECT 
        id, loid, blue, @tmp AS nextId, @tmp:=id
    FROM
        lottery.historydata a, (SELECT @tmp:=0) b
    WHERE
        blue = 5) t

3. 分组查询blue不连续行的最大差值（待优化）

SELECT 
    blue, MAX(nowid - nextid)
FROM
    (SELECT DISTINCT
        a.*, @tmp AS nextId, @tmp:=a.id AS nowid
    FROM
        lottery.historydata a
    LEFT JOIN lottery.historydata b ON (a.id > b.id), (SELECT @tmp:=0) t
    WHERE
        a.blue = b.blue
    ORDER BY a.blue) aaaa
GROUP BY blue

4.重复数据：

SELECT 
    *
FROM
    lottery.historydata
WHERE
    (r1 , r2, r3, r4, r5, r6) IN (SELECT 
            r1, r2, r3, r4, r5, r6
        FROM
            lottery.historydata
        GROUP BY r1 , r2 , r3 , r4 , r5 , r6
        HAVING COUNT(*) > 1);

5. mysql 日期字符串 格式化 ： https://mysqlcode.com/mysql-date_format/

%y 两位年，大写Y四位年
%m 两位月，大写M英语月份
%d 两位日，大写D第几天th
%h 两位12小时制时，大写24小时制时
%i 两位分，大写I 12小时制时
%s 两位秒，不区分大小写
%T 带冒号的24小时制的时分秒15:38:55, 小写t没有意义
   

6. 统计遗漏次数

set @b01 = 0;
set @b02 = 0;
set @b03 = 0;
set @b04 = 0;
set @b05 = 0;
set @b06 = 0;
set @b07 = 0;
set @b08 = 0;
set @b09 = 0;
set @b10 = 0;
set @b11 = 0;
set @b12 = 0;
set @b13 = 0;
set @b14 = 0;
set @b15 = 0;
set @b16 = 0;
set @t = 0;
set @miss = 0;

SELECT id, (case blue when 01 then @b01 := 0 else  @b01 := @b01 + 1 end) as b01,
(case blue when 02 then @b02 := 0 else  @b02 := @b02 + 1 end) as b02,
(case blue when 03 then @b03 := 0 else  @b03 := @b03 + 1 end) as b03,
(case blue when 04 then @b04 := 0 else  @b04 := @b04 + 1 end) as b04,
(case blue when 05 then @b05 := 0 else  @b05 := @b05 + 1 end) as b05,
(case blue when 06 then @b06 := 0 else  @b06 := @b06 + 1 end) as b06,
(case blue when 07 then @b07 := 0 else  @b07 := @b07 + 1 end) as b07,
(case blue when 08 then @b08 := 0 else  @b08 := @b08 + 1 end) as b08,
(case blue when 09 then @b09 := 0 else  @b09 := @b09 + 1 end) as b09,
(case blue when 10 then @b10 := 0 else  @b10 := @b10 + 1 end) as b10,
(case blue when 11 then @b11 := 0 else  @b11 := @b11 + 1 end) as b11,
(case blue when 12 then @b12 := 0 else  @b12 := @b12 + 1 end) as b12,
(case blue when 13 then @b13 := 0 else  @b13 := @b13 + 1 end) as b13,
(case blue when 14 then @b14 := 0 else  @b14 := @b14 + 1 end) as b14,
(case blue when 15 then @b15 := 0 else  @b15 := @b15 + 1 end) as b15,
(case blue when 16 then @b16 := 0 else  @b16 := @b16 + 1 end) as b16,
@t := @b01 +@b02 +@b03 +@b04 +@b05 +@b06 +@b07 +@b08 +@b09 +@b10 +@b11 +@b12 +@b13 +@b14 +@b15 +@b16 as total,
@miss + 15 - @t as missCnt, @miss := @t as prev
FROM lottery.historydata group by id, blue;