9-9-01SQL-总结

1 SQL技能总结

1.1 时间

1.1.1 时间连续

/*
时间序列：
	将表t的数据b的字段扩充范围[2018-01-01,2018-01-07]，并累积对c求和
	b字段的值较稀疏的
*/

CREATE DATABASE wmy DEFAULT CHARSET=utf8;

CREATE TABLE wmy.test(
`a` VARCHAR(20),
`b` VARCHAR(20),
`c` VARCHAR(20)
)DEFAULT CHARSET=utf8;

INSERT INTO test
VALUES
('101','2018-01-01',10),
('101','2018-01-03',20),
('101','2018-01-06',40),
('102','2018-01-02',20),
('102','2018-01-04',30),
('103','2018-01-07',60);

SELECT * FROM test;

SELECT 
	a,df as b,c,SUM(c) OVER(PARTITION BY a ORDER BY df) as d
FROM
(
SELECT
	a,df,
	SUM(IF(df=b,c,0)) c
FROM
	(
	SELECT 
		t1.a,df,b,c
	FROM 
		(
			SELECT '101' a,
			DATE_SUB('2018-01-07',INTERVAL t.help_topic_id  DAY) AS df
			FROM mysql.help_topic t
			WHERE t.help_topic_id <= 6
			UNION ALL 
			SELECT '102',
			DATE_SUB('2018-01-07',INTERVAL t.help_topic_id  DAY) AS df
			FROM mysql.help_topic t
			WHERE t.help_topic_id <= 6
		)t1
		LEFT JOIN 
		(
			SELECT 
			a,b,c
			FROM test
		)t2
		ON t1.a=t2.a
	)t3
GROUP BY a,df
ORDER BY a,df
)t4

需要掌握的知识点

CREATE TABLE test2(id INT PRIMARY KEY AUTO_INCREMENT,curr_date DATETIME);
INSERT INTO test2(curr_date) VALUES('2019-08-11 10:12:30'),('2019-08-14 10:12:30'),('2019-08-16 10:12:30');
SELECT * FROM test2;


#########################################
SELECT DATE_FORMAT(curr_date,'%Y-%m-%d') AS 'curr_date',COUNT(id) AS 'record_count' 
FROM wmy.test2 GROUP BY DATE_FORMAT(curr_date,'%Y-%m-%d');

#########################################
SELECT MAX(help_topic_id),MIN(help_topic_id),COUNT(help_topic_id) FROM mysql.help_topic;

#########################################
SELECT DATE_FORMAT(DATE_ADD('2019-08-20',INTERVAL -t.help_topic_id DAY),'%Y-%m-%d') AS 'curr_date'  
FROM mysql.help_topic t WHERE t.help_topic_id<=10;

#########################################
SELECT t1.curr_date,IFNULL(t2.record_count,0) AS record_count FROM  
(   
SELECT DATE_FORMAT(DATE_ADD('2019-08-20',INTERVAL -t.help_topic_id DAY),'%Y-%m-%d') AS 'curr_date'  
FROM mysql.help_topic t WHERE t.help_topic_id<=10  
) t1
LEFT JOIN 
(   
SELECT DATE_FORMAT(curr_date,'%Y-%m-%d') AS 'curr_date',COUNT(id) AS 'record_count' 
FROM test2 GROUP BY DATE_FORMAT(curr_date,'%Y-%m-%d')     
) t2
ON t1.curr_date=t2.curr_date
ORDER BY t1.curr_date;