sql语句积累

1.嵌套再进行连接查询

SELECT * from (
SELECT * from tit_training_program 
WHERE level_id=#{levelId}) as p 
LEFT JOIN(
  select * from tit_training_user 
  where user_id=#{userId}) as u 
ON  p.id=u.program_id

2.查询本周内的数据

SELECT
SUM(integral),
DAYOFWEEK(create_time),
case(DAYOFWEEK(create_time))
when 1 then '星期天'
when 2 then '星期一'
when 3 then '星期二'
when 4 then '星期三'
when 5 then '星期四'
when 6 then '星期五'
when 7 then '星期六' 
else '' end
FROM
tik_integral_detail
WHERE
YEARWEEK(
date_format(create_time, '%Y-%m-%d')
) = YEARWEEK(now())
group by Date(create_time)

3.将一个表中的数据批量不重复地导入到另一个表

insert b select * from a where not exists(select 1 from b where id=a.id)