SQL intern 29题记录及心得

• 表结构
  

21、豹子手机号用户（4个连续数字，如6666）和非豹子号用户的笔均消费金额分别是多少？

with b as(
select usr_id,
  case
   when phone_num REGEXP '[0-9](?=\\1{3})' THEN 'Leopard' `
   else ' no-Leopard'`
  end as is_豹子
from id_inf
)
select avg(a.trx_amt),is_豹子
from trx_rcd a
left join b
on a.usr_id=b.usr_id
group by is_豹子

关于表连接的题目
情况1：A去过，但B没有去过

SELECT DISTINCT a.mch_nm  
FROM trx_rcd a  
LEFT JOIN trx_rcd b ON a.mch_nm = b.mch_nm AND b.usr_id = '4066802156346859215'  
WHERE a.usr_id = '3581980399641129' AND b.usr_id IS NULL;

关于连接是指把a列全部跟b列匹配，不会把表格合并，只是粘在一起，由于left所以b表中匹配不上的都是用null表示，and是对表b进行调整，有没有and影响的是null的取法
情况2：A、B都去过

SELECT DISTINCT a.mch_nm  
FROM trx_rcd a  
（inner） JOIN trx_rcd b ON a.mch_nm = b.mch_nm
WHERE a.usr_id = '3581980399641129' AND b.usr_id='4066802156346859215'

ST_Distance_Sphere函数来计算地球上两点之间的大圆距离

SELECT u.usr_id,s.mch_nm
FROM log_loc u  
JOIN mch_loc s ON 1 = 1
WHERE s.mch_nm = '屈臣氏东门中路店'  
  AND ST_Distance_Sphere(  
        POINT(u.lon, u.lat),  
        POINT(s.lon, s.lat)  
      ) < 500;