MYSQL: Find subsequences in a sequence and group by ID

Question

MYSQL: Find subsequences in a sequence and group by ID

Navigation

#1 by (1 votes)

0

I have a table with the following result:

------------------
|seq  |   ID     |
------------------
| 1   |  12345   |
| 2   |  12345   |
| 3   |  12345   |
| 4   |  12345   |
| 8   |  12345   |
| 9   |  54321   |
| 10  |  54321   |
| 11  |  54321   |
| 12  |  54321   |
| 13  |  54321   |
| 14  |  12345   |
| 15  |  12345   |
------------------

And right now I'm using the following query:

SELECT min(cardinals.seq) as start, max(cardinals.seq) as stop, r.PART_NUMBER --tao em uso no codigo
  FROM (
        SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq  
          FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
          JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F
           ) cardinals
  JOIN (
             SELECT PART_NUMBER, MIN(SERIAL_NUMBER) as minSeq, MAX(SERIAL_NUMBER) as maxSeq
               FROM  manifa6_import_manif
               where wu_name='FT4WU9C7'  
              GROUP BY PART_NUMBER
       ) r ON cardinals.seq >= r.minSeq AND cardinals.seq <= r.maxSeq
  LEFT JOIN  manifa6_import_manif ON cardinals.seq =  manifa6_import_manif.SERIAL_NUMBER AND r.PART_NUMBER = manifa6_import_manif.part_number
 WHERE  manifa6_import_manif.SERIAL_NUMBER IS NULL

The result of this query is NULL

I need you to give me the following result:

 -------------------------
 |start  | Stop   | ID   |
 -------------------------
 |  1    |  4     |12345 |
 |  8    |  8     |54321 |
 |  9    |  13    |54321 |
 |  14   |  15    |12345 |
 -------------------------

Can you help me? Thanks

mysql sql query

asked by anonymous 27.05.2016 / 17:04

1 answer

How to update the view after changing the database with IONIC 2 cascade delete with entity framework

score 1 · Accepted Answer

The following is the SQL DDL job:

CREATE DATABASE stackoverflow ;

CREATE TABLE 'manifa6_import_manif' (
  'seq' int(11) NOT NULL AUTO_INCREMENT,
  'ID' int(11) DEFAULT NULL,
  'PART_NUMBER' varchar(45) DEFAULT NULL,
  'SERIAL_NUMBER' int(11) DEFAULT NULL,
  'wu_name' varchar(45) DEFAULT NULL,
  PRIMARY KEY ('seq')
);

And as a response, the SQL DML follows:

INSERT INTO 'manifa6_import_manif' ('seq','ID','PART_NUMBER','SERIAL_NUMBER','wu_name') 
VALUES  (1,12345,'333',1,NULL),
        (2,12345,'333',2,'FT4WU9C7'),
        (3,12345,'333',3,NULL),
        (4,12345,'333',4,NULL),
        (8,54321,'444',8,'FT4WU9C7'),
        (9,54321,'555',9,'FT4WU9C7'),
        (10,54321,'555',10,NULL),
        (11,54321,'555',11,NULL),
        (12,54321,'555',12,NULL),
        (13,54321,'555',13,'FT4WU9C7'),
        (14,12345,'666',14,'FT4WU9C7'),
        (15,12345,'666',15,NULL);


SELECT 
    MIN(mim1.SERIAL_NUMBER) as 'start', 
    MAX(mim1.SERIAL_NUMBER) as 'Stop',
    mim1.ID
FROM  manifa6_import_manif mim1
-- where wu_name='FT4WU9C7'  
GROUP BY mim1.PART_NUMBER;

NOTE : @Gomez , I had to infer, and had which populates the 'manifa6_import_manif' field. 'PART_NUMBER' with data that enables grouping according to the GROUP BY PART_NUMBER clause. I hope in the application it is possible to use the query as a solution.

Personal, for database queries it would be helpful to put the DDL SQL to create the possible working tables.

Reference:

[Paul Dubois at al, 2005], MySQL® 5.0 : Certification Study Guide

p>