Guest User

133

a guest
Mar 27th, 2020
185
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH
  2. minComp
  3. AS
  4. (
  5. SELECT MIN(ID_comp) AS ID_comp
  6. FROM Trip
  7. ),
  8. maxComp
  9. AS
  10. (
  11. SELECT MAX(ID_comp) AS ID_comp
  12. FROM Trip
  13. ),
  14. genUp
  15. AS
  16. (
  17. SELECT
  18. (
  19. SELECT ID_comp
  20. FROM minComp
  21. ) AS ID_comp
  22. UNION ALL
  23. SELECT ID_comp + 1 AS ID_comp
  24. FROM genUp
  25. WHERE ID_comp + 1 <=
  26. (
  27. SELECT ID_comp
  28. FROM maxComp
  29. )
  30. ),
  31. compAndHill
  32. AS
  33. (
  34. SELECT DISTINCT lt.ID_comp, rt.ID_comp AS hill
  35. FROM trip lt
  36. CROSS JOIN genUp rt
  37. ),
  38. corectCompAndHill
  39. AS
  40. (
  41. SELECT ID_comp, hill
  42. FROM compAndHill
  43. WHERE hill <= ID_comp
  44. ),
  45. corectCompAndHillNoEql
  46. AS
  47. (
  48. SELECT ID_comp, hill
  49. FROM compAndHill
  50. WHERE hill < ID_comp
  51. ),
  52. compTwoHills
  53. AS
  54. (
  55. SELECT lt.ID_comp, STRING_AGG(lt.hill,'') WITHIN GROUP(ORDER BY lt.ID_comp) AS hill1, STRING_AGG(REVERSE(rt.hill),'') WITHIN GROUP(ORDER BY lt.ID_comp) AS hill2
  56. FROM corectCompAndHill lt
  57. LEFT JOIN corectCompAndHillNoEql rt ON lt.ID_comp = rt.ID_comp AND lt.hill = rt.hill
  58. GROUP BY lt.ID_comp
  59. )
  60. SELECT ID_comp, CONCAT(hill1, REVERSE(hill2)) AS hill
  61. FROM compTwoHills
RAW Paste Data