3
I have a database that already performed a query and got the following table:
I would like to know how to assign zeroed positions, or replicate subsequent fields that have some defined value, forming for example:
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.
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
– Sorack