mysql语句优化

最新推荐文章于 2024-08-23 21:44:26 发布

原创最新推荐文章于 2024-08-23 21:44:26 发布 · 432 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql语句优化

mysql 专栏收录该内容

4 篇文章

订阅专栏

这是年中详细月查询

select DATE_FORMAT(c.orderDate,'%Y') as '年份',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 1  then c.totalAmount else  0 end  ) as '一月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 2  then c.totalAmount else  0 end  ) as '二月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 3  then c.totalAmount else  0 end  ) as '三月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 4  then c.totalAmount else  0 end  ) as '四月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 5  then c.totalAmount else  0 end  ) as '五月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 6  then c.totalAmount else  0 end  ) as '六月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 7  then c.totalAmount else  0 end  ) as '七月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 8  then c.totalAmount else  0 end  ) as '八月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 9  then c.totalAmount else  0 end  ) as '九月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 10  then c.totalAmount else  0 end  ) as '十月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 11  then c.totalAmount else  0 end  ) as '十一月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 12  then c.totalAmount else  0 end  ) as '十二月',
sum(c.totalAmount) as '总金额' 
FROM erp$sales$sales_order c 
LEFT JOIN erp$base$customer k ON c.customer_id=k.id
LEFT JOIN erp$base$customer_category f  ON  k.customer_category_id =f.id 
LEFT JOIN erp$base$area a ON  c.area_id=a.id
where DATE_FORMAT(c.orderDate,'%Y')=2015
UNION ALL select DATE_FORMAT(c.orderDate,'%Y') as '年份',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 1  then c.totalAmount else  0 end  ) as '一月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 2  then c.totalAmount else  0 end  ) as '二月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 3  then c.totalAmount else  0 end  ) as '三月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 4  then c.totalAmount else  0 end  ) as '四月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 5  then c.totalAmount else  0 end  ) as '五月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 6  then c.totalAmount else  0 end  ) as '六月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 7  then c.totalAmount else  0 end  ) as '七月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 8  then c.totalAmount else  0 end  ) as '八月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 9  then c.totalAmount else  0 end  ) as '九月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 10  then c.totalAmount else  0 end  ) as '十月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 11  then c.totalAmount else  0 end  ) as '十一月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 12  then c.totalAmount else  0 end  ) as '十二月',
sum(c.totalAmount) as '总金额' 
FROM erp$sales$sales_order c 
LEFT JOIN erp$base$customer k ON c.customer_id=k.id
LEFT JOIN erp$base$customer_category f  ON  k.customer_category_id =f.id 
LEFT JOIN erp$base$area a ON  c.area_id=a.id
where DATE_FORMAT(c.orderDate,'%Y')=2014

还有别一种更好

select DATE_FORMAT(c.orderDate,'%Y') as '年份',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 1  then c.totalAmount else  0 end  ) as '一月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 2  then c.totalAmount else  0 end  ) as '二月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 3  then c.totalAmount else  0 end  ) as '三月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 4  then c.totalAmount else  0 end  ) as '四月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 5  then c.totalAmount else  0 end  ) as '五月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 6  then c.totalAmount else  0 end  ) as '六月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 7  then c.totalAmount else  0 end  ) as '七月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 8  then c.totalAmount else  0 end  ) as '八月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 9  then c.totalAmount else  0 end  ) as '九月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 10  then c.totalAmount else  0 end  ) as '十月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 11  then c.totalAmount else  0 end  ) as '十一月',
sum( case  DATE_FORMAT(c.orderDate,'%m')  when 12  then c.totalAmount else  0 end  ) as '十二月',
sum(c.totalAmount) as '总金额' 
FROM erp$sales$sales_order c 
LEFT JOIN erp$base$customer k ON c.customer_id=k.id
LEFT JOIN erp$base$customer_category f  ON  k.customer_category_id =f.id 
LEFT JOIN erp$base$area a ON  c.area_id=a.id
where (DATE_FORMAT(c.orderDate,'%Y')=2015 OR DATE_FORMAT(c.orderDate,'%Y')=2014) GROUP BY DATE_FORMAT(c.orderDate,'%Y');