sql 遇到的场景问题的解决方案笔记

//取记录的前几条数据
substring_index(字符串，分隔符，计数）
substring_index('www.baidu.com','.',1)
输出：www

//查看某个字符出现的次数
CHAR_LENGTH(字段)-CHAR_LENGTH(REPLACE(字段,'统计的字符',''))


SELECT
	SUBSTRING_INDEX( str, '-', count ) AS '去掉最后一个‘-’后的内容'
FROM
	(
	SELECT
		'343-43-431' str,
		CHAR_LENGTH( '343-43-431' ) - CHAR_LENGTH( REPLACE ( '343-43-431', '-', '' ) ) AS count  UNION ALL
	SELECT
		'343-43-431' str,
	CHAR_LENGTH( '343-43-431-34' ) - CHAR_LENGTH( REPLACE ( '343-43-431', '-', '' ) ) AS count 
	
	) a
输出：	343-43
		343-43-431

//在group by函数中，展示分组内部的信息
group_concat（聚合内容）
group_concat（内容，order by 排序字段 升降序)

//返回指定时间到当前时间的天数
SELECT datediff(now(),'1997-12-30') 

//求某一年+当前月日 到 不同年某一年某一月某一日的天数
//比如当前时间为2022-05-23，要求2018-05-23到2018-05-22的天数 
SELECT DATEDIFF((DATE_SUB(now(),INTERVAL 3 year)),"2018-05-22")

//SQL server 将两个int类型的字段数据拼接后转换为日期格式
cast ((convert(varchar,FYear)+'-'+convert(varchar,FMonth)+'-01') as DateTime) as cDate

//连接用using   
select  * from 表1 inner join 表2 using（两表相同的字段）

三大排序函数：
row_number():对于每一条记录生成不重复的id，每次+1
rank():当n条记录相同时，序号相同，下次序号+n
dense_rank():多条记录相同时，序号相同，下次序号+1

--mysql使用<>时，会将null也过滤，要先将null转为非null
SELECT * FROM test WHERE IFNULL(xxname,'') != 'xxx'

union all 会按照顺序将各个字段合起来，所以每个union all的表的字段要顺序要相同

--开窗函数求空档期，MySQL8.0以上版本适用
--lead()方法：取当前数据的下一条数据 test
--创建表和数据
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `Mdate datetime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO  test(id,Mdate) VALUES
 ('1',  '2021-11-01'),
 ('1',  '2022-01-01'),
 ('1',  '2022-01-15'),
 ('1',  '2022-02-01'),
 ('1',  '2022-02-03'),
 ('1',  '2022-03-01'),
 ('1',  '2022-03-05'),
 ('1',  '2022-03-18'),
 ('2',  '2021-11-01'),
 ('2',  '2022-01-01'),
 ('2',  '2022-01-15'),
 ('2',  '2022-02-01'),
 ('3',  '2022-02-03'),
 ('3',  '2022-03-01'),
 ('4',  '2022-03-05'),
 ('5',  '2022-03-18');
	SELECT
	  	 id,
	 	 开始时间,
	 	 下一次订单时间,
		 datediff(下一次订单时间,开始时间) as 间隔天数
	from(	
	SELECT
	 id,
	 Mdate  as 开始时间,
	 lead ( Mdate, 1, NOW() ) OVER ( partition BY id ORDER BY Mdate ASC ) 下一次订单时间 
	FROM test )T
--在上面的基础上求最大空档期

--mysql版本不是8.0以上的
	SELECT
		T1.id as id,
		T1.Mdate 开始时间,
		T2.Mdate AS 下一次订单时间,
		MIN( DATEDIFF( T2.Mdate, T1.Mdate ) ) 间隔天数 
	FROM
		test T1
		INNER JOIN ( 
			SELECT 
				id,Mdate 
			FROM 
				test
			UNION ALL 
			SELECT DISTINCT
				id,NOW( ) AS Mdate
			FROM test
		) T2 ON T1.id = T2.id 
		AND T1.Mdate < T2.Mdate 
	GROUP BY
		T1.id,T1.Mdate

--按照季度汇总
--返回不大于X的最大整数值  FLOOR(3/2) 则返回1 
 SELECT
 	sum( Money ) AS 金额,
 	concat(date_format( dDate, '%Y' ),'-', FLOOR( ( date_format( dDate, '%m' ) + 2 ) / 3 )) as "年-季"
 FROM
 	youTable
 GROUP BY
 	concat( date_format( dDate, '%Y' ), FLOOR( ( date_format( dDate, '%m' ) + 2 ) / 3 ) );