mysql-非常实用的sql语句

 一、 名称解释

        常量：将某些查询的结果集当成一个变量，结果出来后直接参与其他计算，不需要再次查询的结果。

 

二、非常实用的sql语句

1、迭代查询

    表结构

CREATE TABLE `product` (
  `id` int(11) NOT NULL auto_increment COMMENT 'id',
  `productName` varchar(100) NOT NULL COMMENT '产品名称',
  `productPrice` int(11) NOT NULL COMMENT '产品价格',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

    测试数据

insert into `product` (`id`, `productName`, `productPrice`) values('1','产品1','23');
insert into `product` (`id`, `productName`, `productPrice`) values('2','产品2','34');
insert into `product` (`id`, `productName`, `productPrice`) values('3','产品3','38');
insert into `product` (`id`, `productName`, `productPrice`) values('4','产品4','45');
insert into `product` (`id`, `productName`, `productPrice`) values('5','产品5','78');
insert into `product` (`id`, `productName`, `productPrice`) values('6','产品1','65');

   

    效果如图：

图1

图2

    从上面的查询结果可以看出，totalprice字段值的规则是从第1条记录到当前记录的totalprice之和，不过这种方式效率并不高，尤其在记录非常多的情况。

    从图2的查询结果分析可知，这个结果仍然是求和的操作，只是并不是对所有的记录求和，也不是分组求和，而是使用迭代的方式进行求和，求和的公式如下：当前 记录的totalprice值= 当前记录的productPrice值+ 上一条记录的totalprice值，上一条记录的totalprice值也可看成是当前记录以前所有记录的productPrice值之和。因此，可以 对每一条记录进行求和(使用sum函数)，不过要求出当前记录及以前的记录的productPrice之和，如下面的SQL语句：

SELECT a.id,a.productPrice,(SELECT SUM(productPrice) FROM product b WHERE b.id <= a.id ) AS totalprice FROM product a;

 

2、sql语句中常量的应用

   创建表

CREATE TABLE `r_statistics_hour` (  
  `id` bigint(50) NOT NULL auto_increment,  
  `createDate` date default NULL,  
  `modifyDate` date default NULL,  
  `timePatternString` varchar(20) default NULL COMMENT '统计时间类型',  
  `salesAmount` double default NULL COMMENT '销售额',  
  `shoppersTraffic` double default NULL COMMENT '客流量',  
  `empWorkHoursPer` double default NULL COMMENT '员工工时比',  
  `avgSalesAmount` double default NULL COMMENT '平均交易额',  
  `turnoverAmountRate` double default NULL COMMENT '成交率',  
  `storeid` int(11) default NULL,  
  `regionId` int(11) default NULL,  
  `turnoverAmount` int(11) default NULL COMMENT '成交量',  
  `empWorkTime` double default NULL COMMENT '员工工时',  
  `businessOpportunities` int(11) default NULL COMMENT '商机(店铺外的人数)',  
  `giveUpRate` double default NULL COMMENT '放弃率',  
  `residenceTime` double default NULL COMMENT '平均停留时间',  
  `transactionScale` double default NULL COMMENT '交易规模',  
  `estimatedVisits` int(11) default NULL COMMENT '预估销售额',  
  `timestring` varchar(32) default NULL,  
  PRIMARY KEY  (`id`),  
  UNIQUE KEY `unique_statistics` (`timestring`,`storeid`)  
) ENGINE=InnoDB AUTO_INCREMENT=30970 DEFAULT CHARSET=utf8 

 参考sql语句

SELECT   
  DATE_FORMAT(  
    CONCAT(timePatternString, '0000'),  
    '%H'  
  ) hours,  
  SUM(salesAmount) salesAmount,  
  AVG(b.salesAmount12) salesAmount12  
FROM  
  r_statistics_hour a,  
  -- 有一个变量的时候该调语句可以作为一个常量，添加到上面的查询语句中   
  (SELECT SUM(salesAmount) salesAmount12 FROM r_statistics_hour WHERE LEFT(timePatternString, 8) >= 20150830 AND LEFT(timePatternString, 8) <= 20150926 AND (storeid = 47 OR regionId = 47)) b  
WHERE LEFT(timePatternString, 8) >= 20150830   
  AND LEFT(timePatternString, 8) <= 20150926  
  AND (storeid = 47 OR regionId = 47)   
GROUP BY DATE_FORMAT(  
    CONCAT(timePatternString, '0000'),  
    '%H'  
)  

 

   3、在sql语句中使用截取字符串(LOCATE(CONCAT(store_id,','),CONCAT(ca.`STOREIDS`,','))>0)和ifnull

SELECT *,
	-- 计算两个日期之间相差的天数
	(TO_DAYS(end_time) - TO_DAYS(start_time) + 1) dayNum,
	-- 获取店铺家数（字段长度-分隔符长度）+ 1
	LENGTH(STOREIDS) - LENGTH(REPLACE(STOREIDS,',',''))+1 storeNum,
	-- 当期客流量除以环比客流量*100保留两位小数，在判断是否为空，如果为空则设置为零
	IFNULL(FORMAT(((enters - entersHb) / entersHb)*100,2),0) rateHb,
	IFNULL(FORMAT(((enters - entersTb) / entersTb)*100,2),0) rateTb,
	FORMAT((SPEND /(enters - entersHb)),2) costAvgHb,
	FORMAT((SPEND /(enters - entersTb)),2) costAvgTb
	FROM (
SELECT
  *,
  -- LOCATE(CONCAT(store_id,','),CONCAT(ca.`STOREIDS`,','))>0 判断store_id 的值是否在ca.`STOREIDS`中; DATE_FORMAT(ca.START_TIME, '%Y%m%d') 日期格式化
  IFNULL((SELECT SUM(ENTERS) FROM t_statistic_day WHERE LOCATE(store_id,CONCAT(ca.`STOREIDS`,','))>0 AND DATE >= DATE_FORMAT(ca.START_TIME, '%Y%m%d') AND DATE <= DATE_FORMAT(ca.END_TIME, '%Y%m%d')),0) enters,
  IFNULL((SELECT SUM(ENTERS) FROM t_statistic_day WHERE LOCATE(store_id,CONCAT(ca.`STOREIDS`,','))>0 AND DATE >= DATE_FORMAT(ca.START_HB_TIME, '%Y%m%d') AND DATE <= DATE_FORMAT(ca.END_HB_TIME, '%Y%m%d')),0) entersHb,
  IFNULL((SELECT SUM(ENTERS) FROM t_statistic_day WHERE LOCATE(store_id,CONCAT(ca.`STOREIDS`,','))>0 AND DATE >= DATE_FORMAT(ca.START_TB_TIME, '%Y%m%d') AND DATE <= DATE_FORMAT(ca.END_TB_TIME, '%Y%m%d')),0) entersTb
FROM
  t_campaigns ca 
WHERE STAFF_ID = 1) tempA

    说明：LOCATE(CONCAT(store_id,','),CONCAT(ca.`STOREIDS`,','))>0为store_id存在ca.`STOREIDS`字符串中。

 

4、用时间叔来当条件时间来比较，把当前时间数据和对比时间数据来比较

-- sql的意识是得到当前日期、当前日期总客流量、当前日期个店平均客流量，对比日期客流量、客流变化率
SELECT DATE_FORMAT(dq.date,'%Y/%m/%d') currentDate,dq.enters enters,dq.entersAvg entersAvg,DATE_FORMAT(tb.date,'%Y/%m/%d') contrastDate,tb.enters entersTb, 
IFNULL(FORMAT(((dq.enters - tb.enters)/tb.enters)*100,1),0) rateTb FROM 

	(SELECT st.date,SUM(enters) enters,FORMAT(SUM(enters)/ca.storeNum,0) entersAvg FROM 
		t_statistic_day st,
		(SELECT *,LENGTH(STOREIDS) - LENGTH(REPLACE(STOREIDS,',',''))+1 storeNum FROM t_campaigns WHERE id = 1) ca 
	WHERE LOCATE(st.store_id,CONCAT(ca.storeids,',')) > 0 AND st.date >= DATE_FORMAT(ca.START_TIME,'%Y%m%d') 
	AND st.date <= DATE_FORMAT(ca.END_TIME,'%Y%m%d') GROUP BY DATE) dq 
LEFT JOIN 
   
	(SELECT st.date,SUM(enters) enters FROM 
		t_statistic_day st,
		(SELECT * FROM t_campaigns WHERE id = 1) ca 
	WHERE LOCATE(st.store_id,CONCAT(ca.storeids,',')) > 0 
	AND st.date >= DATE_FORMAT(ca.START_TB_TIME,'%Y%m%d') 
	AND st.date <= DATE_FORMAT(ca.END_TB_TIME,'%Y%m%d') 
	GROUP BY DATE) tb 

	-- 当前日期与对比日期之间相差的天数为一个固定值，
	-- 364是拿当前开始时间和对比开始时间计算得出的相隔天数
ON DATEDIFF(dq.date,tb.date) = 364

 

5、mysql类似rownum的查询语句

SELECT @rownum:=@rownum+1 rownum,s.* FROM  (SELECT @rownum:=0) r,t_deal_sum s

 

 6 获取时间差、在时间基础上加上一个时间

     6.1 TIMESTAMPDIFF

         语法：

              TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)。

         说明：

               返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由       interval 参数给出。该参数必须是以下值的其中一个：

          参数：

              FRAC_SECOND 表示间隔是毫秒

              SECOND  秒

              MINUTE  分钟

              HOUR  小时

              DAY  天

              WEEK  星期

              MONTH  月

              QUARTER  季度

              YEAR  年

mysql> select TIMESTAMPDIFF(day,'2012-08-24','2012-08-30');  
+----------------------------------------------+  
| TIMESTAMPDIFF(day,'2012-08-24','2012-08-30') |  
+----------------------------------------------+  
|                                            6 |   
+----------------------------------------------+  
1 row in set (0.00 sec)

 

    6.2 TIMESTAMPADD

        语法：

TIMESTAMPADD(interval,int_expr,datetime_expr)

        说明：

                将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中。式中的interval和上文中列举的取值是一样的。

mysql> select TIMESTAMPADD(MINUTE,8820,'2012-08-24 09:00:00');  
+-------------------------------------------------+  
| TIMESTAMPADD(MINUTE,8820,'2012-08-24 09:00:00') |  
+-------------------------------------------------+  
| 2012-08-30 12:00:00                             |   
+-------------------------------------------------+  
1 row in set (0.00 sec)

 

    例1：

 

-- 获取时间差
SELECT CONCAT(TIMESTAMPDIFF(DAY,'2016-09-21 10:17:57','2016-12-21 11:17:57'),'天',TIMEDIFF('11:17:57','10:17:57')) day1; 

    结果：

    例2：

-- 将时间转换为天、时、分 
 SELECT CONCAT(
	FLOOR(HOUR(SEC_TO_TIME(TIMESTAMPDIFF(SECOND,'2016-07-21 08:46:00','2016-12-01 12:30')))/24), ' 天 ',
	MOD(HOUR(SEC_TO_TIME(TIMESTAMPDIFF(SECOND,'2016-07-21 08:46:00', '2016-12-01 12:30'))),24),' 时 ',
	MINUTE(SEC_TO_TIME(TIMESTAMPDIFF(SECOND,'2016-07-21 08:46:00', '2016-12-01 12:30'))),' 分'
) str2;

   结果：