SQL 日常用法积累

自定义排序

ORDER BY
    (case when d.deliver_status='notify' then '0' when d.deliver_status='reject' then '1' else d.deliver_status end)

多字段模糊查询

SELECT i.city,i.company_name,i.name,i.job,i.dayperweek,i.maxsalary,c.logo_url,c.tags 
FROM intern i INNER JOIN company c ON (c.uuid=i.company_uuid) 
WHERE i.topics ? '2017summer' AND i.city like '%佛山%' AND concat(i.name,i.company_name,i.job) like '%萌想%';

排序对空值的处理 postgresql

select f.id, u.username, f.message, f.uuid, f.praise_num,u.head_url 
from user_feedback f 
INNER JOIN users u ON (u.uuid=f.user_uuid)  
WHERE f.status='2017summer' 
ORDER BY praise_num DESC nulls last

jsonb数据查询 postgresql

select * from testjson where (data->>'a')::int>1;