/**查询近两周销量，按照日期分组**/
select tpdate.dateStr,ifnull(tpdata.count,0) as count from
(SELECT @rownum:=@rownum+1 as count,date_add(DATE_FORMAT(now(),'%Y-%m-%d'),INTERVAL -@rownum day) as dateStr from (select @rownum:=-1)r,记录大于13条的任意表
where @rownum < 13) tpdate
left join (select sum(xxxtable.amount) as amount,DATE_FORMAT(sub.create_time,'%Y-%m-%d') as dateStr from xxxxtable where
xxid=#{xxid} and create_time > date_add(now(),interval -13 day) and status in(x,x,x,x,x)
group by dateStr) tpdata on tpdata.dateStr=tpdate.dateStr order by dateStr
/**在父子节点的一张表中查找某条记录的根节点**/
/**根据输入的id查询根节点id**/
select IFNULL(@head_id,输入的id) from
(
select @head_id:=if(xxxtable.id=@head_id and xxxtable.parent_id>0,xxxtable.parent_id,@head_id)
from (select @head_id:=输入的id)r,xxxtable order by xxxtable.id desc
)rr limit 1;