Merge results in mysql

Asked

Viewed 68 times

3

I have a database that already performed a query and got the following table:

inserir a descrição da imagem aqui

I would like to know how to assign zeroed positions, or replicate subsequent fields that have some defined value, forming for example:

inserir a descrição da imagem aqui

mysql sql database

  • Don’t have any grouper information? And don’t put a picture of the data. It’s really hard to copy to try to help you

1 answer

1


You can control the state of your columns by means of variables, but it is difficult to read with so many variables and there is probably a better way that could be discovered if more information were given.

Using the structure:

CREATE TABLE tabela(
  data DATETIME,
  gw1  INTEGER,
  gw2  INTEGER,
  gw3  INTEGER
);

INSERT INTO tabela(data, gw1, gw2, gw3)
            VALUES('2019-03-19 21:03:11', -72, 0, 0),
                  ('2019-03-19 21:03:18', 0, -80, 0),
                  ('2019-03-19 21:03:26', 0, 0, -45),
                  ('2019-03-19 21:04:26', 0, 0, -50),
                  ('2019-03-19 21:04:26', 0, 0, -100),
                  ('2019-03-19 21:04:26', 0, 10, -50),
                  ('2019-03-19 21:04:26', 30, 0, -50);

To query would look something like this:

SET @gw1 := 0;
SET @gw2 := 0;
SET @gw3 := 0;
SET @grupo := 1;

SELECT x.data,
       x.gw1_final AS gw1,
       x.gw2_final AS gw2,
       x.gw3_final AS gw3
  FROM (SELECT t.data,
               @gw1 := @gw1 + t.gw1 AS gw1_final,
               @gw2 := @gw2 + t.gw2 AS gw2_final,
               @gw3 := @gw3 + t.gw3 AS gw3_final,
               @mudar := CASE
                           WHEN @gw1 = 0 OR @gw2 = 0 OR @gw3 = 0 THEN 'N'
                           ELSE 'S'
                         END,
               @gw1 := CASE @mudar
                         WHEN 'S' THEN 0
                         ELSE @gw1
                       END,
               @gw2 := CASE @mudar
                         WHEN 'S' THEN 0
                         ELSE @gw2
                       END,
               @gw3 := CASE @mudar
                         WHEN 'S' THEN 0
                         ELSE @gw3
                       END
          FROM tabela t
         ORDER BY t.data
       ) x
  WHERE x.gw1_final <> 0
    AND x.gw2_final <> 0
    AND x.gw3_final <> 0;

Resulting in:

| data                | gw1 | gw2 | gw3  |
| ------------------- | --- | --- | ---- |
| 2019-03-19 21:03:26 | -72 | -80 | -45  |
| 2019-03-19 21:04:26 | 30  | 10  | -250 |

See working on DB Fiddle.

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.