SHARE
TWEET

Untitled

a guest Oct 14th, 2019 81 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. * Some notes when solving Hackerrand SQL problem
  2. * Based on mySQL
  3.  
  4. ```sql
  5. --- REGEXP for pattern matching in my SQL
  6. X REGEXP '^[abc]'
  7.  
  8. --- '^[^abc]', if do not want to start with abc
  9. ORDER BY X ASC, Y DESC LIMIT 1
  10. CHAR_LENGTH(X)
  11. LEFT(X,1) in ('a','b','c')
  12. RIGHT(X,1)
  13. SUBSTRING(X,-5,5)
  14.  
  15. --- user variables
  16. set @x := 0
  17. set @y := (select count(1) from T) --- use select will print y
  18.  
  19. --- change itself in while
  20. while (select @x:=@x+1) --- run row by row?
  21. between @x/2 and @x/2+1
  22.  
  23. --- or change itself in select (add an extra column)
  24. select avg(T.price)
  25. from (
  26. select data, @x := @x+1 as haha from T --- run row by row
  27. ) T
  28. where T.haha in (a, b)
  29.  
  30. --- group by
  31. --- first group, then order the grouped item
  32. select count(x) as cnt from data group by x order by cnt
  33.  
  34. --- group by using the newly generated column
  35. select a*b as x, count(x) from data group by x limit 1;
  36.  
  37. --- same functions work for digits and strings
  38. replace(x,' 0','') -- remove 0 in some numbers
  39. min(x) --- or max(x), choose non-null if possible
  40.  
  41. --- join condition for rows, =, between
  42. from data1 inner join data2 on data1.x between data2.y and data2.z
  43.  
  44. --- a table inside every MySQL instance
  45. select repeat('* ',@row:=@row+1) from information_schema.tables
  46. ```
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