CODEWITHSUNDEEP
mysql
Dzung Nguyen
Dzung Nguyen

Reputation: 113

How can I query info from a special table structure in MySQL?

I got a problem with a query or a table in MySQL and i spent so much time to solve it, but i still have no answer. So i need your help.

The format of the table is something like that

-------------------------------------------------------------
col1   |   col2   |   col3   |   col4  |  col5   |   col6   |
-------------------------------------------------------------
   a   |     b    |    c     | null    |   null  |   null   |
-------------------------------------------------------------
 null  |   null   |   null   |   1     |    2    |    3     |
-------------------------------------------------------------
  null |   null   |   null   |   4     |    5    |    6     |
-------------------------------------------------------------
  null |   null   |   null   |   7     |    8    |    9     |
-------------------------------------------------------------
  d    |    e     |    f     |   null  |   null  |   null   |
-------------------------------------------------------------
  null |   null   |   null   |   10    |   11    |    12    |
-------------------------------------------------------------
  null |   null   |   null   |   13    |   14    |    15    |
-------------------------------------------------------------
  null |   null   |   null   |   16    |   17    |    18    |
-------------------------------------------------------------
...

The table has a structure like that because it loads data from a big-size csv file (about 300k lines). All i want is write a mysql statement that query records in the fastest time that it can and its result is in format:

(a,b,c,1,2,3),(a,b,c,4,5,6),(a,b,c,7,8,9),(d,e,f,10,11,12),(d,e,f,13,14,15),(d,e,f,16,17,18) ...

But ... how? Please help me to solve the problem. Thks in advance

Upvotes: 0

Views: 100

Answers (1)

lqez
lqez

Reputation: 3008

Looks ugly, but works.

SELECT * FROM (
SELECT
    IF(col1 IS NULL,@c1,@c1:=col1) AS col1, 
    IF(col2 IS NULL,@c2,@c2:=col2) AS col2, 
    IF(col3 IS NULL,@c3,@c3:=col3) AS col3, 
    col4, col5, col6
FROM
    [table name],
    ( SELECT @c1:=0, @c2:=0, @c3:=0 ) x 
) y WHERE col4 IS NOT NULL;

Upvotes: 2

Related Questions