Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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);
- 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;
- | data | gw1 | gw2 | gw3 |
- | ------------------- | --- | --- | ---- |
- | 2019-03-19 21:03:26 | -72 | -80 | -45 |
- | 2019-03-19 21:04:26 | 30 | 10 | -250 |
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement