mysql组通过使用范围记录值

Question

在 SQL 中，我有下面的表 t_test：

emp_code | period_month | company_code  
NIK001   | 01           | ALPHA
NIK001   | 02           | ALPHA
NIK001   | 03           | ALPHA
NIK001   | 04           | ALPHA
NIK001   | 05           | ALPHA
NIK001   | 06           | BETA 
NIK001   | 07           | BETA
NIK001   | 08           | BETA
NIK001   | 09           | BETA
NIK001   | 10           | ALPHA
NIK001   | 11           | ALPHA
NIK001   | 12           | ALPHA

我想查询以下结果：

emp_code | company_code | from_month  | to_month 
--------------------------------------------------
NIK001   | ALPHA        | 01          | 05
NIK001   | BETA         | 06          | 09
NIK001   | ALPHA        | 10          | 12

Answer 1

这让我恐惧地颤抖，但它确实输出了你所追求的近似值：

    select w.emp_code, w.company_code, w.period_month from_month,
(select min(convert(u.period_month,unsigned))-1 from t_test u where u.emp_code=w.emp_Code and convert(u.period_month,signed)>convert(w.period_month,signed) and u.company_code<>w.company_code) to_month
 from 
(
select * from
(
select y.emp_code, y.period_month, y.company_code,
(select x.company_code from t_test x where x.emp_code=y.emp_code and convert(x.period_month,unsigned)<convert(y.period_month,unsigned) order by convert(x.period_month,unsigned) desc limit 1) previous_company_code
 from t_test y
) z
where company_code<>previous_company_code or previous_company_code is null
) w

但是，我只是出于好奇而尝试了这个。您不应该在生产环境中真正依赖类似的东西。我认为简单的 SQL 查询不太适合您的问题。我会去别处看看。

编辑：基于下表：

CREATE TABLE `t_test` (
  `emp_code` varchar(50) DEFAULT NULL,
  `period_month` varchar(2) DEFAULT NULL,
  `company_code` varchar(50) DEFAULT NULL
);

填充以下 INSERT 语句：

INSERT INTO `t_test` (`emp_code`,`period_month`,`company_code`)
VALUES
    ('NIK001', '01', 'ALPHA'),
    ('NIK001', '02', 'ALPHA'),
    ('NIK001', '03', 'ALPHA'),
    ('NIK001', '04', 'ALPHA'),
    ('NIK001', '05', 'ALPHA'),
    ('NIK001', '06', 'BETA'),
    ('NIK001', '07', 'BETA'),
    ('NIK001', '08', 'BETA'),
    ('NIK001', '09', 'BETA'),
    ('NIK001', '10', 'ALPHA'),
    ('NIK001', '11', 'ALPHA'),
    ('NIK001', '12', 'ALPHA');

Answer 2

您可以使用 MySQL 变量。例如：

SET @ttt = 0;

SELECT
    @ttt := IF(
        `company_code`=(SELECT `company_code` FROM `range` r2 WHERE r2.`period_month`<r1.`period_month` ORDER BY `period_month` DESC LIMIT 1), 
        @ttt,
        @ttt+1) iter,
`period_month`,
`company_code`
FROM `range` r1
WHERE emp_code = 'NIK001'
ORDER BY period_month ASC;

在我的测试数据上，这给了我：

iter period_month company_code
1    1            Alpha
1    2            Alpha
1    3            Alpha
2    4            Beta
2    5            Beta
2    6            Beta
3    7            Alpha
3    8            Alpha
3    9            Alpha
3    10           Alpha

这个想法是在每一行检查当前的company_code是否等于前一条记录的company_code。如果不同，则增加变量。

这是向前迈出的一步。但如何对行进行分组呢？您无法直接对它们进行分组，但需要用第二个查询包装该查询。

SET @ttt = 0;

SELECT `company_code`,MIN(`period_month`),MAX(`period_month`)
FROM
(    SELECT
        @ttt := IF(
            `company_code`=(SELECT `company_code` FROM `range` r2 WHERE r2.`period_month`<r1.`period_month` ORDER BY `period_month` DESC LIMIT 1), 
            @ttt,
            @ttt+1) iter,
    `period_month`,
    `company_code`
    FROM `range` r1
    WHERE emp_code = 'NIK001'
    ORDER BY period_month ASC
    ) subreq
GROUP BY iter
;

这是在外部查询中使用内部查询的结果。对于我的测试数据，

company_code MIN(`period_month`) MAX(`period_month`)
Alpha        1                   3
Beta         4                   6
Alpha        7                   10

如果您需要限制行的选择，请在 内部 查询的 where 子句中执行此操作，否则整个数据库将在每个查询上加载到内存中。另一个问题是，内部查询将前一条记录作为“具有最接近的较低 period_month 的记录。因此，您不能按任何内容对内部查询进行排序但是period_month ASC，如果您愿意，您仍然可以在外部查询中对内容进行排序。

最后，如果您使用旧的mysql_query接口。出于某种原因，你需要把SET 行在一个单独的查询中，因为它一次只能处理一个查询，

mysql_query("SET @ttt = 0;");

$rs=mysql_query("SELECT `company_code`,MIN(`period_month`),MAX(`period_month`)
FROM
(    SELECT
        @ttt := IF(
            `company_code`=(SELECT `company_code` FROM `range` r2 WHERE r2.`period_month`<r1.`period_month` ORDER BY `period_month` DESC LIMIT 1), 
            @ttt,
            @ttt+1) iter,
    `period_month`,
    `company_code`
FROM `range` r1) subreq
    GROUP BY iter
;");

不确定这种方法在扩展到大型数据库时效果如何，但它肯定有效。 “方式。只要确保您不会意外地弄乱查询中的某些内容，以防您不完全理解它。:)

Answer 3

@magma：非常感谢您的回答。好工作！谢谢..

我只是做了一点修改，所以 from_month 和 to_month 永远不会得到 NULL 值。

为了加快执行查询的速度，我使用会话创建临时表来存储查询结果，并使用临时数据来生成报告

SELECT
  w.emp_code, w.company_code, w.period_month from_month,
  LPAD(
    IFNULL(
    IFNULL(
      ( select min(u.period_month)-1
        from t_test u
        where (u.emp_code = w.emp_code)
        and (u.period_month > w.period_month)
        and (u.company_code  w.company_code)
      ),( select max(v.period_month)
          from t_test v
          where
          (v.emp_code=w.emp_code)
          and (v.period_month > w.period_month)
          and (v.company_code = w.company_code)
         )
    ),( select max(z.period_month)
          from t_test z
          where
          (z.emp_code=w.emp_code)
          and (z.period_month = w.period_month)
          and (z.company_code = w.company_code)
         )
    ),2,'0'
  ) AS to_month
FROM
  ( select *
    from
    (
      select
        y.emp_code, y.company_code, y.period_month, 
        ( select x.company_code
          from t_test x
          where
          (x.emp_code = y.emp_code)
          and (x.period_month  previous_company_code)
  or (previous_company_code is null)
  ) w