MySql关于数据统计常用的SQL

大家在工作汇总可能需要很多sql,增删改查，有可能没有接触到很难的sql，我们通常要对数据进行统计，比如获取本周，本月，本年的数据，这时候需要查询本月一号，到月末，本年一号，到12月31，随着年份的增长我们不可能给一个固定值，这就需要我们在MySql中使用时间。

1. 获取本周周一

    

 select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) DAY)

2.  获取本月月初

    

select DATE_ADD(curdate(),interval -day(curdate())+1 day)

3.  获取本年的年初

    

select DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY)

 注意：curdate 默认的是目前日期的开始，如果当前日期为 ‘2018-08-17’ 匹配时为‘2018-08-17 00:00:00’，所以不用担心的数据库的时间类型为时间戳，这也是没有问题的

接下来再说一些比较常用的SQL

4. 这是典型的choose when语句

<choose>
    	<when test="dateType!=null and dateType==1">
    		and order_time &gt;= date_sub(curdate(),INTERVAL WEEKDAY(curdate()) DAY)
    	</when>
    	<when test="dateType!=null and dateType==2">
    		and order_time &gt;= DATE_ADD(curdate(),interval -day(curdate())+1 day)
    	</when>
    	<when test="dateType!=null and dateType==4">
    		
    		<choose>
    			<when test="startDate!=null and startDate!='' and endDate!=null and endDate!=''">
    				and order_time &gt;= #{startDate,jdbcType=DATE}
    				and order_time &lt; ADDDATE(#{endDate,jdbcType=DATE},INTERVAL 1 day)
    			</when>
    			<when test="startDate!=null and startDate!='' ">
    				and order_time &gt;= #{startDate,jdbcType=DATE}
    			</when>
    			<when test="endDate!=null and endDate !='' ">
    				and order_time &lt; ADDDATE(#{endDate,jdbcType=DATE},INTERVAL 1 day)
    			</when>
    			<otherwise>
    			</otherwise>
    		</choose>
    	</when>
    	<otherwise>
    	</otherwise>
    </choose>

5. case when 

  如果没有数据,则设置为0

select 
		case when payment_type=1 then ro.total_amount else 0 end as 'cashReceipts',
	  case when payment_type=2 then ro.total_amount else 0 end as 'aliPayReceipts',
		case when payment_type=3 then ro.total_amount else 0 end as 'weChatReceipts',
	  case when payment_type=4 then ro.total_amount else 0 end as 'creditCardReceipts',
	  case when payment_type=5 then ro.total_amount else 0 end as 'othersReceipts'
	FROM
		wsc_retail_order ro

6. 接下来写一个行转列的案例,

  这里有一个场景是,  一张表里面有多条数据,其中有两个字段 一个是paymeny_type和totalAmout,一个是支付的方式,一个是支付的金额,我们来假设有5条数据

id	paymeny_type	totalAmout
1	1	200
2	2	300
3	1	100
4	3	500

业务是,我要查询每一个payment_type对应的totalAmount 返回,还有就是把所有的钱统计.当时想到的是使用group by根据payment_type来分组,这样的话,可以知道每一个payment_type 和累计加起来的totalAmount,但是这样的话就没有根据所有的totalAmount累计加。当然也可以去外面累计加，但是sql既然能完成就用sql吧。

思路：我先去判断每一条数据的payment_type，如果等于1，那么对应的现金就有值，则其余的‘aliPayReceipts’等就全部为0。如下面看到的这样。接下的操作就和大家想的一样了，每一个sum就可以了。

  select 
		sum(a.totalAmount) as 'totalAmount',
		sum(a.orderCount) as 'orderCount',
	    sum(a.cashReceipts) as 'cashReceipts',
	    sum(a.aliPayReceipts) as 'aliPayReceipts',
	    sum(a.weChatReceipts) as 'weChatReceipts',
	    sum(a.creditCardReceipts) as 'creditCardReceipts',
	    sum(a.othersReceipts) as 'othersReceipts'
	from (
	select 
		ro.total_amount as 'totalAmount', 
		case when payment_type=1 then ro.total_amount else 0 end as 'cashReceipts',
	  case when payment_type=2 then ro.total_amount else 0 end as 'aliPayReceipts',
		case when payment_type=3 then ro.total_amount else 0 end as 'weChatReceipts',
	  case when payment_type=4 then ro.total_amount else 0 end as 'creditCardReceipts',
	  case when payment_type=5 then ro.total_amount else 0 end as 'othersReceipts'
	FROM
		wsc_retail_order ro

	) a
    
  </select>

如果能在数据库完成的事情就不要用去写代码去完成，减少开发的成本。

如果大家喜欢我的文章可以关注我的微信公众号，有更多精美的文章。