SHARE
TWEET

133

a guest Mar 27th, 2020 158 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top