一句SQL计算出一组数据的循环周期长度，支持MySQL，Oracle，DB2

honey_pie

已于 2022-06-13 03:44:30 修改

阅读量266

点赞数 1

分类专栏：数据库文章标签： sql mysql dba

于 2022-06-13 03:41:26 首次发布

本文链接：https://blog.csdn.net/csdn_hypocrite/article/details/125252988

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

处理轮班数据产生的需求，一个人的轮班是早班、早班、中班、中班、夜班、休息，6天一个循环。
已知此人的轮班数据，用SQL求出轮班周期的长度。

MySQL实现

# MYSQL版本，需要MySQL 8以上
# 下面数据中循环的长度是 5：甲、乙、甲、乙、丙
# SQL执行结果返回 5
WITH RECURSIVE data(SIGN) AS (
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual
),
     data2 (ROWNUM, SIGN) AS (
         SELECT ROW_NUMBER() OVER (), SIGN
         FROM data
     ),
     cycle_length(I, SIGN_COUNT) as (
         SELECT 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROWNUM, 1) = 0) SIGN_COUNT
         FROM dual
         UNION ALL
         SELECT I + 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROWNUM, I + 1) = 0) SIGN_COUNT
         FROM cycle_length
         WHERE SIGN_COUNT != 1
     )
SELECT I
FROM cycle_length
WHERE SIGN_COUNT = 1;



# 下面数据中循环的长度是 6：甲、乙、甲、乙、丙、丁
# SQL执行结果返回 6
WITH RECURSIVE data(SIGN) AS (
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL
    SELECT '丁' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL
    SELECT '丁' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL
    SELECT '丁' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL
    SELECT '丁' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual
),
     data2 (ROWNUM, SIGN) AS (
         SELECT ROW_NUMBER() OVER (), SIGN
         FROM data
     ),
     cycle_length(I, SIGN_COUNT) as (
         SELECT 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROWNUM, 1) = 0) SIGN_COUNT
         FROM dual
         UNION ALL
         SELECT I + 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROWNUM, I + 1) = 0) SIGN_COUNT
         FROM cycle_length
         WHERE SIGN_COUNT != 1
     )
SELECT I
FROM cycle_length
WHERE SIGN_COUNT = 1;

Oracle实现

-- Oracle版本，需要Oracle 11以上
-- 下面数据中循环的长度是 5：甲、乙、甲、乙、丙
-- SQL执行结果返回 5
WITH data(SIGN) AS (
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual
),
     data2 (ROW_NUMBER, SIGN) AS (
         SELECT ROWNUM, SIGN
         FROM data
     ),
     cycle_length(I, SIGN_COUNT) as (
         SELECT 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROW_NUMBER, 1) = 0) SIGN_COUNT
         FROM dual
         UNION ALL
         SELECT I + 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROW_NUMBER, I + 1) = 0) SIGN_COUNT
         FROM cycle_length
         WHERE SIGN_COUNT != 1
-- 必须要加以下条件，否则 Oracle 会判定为无限循环抛出异常
           AND I < 999999999
     )
SELECT I
FROM cycle_length
WHERE SIGN_COUNT = 1;



-- 下面数据中循环的长度是 6：甲、乙、甲、乙、丙、丁
-- SQL执行结果返回 6
WITH data(SIGN) AS (
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL
    SELECT '丁' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL
    SELECT '丁' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL
    SELECT '丁' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual UNION ALL
    SELECT '丙' FROM dual UNION ALL
    SELECT '丁' FROM dual UNION ALL

    SELECT '甲' FROM dual UNION ALL
    SELECT '乙' FROM dual
),
     data2 (ROW_NUMBER, SIGN) AS (
         SELECT ROWNUM, SIGN
         FROM data
     ),
     cycle_length(I, SIGN_COUNT) as (
         SELECT 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROW_NUMBER, 1) = 0) SIGN_COUNT
         FROM dual
         UNION ALL
         SELECT I + 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROW_NUMBER, I + 1) = 0) SIGN_COUNT
         FROM cycle_length
         WHERE SIGN_COUNT != 1
-- 必须要加以下条件，否则 Oracle 会判定为无限循环抛出异常
           AND I < 999999999
     )
SELECT I
FROM cycle_length
WHERE SIGN_COUNT = 1;

DB2实现

-- DB2版本
-- 下面数据中循环的长度是 5：甲、乙、甲、乙、丙
-- SQL执行结果返回 5
WITH data(SIGN) AS (
    VALUES ('甲'),
           ('乙'),
           ('甲'),
           ('乙'),
           ('丙'),

           ('甲'),
           ('乙'),
           ('甲'),
           ('乙'),
           ('丙'),

           ('甲'),
           ('乙'),
           ('甲'),
           ('乙'),
           ('丙'),

           ('甲'),
           ('乙')
),
     data2 (ROW_NUMBER, SIGN) AS (
         SELECT ROWNUMBER() OVER (), SIGN
         FROM data
     ),
     cycle_length(I, SIGN_COUNT) as (
         SELECT 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROW_NUMBER, 1) = 0) SIGN_COUNT
         FROM SYSIBM.DUAL
         UNION ALL
         SELECT I + 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROW_NUMBER, I + 1) = 0) SIGN_COUNT
         FROM cycle_length
         WHERE SIGN_COUNT != 1
     )
SELECT I
FROM cycle_length
WHERE SIGN_COUNT = 1;

-- 下面数据中循环的长度是 6：甲、乙、甲、乙、丙、丁
-- SQL执行结果返回 6
WITH data(SIGN) AS (
    VALUES ('乙'),
           ('丙'),
           ('丁'),

           ('甲'),
           ('乙'),
           ('甲'),
           ('乙'),
           ('丙'),
           ('丁'),

           ('甲'),
           ('乙'),
           ('甲'),
           ('乙'),
           ('丙'),
           ('丁'),

           ('甲'),
           ('乙'),
           ('甲'),
           ('乙'),
           ('丙'),
           ('丁'),

           ('甲'),
           ('乙')
),
     data2 (ROW_NUMBER, SIGN) AS (
         SELECT ROWNUMBER() OVER (), SIGN
         FROM data
     ),
     cycle_length(I, SIGN_COUNT) as (
         SELECT 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROW_NUMBER, 1) = 0) SIGN_COUNT
         FROM SYSIBM.DUAL
         UNION ALL
         SELECT I + 1, (SELECT COUNT(DISTINCT SIGN) FROM data2 WHERE MOD(ROW_NUMBER, I + 1) = 0) SIGN_COUNT
         FROM cycle_length
         WHERE SIGN_COUNT != 1
     )
SELECT I
FROM cycle_length
WHERE SIGN_COUNT = 1;