Advertisement
Guest User

Untitled

a guest
Mar 19th, 2019
68
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.72 KB | None | 0 0
  1. CREATE TABLE tabela(
  2. data DATETIME,
  3. gw1 INTEGER,
  4. gw2 INTEGER,
  5. gw3 INTEGER
  6. );
  7.  
  8. INSERT INTO tabela(data, gw1, gw2, gw3)
  9. VALUES('2019-03-19 21:03:11', -72, 0, 0),
  10. ('2019-03-19 21:03:18', 0, -80, 0),
  11. ('2019-03-19 21:03:26', 0, 0, -45),
  12. ('2019-03-19 21:04:26', 0, 0, -50),
  13. ('2019-03-19 21:04:26', 0, 0, -100),
  14. ('2019-03-19 21:04:26', 0, 10, -50),
  15. ('2019-03-19 21:04:26', 30, 0, -50);
  16.  
  17. SET @gw1 := 0;
  18. SET @gw2 := 0;
  19. SET @gw3 := 0;
  20. SET @grupo := 1;
  21.  
  22. SELECT x.data,
  23. x.gw1_final AS gw1,
  24. x.gw2_final AS gw2,
  25. x.gw3_final AS gw3
  26. FROM (SELECT t.data,
  27. @gw1 := @gw1 + t.gw1 AS gw1_final,
  28. @gw2 := @gw2 + t.gw2 AS gw2_final,
  29. @gw3 := @gw3 + t.gw3 AS gw3_final,
  30. @mudar := CASE
  31. WHEN @gw1 = 0 OR @gw2 = 0 OR @gw3 = 0 THEN 'N'
  32. ELSE 'S'
  33. END,
  34. @gw1 := CASE @mudar
  35. WHEN 'S' THEN 0
  36. ELSE @gw1
  37. END,
  38. @gw2 := CASE @mudar
  39. WHEN 'S' THEN 0
  40. ELSE @gw2
  41. END,
  42. @gw3 := CASE @mudar
  43. WHEN 'S' THEN 0
  44. ELSE @gw3
  45. END
  46. FROM tabela t
  47. ORDER BY t.data
  48. ) x
  49. WHERE x.gw1_final <> 0
  50. AND x.gw2_final <> 0
  51. AND x.gw3_final <> 0;
  52.  
  53. | data | gw1 | gw2 | gw3 |
  54. | ------------------- | --- | --- | ---- |
  55. | 2019-03-19 21:03:26 | -72 | -80 | -45 |
  56. | 2019-03-19 21:04:26 | 30 | 10 | -250 |
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement