MySQL 手机选号(AABB、ABCD、DCBA、AAA)，SQL SERVER 手机选号(AABB、ABCD、DCBA、AAA)，通过规则查询靓号

朴灿烈づ我的快乐病毒、 2023-10-14 17:12 41阅读 0赞

先上SQL SERVER：

create table plat_uidlist(Uidd varchar(15) , Areaid int , State int)
     
    insert into plat_uidlist values('2335435' ,8 ,0 )
    insert into plat_uidlist values('2335436' ,8 ,1 )
    insert into plat_uidlist values('2335437' ,2 ,2 )
    insert into plat_uidlist values('2335438' ,8 ,3 )
    insert into plat_uidlist values('2335439' ,3 ,0 )
    insert into plat_uidlist values('2335440' ,8 ,0 )
    insert into plat_uidlist values('6677889' ,8 ,0 )
    insert into plat_uidlist values('1234578' ,4 ,0 )
    insert into plat_uidlist values('4980010' ,8 ,0 )
    insert into plat_uidlist values('4980099' ,8 ,0 )
     
     
     
    --如何查询test值3位尾数以上是一样的，如尾数是8888，7777，888，777
    select *
    from plat_uidlist
    where replicate(RIGHT(Uidd,1),3)=RIGHT(Uidd,3)
     
     
    select * from plat_uidlist where abs(RIGHT(Uidd,2)*1-LEFT(RIGHT(Uidd,4),2)*1)=22
     
    -- 后4位ABCD或者DCBA
    select * from plat_uidlist where abs(RIGHT(Uidd,4-1)*1-LEFT(RIGHT(Uidd,4),4-1)*1)=REPLICATE(1,4-1)
    -- 后5位ABCDE或者EDCBA
    select * from plat_uidlist where abs(RIGHT(Uidd,5-1)*1-LEFT(RIGHT(Uidd,5),5-1)*1)=REPLICATE(1,5-1)
     
    --如何查询test值4位尾数是AABB，如8877，7766，6677
    SELECT * FROM 
    (SELECT Uidd,A = RIGHT(Uidd,1), 
    B= LEFT(RIGHT(Uidd,2),1),C = LEFT(RIGHT(Uidd,3),1),D = LEFT(RIGHT(Uidd,4),1) FROM plat_uidlist )E
    WHERE  (E.A = E.B ) AND (E.C = E.D )  AND (E.A <> E.C) 
     
    --如何查询test值4位以上尾数是顺序的，如87654321、1234567、1234、4321？
    SELECT * FROM 
    (SELECT Uidd,A = RIGHT(Uidd,1), B= LEFT(RIGHT(Uidd,2),1),C = LEFT(RIGHT(Uidd,3),1),D = LEFT(RIGHT(Uidd,4),1) FROM plat_uidlist )E
    WHERE  (E.A = E.B + 1) AND (E.B = E.C + 1)  AND (E.C = E.D + 1) 
            OR ((E.A = E.B - 1) AND (E.B = E.C - 1)  AND (E.C = E.D - 1))
            
    --如何查询test值3位尾数以上是一样的，如尾数是8888，7777，888，777？
    SELECT * FROM 
    (SELECT Uidd,A = RIGHT(Uidd,1), B= LEFT(RIGHT(Uidd,2),1),C = LEFT(RIGHT(Uidd,3),1) FROM plat_uidlist )D
    WHERE  D.A = D.B AND D.A = D.C  AND D.B = D.C
     
    -- 三连号AAA或者以上(AAAA、AAAAA),第四位起的任意位置
    select * from
    (select tel_no,A = right(left(tel_no,4),1),B = right(left(tel_no,5),1),C = right(left(tel_no,6),1),D = right(left(tel_no,7),1),
    E = right(left(tel_no,8),1),F = right(left(tel_no,9),1),G = right(left(tel_no,10),1),H = right(left(tel_no,11),1) 
    from stureg.dbo.t_tel_info) TMP
    where (TMP.A = TMP.B AND TMP.A = TMP.C AND TMP.B = TMP.C) 
    OR (TMP.B = TMP.C AND TMP.B = TMP.D AND TMP.C = TMP.D)
    OR (TMP.C = TMP.D AND TMP.C = TMP.E AND TMP.D = TMP.E)
    OR (TMP.D = TMP.E AND TMP.D = TMP.F AND TMP.E = TMP.F)
    OR (TMP.E = TMP.F AND TMP.E = TMP.G AND TMP.F = TMP.G)
    OR (TMP.F = TMP.G AND TMP.F = TMP.H AND TMP.G = TMP.H)
     
    -- AABB,第四位起任意位置
    select * from
    (select tel_no,A = right(left(tel_no,4),1),B = right(left(tel_no,5),1),C = right(left(tel_no,6),1),D = right(left(tel_no,7),1),
    E = right(left(tel_no,8),1),F = right(left(tel_no,9),1),G = right(left(tel_no,10),1),H = right(left(tel_no,11),1) 
    from stureg.dbo.t_tel_info) TMP
    where (TMP.A = TMP.B AND TMP.C = TMP.D AND TMP.B != TMP.C) 
    OR (TMP.B = TMP.C AND TMP.D = TMP.E AND TMP.C != TMP.D)
    OR (TMP.C = TMP.D AND TMP.E = TMP.F AND TMP.D != TMP.E)
    OR (TMP.D = TMP.E AND TMP.F = TMP.G AND TMP.E != TMP.F)
    OR (TMP.E = TMP.F AND TMP.G = TMP.H AND TMP.F != TMP.G)
     
    -- ABCD(ABCDE、ABCDEF)或以上,第四位起任意位置
    select * from
    (select tel_no,A = right(left(tel_no,4),1),B = right(left(tel_no,5),1),C = right(left(tel_no,6),1),D = right(left(tel_no,7),1),
    E = right(left(tel_no,8),1),F = right(left(tel_no,9),1),G = right(left(tel_no,10),1),H = right(left(tel_no,11),1) 
    from stureg.dbo.t_tel_info) TMP
    where (TMP.A+1 = TMP.B AND TMP.B+1 = TMP.C AND TMP.C+1 = TMP.D)
    OR (TMP.B+1 = TMP.C AND TMP.C+1 = TMP.D AND TMP.D+1 = TMP.E)
    OR (TMP.C+1 = TMP.D AND TMP.D+1 = TMP.E AND TMP.E+1 = TMP.F)
    OR (TMP.D+1 = TMP.E AND TMP.E+1 = TMP.F AND TMP.F+1 = TMP.G)
    OR (TMP.E+1 = TMP.F AND TMP.F+1 = TMP.G AND TMP.G+1 = TMP.H)
     
    -- DCBA或以上,第四位起任意位置
    select * from
    (select tel_no,A = right(left(tel_no,4),1),B = right(left(tel_no,5),1),C = right(left(tel_no,6),1),D = right(left(tel_no,7),1),
    E = right(left(tel_no,8),1),F = right(left(tel_no,9),1),G = right(left(tel_no,10),1),H = right(left(tel_no,11),1) 
    from stureg.dbo.t_tel_info) TMP
    where (TMP.A-1 = TMP.B AND TMP.B-1 = TMP.C AND TMP.C-1 = TMP.D)
    OR (TMP.B-1 = TMP.C AND TMP.C-1 = TMP.D AND TMP.D-1 = TMP.E)
    OR (TMP.C-1 = TMP.D AND TMP.D-1 = TMP.E AND TMP.E-1 = TMP.F)
    OR (TMP.D-1 = TMP.E AND TMP.E-1 = TMP.F AND TMP.F-1 = TMP.G)
    OR (TMP.E-1 = TMP.F AND TMP.F-1 = TMP.G AND TMP.G-1 = TMP.H)

然后上mysql的

<sql id="AAA">
            select *
            from zb_lucky_number_pool
            where REPEAT(RIGHT(lucky_number,1),3)=RIGHT(lucky_number,3)
        </sql>
        <sql id="AAAA">
            select *
            from zb_lucky_number_pool
            where REPEAT(RIGHT(lucky_number,1),4)=RIGHT(lucky_number,4)
        </sql>
        <sql id="AAAAA">
            select *
            from zb_lucky_number_pool
            where REPEAT(RIGHT(lucky_number,1),5)=RIGHT(lucky_number,5)
        </sql>
        <sql id="AAAAAA">
            select *
            from zb_lucky_number_pool
            where REPEAT(RIGHT(lucky_number,1),6)=RIGHT(lucky_number,6)
        </sql>
        <sql id="AAAAAAA">
            select *
            from zb_lucky_number_pool
            where REPEAT(RIGHT(lucky_number,1),7)=RIGHT(lucky_number,7)
        </sql>
        <sql id="ABAB">
            SELECT * FROM
            (SELECT *, RIGHT(lucky_number,1) AS A,
            LEFT(RIGHT(lucky_number,2),1) AS B,LEFT(RIGHT(lucky_number,3),1) AS C , LEFT(RIGHT(lucky_number,4),1) AS D FROM zb_lucky_number_pool )E
            WHERE  (E.A = E.C ) AND (E.B = E.D )  AND (E.A != E.B)
        </sql>
        <sql id="ABABAB">
            SELECT * FROM
                (SELECT *, RIGHT(lucky_number,1) AS A,
                     LEFT(RIGHT(lucky_number,2),1) AS B,LEFT(RIGHT(lucky_number,3),1) AS C , LEFT(RIGHT(lucky_number,4),1) AS D, LEFT(RIGHT(lucky_number,5),1) AS E, LEFT(RIGHT(lucky_number,6),1) AS F
                 FROM zb_lucky_number_pool )G
            WHERE  (G.A = G.C ) AND (G.B = G.D ) AND (G.A = G.E )  AND (G.B = G.F )AND (G.A != G.B)
        </sql>
        <sql id="AABB">
            SELECT * FROM
            (SELECT *, RIGHT(lucky_number,1) AS A,
            LEFT(RIGHT(lucky_number,2),1) AS B,LEFT(RIGHT(lucky_number,3),1) AS C , LEFT(RIGHT(lucky_number,4),1) AS D FROM zb_lucky_number_pool )E
            WHERE  (E.A = E.B ) AND (E.C = E.D )  AND (E.A != E.C)
        </sql>
        <sql id="AABBCC">
            SELECT * FROM
            (SELECT *, RIGHT(lucky_number,1) AS A,
            LEFT(RIGHT(lucky_number,2),1) AS B,LEFT(RIGHT(lucky_number,3),1) AS C , LEFT(RIGHT(lucky_number,4),1) AS D
            , LEFT(RIGHT(lucky_number,5),1) AS E
            , LEFT(RIGHT(lucky_number,6),1) AS F
            FROM zb_lucky_number_pool )G
            WHERE  (G.A = G.B ) AND (G.C = G.D ) AND (G.E = G.F ) AND (G.A != G.C)  AND (G.A != G.E) AND (G.C != G.E)
        </sql>
        <sql id="AAABBB">
            SELECT * FROM
                (SELECT *, RIGHT(lucky_number,1) as A, LEFT(RIGHT(lucky_number,2),1) AS B, LEFT(RIGHT(lucky_number,3),1) AS C
                         , LEFT(RIGHT(lucky_number,4),1) AS D
                         , LEFT(RIGHT(lucky_number,5),1) AS E
                         , LEFT(RIGHT(lucky_number,6),1) AS F
                 FROM zb_lucky_number_pool )G
            WHERE  G.A = G.B  AND G.B = G.C  AND G.D = G.E AND G.E = G.F AND G.A != G.D
        </sql>
        <sql id="ABC">
            select * from zb_lucky_number_pool where abs(RIGHT(lucky_number,3-1)*1-LEFT(RIGHT(lucky_number,3),3-1)*1)=REPEAT(1,3-1)
        </sql>
        <sql id="ABCD">
            select * from zb_lucky_number_pool where abs(RIGHT(lucky_number,4-1)*1-LEFT(RIGHT(lucky_number,4),4-1)*1)=REPEAT(1,4-1)
        </sql>
        <sql id="ABCDE">
            select * from zb_lucky_number_pool where abs(RIGHT(lucky_number,5-1)*1-LEFT(RIGHT(lucky_number,5),5-1)*1)=REPEAT(1,5-1)
        </sql>
        <sql id="ABCDEF">
            select * from zb_lucky_number_pool where abs(RIGHT(lucky_number,6-1)*1-LEFT(RIGHT(lucky_number,6),6-1)*1)=REPEAT(1,6-1)
        </sql>
        <sql id="ABCABC">
            SELECT * FROM
                (SELECT *, RIGHT(lucky_number,1) as A, LEFT(RIGHT(lucky_number,2),1) AS B, LEFT(RIGHT(lucky_number,3),1) AS C
                         , LEFT(RIGHT(lucky_number,4),1) AS D
                         , LEFT(RIGHT(lucky_number,5),1) AS E
                         , LEFT(RIGHT(lucky_number,6),1) AS F
                 FROM zb_lucky_number_pool )G
            WHERE  G.A = G.D  AND G.B = G.E  AND G.C = G.F AND abs(G.A - G.B) = 1 AND abs(G.B - G.C) = 1
        </sql>
        <sql id="ABCDABCD">
            SELECT * FROM
                (SELECT *, RIGHT(lucky_number,1) as A, LEFT(RIGHT(lucky_number,2),1) AS B, LEFT(RIGHT(lucky_number,3),1) AS C
                         , LEFT(RIGHT(lucky_number,4),1) AS D
                         , LEFT(RIGHT(lucky_number,5),1) AS E
                         , LEFT(RIGHT(lucky_number,6),1) AS F
                         , LEFT(RIGHT(lucky_number,7),1) AS G
                         , LEFT(RIGHT(lucky_number,8),1) AS H
                 FROM zb_lucky_number_pool ) Z
            WHERE  Z.A = Z.E  AND Z.B = Z.F  AND Z.C = Z.G AND Z.D = Z.H AND abs(Z.A - Z.B) = 1 AND abs(Z.B - Z.C) = 1 AND abs(Z.C - Z.D) = 1
        </sql>
        <sql id="520">
            select * from zb_lucky_number_pool where lucky_number REGEXP '520$'
        </sql>
        <sql id="1314">
            select * from zb_lucky_number_pool where lucky_number REGEXP '1314$'
        </sql>
        <sql id="5201314">
            select * from zb_lucky_number_pool where lucky_number REGEXP '5201314$'
        </sql>