</pre><pre class="sql" name="code" snippet_file_name="blog_20160619_3_9123016" code_snippet_id="1721729">select ab.Id id,b.Name name,sum(d) d,sum(m) m,sum(y) y from (
select a.Id,a.payAmount d,0 m,0 y from RetailTrade a where DATE_FORMAT(paytime,'%Y-%m-%d')='2015-05-03'
UNION ALL
select a.Id,0 d,a.payAmount m,0 y from RetailTrade a where DATE_FORMAT(paytime,'%Y-%m')='2015-05'
UNION ALL
select a.Id,0 d,0 m,a.payAmount y from RetailTrade a where DATE_FORMAT(paytime,'%Y')='2015'
) ab inner join Partner b on ab.Id=b.Id group by ab.Id,b.Name order by y desc LIMIT 0,10
mysql 统计每个门店 每天 每月 每年 的订单 消费总额,如下sql是按照每年 取前10select ab.Id id,b.Name name,sum(dc) dc,sum(mc) mc,sum(yc) yc from (select a.Id,a.payAmount dc,0 mc,0 yc from RetailTrade a where DATE_FORMAT(paytime,'