sql趣味题

用sql实现从0累加到100

with recursive cte(id,val) as (
	values(0,0)
	union all
	select (id+1),((id+1)+val)
	from cte where id<=100
)select val from cte where id=100;

结果为5050

十进制转换为二进制

#基本原理(负数时有bug)
with recursive cte(val,remainder) as (
	values(255,255%2)
	union all
	select val,remainder from (select (val/2) as val, ((val/2)%2) as remainder from cte) as tmp where val>0
),tmp as(
	select remainder::text as b from cte order by val
)select string_agg(b,'') from tmp

#使用位操作符代替SQL,正负数均可
#基本原理
#	右移1位相当于除以2 右移2位除以4
#	左移1位等于乘2 左移2位等于乘4
with recursive cte(id,val,bit) as (
	values(31,5,(5>>31)&1)
	union all
	select id-1,val,(val>>(id-1))&1 from cte where id>=1
)select * from cte;

with recursive cte(id,val,bit) as (
	values(31,-1, (case when 0=((-1>>31)&1)  then '0' else '1' end) )
	union all
	select id-1,val,(case when 0=((val>>(id-1))&1)  then '0' else '1' end) from cte where id>=1
)select string_agg(bit,'') from cte;


with recursive cte(id,val,bit) as (
	values(31,-2, (case when 0=((-2>>31)&1)  then '0' else '1' end) )
	union all
	select id-1,val,(case when 0=((val>>(id-1))&1)  then '0' else '1' end) from cte where id>=1
)select string_agg(bit,'') from cte;

结果为11111111

十进制转换为八进制

with recursive cte(val,remainder) as (
	values(255,255%8)
	union all
	select val,remainder from (select (val/8) as val, ((val/8)%8) as remainder from cte) as tmp where val>0
),tmp as(
	select remainder::text as b from cte order by val
)select string_agg(b,'') from tmp

结果为377

十进制转换为十六进制

with recursive cte(flags,val,remainder) as (
	values(array['0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'], 255,255%16)
	union all
	select * from (select flags,(val/16) as val, ((val/16)%16) as remainder from cte) as tmp where val>0
),tmp as(
	select flags,remainder+1 as b from cte order by val
)select string_agg(flags[b],'') from tmp

结果为FF