Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- * Some notes when solving Hackerrand SQL problem
- * Based on mySQL
- ```sql
- --- REGEXP for pattern matching in my SQL
- X REGEXP '^[abc]'
- --- '^[^abc]', if do not want to start with abc
- ORDER BY X ASC, Y DESC LIMIT 1
- CHAR_LENGTH(X)
- LEFT(X,1) in ('a','b','c')
- RIGHT(X,1)
- SUBSTRING(X,-5,5)
- --- user variables
- set @x := 0
- set @y := (select count(1) from T) --- use select will print y
- --- change itself in while
- while (select @x:=@x+1) --- run row by row?
- between @x/2 and @x/2+1
- --- or change itself in select (add an extra column)
- select avg(T.price)
- from (
- select data, @x := @x+1 as haha from T --- run row by row
- ) T
- where T.haha in (a, b)
- --- group by
- --- first group, then order the grouped item
- select count(x) as cnt from data group by x order by cnt
- --- group by using the newly generated column
- select a*b as x, count(x) from data group by x limit 1;
- --- same functions work for digits and strings
- replace(x,' 0','') -- remove 0 in some numbers
- min(x) --- or max(x), choose non-null if possible
- --- join condition for rows, =, between
- from data1 inner join data2 on data1.x between data2.y and data2.z
- --- a table inside every MySQL instance
- select repeat('* ',@row:=@row+1) from information_schema.tables
- ```
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement