mysql group by using the range record value

In SQL I have table t_test below:

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

I want to query with result below:

emp_code | company_code | from_month  | to_month 
--------------------------------------------------
NIK001   | ALPHA        | 01          | 05
NIK001   | BETA         | 06          | 09
NIK001   | ALPHA        | 10开发者_JS百科          | 12

This makes me tremble in fear, but it does output an approximation of what you're after:

    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

However, I tried this just out of curiosity. You shouldn't really rely on anything like that for your production environment. I don't think that a simple SQL query is a good fit for your problem. I would look elsewhere.

EDIT: Based on the following table:

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

Filled with the following INSERT statements:

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');

You could use a MySQL variable. Eg:

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;

On my test data this gives me:

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

The idea is that on every line you check if the current company_code is equal to that of the previous record. If it's different, increase the variable.

That's a step forward. But how to group the rows? You can't group them directly, but you need to wrap that query with a second one.

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
;

That's using the result from the inner query, in the outer query. This gives me, with my test data,

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

If you need to limit the selection of rows, do so in a where clause in the inner query, or else the whole database will be loaded into memory on each query. Another gotcha is that the inner query is fetching the previous record as "the record that has the closest lower period_month. As an effect, you must not sort the inner query by anything but period_month ASC. You can still sort things in the outer query if you want.

Finally, if you're using the old mysql_query interface for some reason, you need to put the SET line in a separate query, as it can only handle one query at a time.

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
;");

Not sure how well this method works out when scaled up to large databases, but it definitely works. Maybe there's a "good" way. Just make sure you don't accidentally mess something up in the query, in case you don't fully understand it. :)

@magma : thnks a lot for the answer. good job! thanks..

I just made a little modification so the from_month and to_month will never get NULL value.

To speed up when execute query, i create temporary table with session to store the query result, and use the temporary data for producing report

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