mysql语句优化

最新推荐文章于 2023-07-31 23:13:38 发布

0o树下阳光o0

最新推荐文章于 2023-07-31 23:13:38 发布

阅读量399

点赞数

文章标签： mysql语句优化

本文链接：https://blog.csdn.net/u011069237/article/details/45891085

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

这是年中详细月查询

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');