Infernale

Symmetric Pairs [NCTU Floor 27]

Dec 21st, 2019
425
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 0.64 KB | None | 0 0
  1. # 14) Swap(lat, lon) by ID parity check
  2. UPDATE citytable
  3.     SET lat = lat + lon, lon = lat-lon, lat = lat - lon
  4.     WHERE CASE WHEN 'od' = 'ev' THEN id % 2 = 0
  5.                ELSE id % 2 = 1 END;
  6.    
  7. # 15) SET lat = lon where ones place of ID is m
  8. UPDATE citytable
  9.     SET lat = lon
  10.     WHERE id % 10 = 6;
  11.  
  12. # 16) Query kth symmetric pair
  13. #Format : kth = LIMIT k-1, 1
  14. SELECT f1.X, f1.Y
  15.     FROM (SELECT ROUND(lat, 1) X, ROUND(lon, 1) Y
  16.         FROM citytable) f1
  17.             INNER JOIN
  18.          (SELECT ROUND(lat, 1) X, ROUND(lon, 1) Y
  19.         FROM citytable) f2
  20.             ON f1.X=f2.Y AND f1.Y=f2.X
  21.     GROUP BY f1.X, f1.Y
  22.     HAVING COUNT(f1.X) > 1 or f1.X < f1.Y
  23.     ORDER BY f1.X, f1.Y LIMIT 1, 1;
Add Comment
Please, Sign In to add comment