SQL分析——常见问题十(日期查询)

最新推荐文章于 2023-12-26 16:43:43 发布

吴声子夜歌

最新推荐文章于 2023-12-26 16:43:43 发布

阅读量254

点赞数 2

分类专栏： SQL 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/cold___play/article/details/129966569

版权

SQL 专栏收录该内容

33 篇文章 10 订阅

订阅专栏

一、均等分问题

CREATE TABLE F0519( 
	Type VARCHAR(10), 
	Num INT  
); 

INSERT INTO F0519 VALUES('A',2); 
INSERT INTO F0519 VALUES('B',3); 
INSERT INTO F0519 VALUES('C',2);  

-- 要求：将每个Type按Num均等分,如下图所示

在这里插入图片描述

-- sql server 

-- spt_values select * from master.dbo.spt_values where Type='P
SELECT a.Type, a.Num, b.number, 1 Num  
FROM F0519 a 
JOIN master.dbo.spt_values b 
ON b.Type='P' AND a.Num>b.Number  



-- mysql
/* 
	help_topic本身是Mysql一个帮助解释注释表，用于解释Mysql各种专有名词，由于这张表数据ID是从0顺序增加的，方便我们用于计数，但是8.0.17版本的只有686条数据，超过这个数字，我们就需要己自定义一张表。
可以用做计数的临时表，查询的语句只会用help_topic计数，超出的部分其实都是脏数据
*/
SELECT a.type, 1 as num 
FROM F0519 a 
JOIN mysql.help_topic b 
on b.help_topic_id < a.Num;

二、日期查询

#查询本周第一天
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY) AS TIME

#查询本周的最后一天
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE())-6 DAY) AS TIME

#查询本月的第一天
SELECT DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) AS TIME

#查询本月的最后一天
SELECT LAST_DAY(CURDATE()) AS TIME

#当前quarter的第一天：  
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM  CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),'%Y-%m-'),'01'); 
 
#当前quarter的最后一天：  
select LAST_DAY(MAKEDATE(EXTRACT(YEAR  FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month);

#当年第一天：
SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY);
 
#当年最后一天：
SELECT concat(YEAR(now()),'-12-31'); 

#新增一天
SELECT DATE_ADD(now(), INTERVAL 1 day) time; 

#新增一个星期
SELECT DATE_ADD(now(), INTERVAL 1 week) time;

#新增一个月
SELECT DATE_ADD(now(), INTERVAL 1 month) time;

#新增一个季度
SELECT DATE_ADD(now(), INTERVAL 1 quarter) time;

#新增一年
SELECT DATE_ADD(now(), INTERVAL 1 year) time;

三、窗口函数+自连接+模除

Create table F0524( 
	TransType varchar(22), 
	OprSeq int, 
	OpCode varchar(24), 
	TransTime varchar(34) 
); 

INSERT INTO F0524 VALUES('开始',10,'NF21','2019-11-30 14:06'); 
INSERT INTO F0524 VALUES('换班',10,'NF21','2019-11-30 14:09'); 
INSERT INTO F0524 VALUES('开始',10,'NF21','2019-11-30 14:10'); 
INSERT INTO F0524 VALUES('结束',10,'NF21','2019-11-30 14:13'); 
INSERT INTO F0524 VALUES('开始',20,'NF22','2019-11-30 14:15'); 
INSERT INTO F0524 VALUES('结束',20,'NF22','2019-11-30 14:16'); 
INSERT INTO F0524 VALUES('开始',30,'NF24','2019-11-30 14:17'); 
INSERT INTO F0524 VALUES('结束',30,'NF24','2019-11-30 14:20');  

-- 要求：根据TransType取每道OprSeq的开始与结束时间，其中，开始-换班是一组，开始-结束是一组

在这里插入图片描述

WITH CTE AS( 
	SELECT 
		*, 
		ROW_NUMBER() OVER(PARTITION BY OprSeq ORDER BY TransTime) RN  
	FROM F0524 
) 

SELECT 
	a.OprSeq, 
	a.OpCode, 
	a.TransTime AS StartTime, 
	b.TransTime AS EndTime 
FROM CTE a 
LEFT JOIN CTE b 
ON a.OprSeq=b.OprSeq AND b.RN=a.RN+1  
WHERE a.RN % 2 > 0;

四、求解行最值问题

CREATE TABLE F0527( 
	date1 date, 
	date2 date, 
	date3 date 
);  

INSERT INTO F0527 VALUES('2020-02-02','2020-08-05','2020-06-08'); 
INSERT INTO F0527 VALUES('2020-09-02','2020-11-05','2020-12-08');  

-- 要求：希望求出date1，date2，date3这三列中每行的最大值maxdate

-- sql server
SELECT 
	date1, 
	date2, 
	date3, 
	( 
		SELECT MAX(date1) 
		FROM ( 
			SELECT date1 as date1 	
			UNION ALL  	
			SELECT date2 
			UNION ALL  
			SELECT date3 
		)T 
	) maxdate 
FROM F0527

-- mysql
-- GREATEST(a,b,c) 某几列的最大值，横向求最大(一行记录)
-- LEAST(a,b,c) 某几列的最小值，横向求最小(一行记录)
select 
	date1, 
	date2, 
	date3, 
	greatest(date1, date2, date3) maxdate,
	least(date1, date2, date3) mindate
from F0527;

在这里插入图片描述

五、巧解递归问题

CREATE TABLE F0530(
	ID INT,
	PID INT
);  

INSERT INTO F0530 VALUES (1,null); 
INSERT INTO F0530 VALUES (2,1); 
INSERT INTO F0530 VALUES (3,1); 
INSERT INTO F0530 VALUES (4,2); 
INSERT INTO F0530 VALUES (5,2);  

-- 要求：求出如图所示的结果  解释：节点 '1' 是根节点，
-- 因为它的父节点是 NULL ， 同时它有孩子节点 '2' 和 '3' 。
-- 节点 '2' 是内部节点，因为它有父节点 '1' ，也有孩子节点 '4' 和 '5' 。 
-- 节点 '3', '4' 和 '5' 都是叶子节点，因为它们都有父节点同时没有孩子节点。 
-- 注意:如果树中只有一个节点，你只需要输出它的根属性

在这里插入图片描述

SELECT 
	ID, 
	CASE WHEN PID IS NULL THEN 'ROOT' 
			 WHEN ID IN (SELECT PID FROM F0530) THEN 'INNER' ELSE 'LEAF' 
  END TYPE 
FROM F0530;

在这里插入图片描述

六、rank窗口函数求解最值问题

CREATE TABLE F0531 ( 
	order_id INT, 
	customer_id INT, 
	order_date VARCHAR(20) 
);  

INSERT INTO F0531 VALUES (1,1,'20190624'); 
INSERT INTO F0531 VALUES (2,2,'20190423'); 
INSERT INTO F0531 VALUES (3,3,'20190321'); 
INSERT INTO F0531 VALUES (4,3,'20190429'); 
INSERT INTO F0531 VALUES (5,4,'20190812'); 
INSERT INTO F0531 VALUES (6,4,'20190914');  


-- 要求：求出在表中订单数最多客户对应的customer_id

SELECT CUSTOMER_ID  
FROM  ( 
	SELECT 
		CUSTOMER_ID,
		RANK() OVER(ORDER BY T DESC) R  
	FROM ( 
		SELECT CUSTOMER_ID,COUNT(1) T 
		FROM F0531 
		GROUP BY CUSTOMER_ID 
	) A 
) B 
WHERE B.R=1;

在这里插入图片描述

七、数据拼接

CREATE TABLE F0609A (
	A INT, 
	B INT
);  

INSERT INTO F0609A VALUES(1,2); 
INSERT INTO F0609A VALUES(4,6);  

CREATE TABLE F0609B (
	C INT, 
	D INT, 
	E INT
);  

INSERT INTO F0609B VALUES(7,4,3); 
INSERT INTO F0609B VALUES(9,5,8); 
INSERT INTO F0609B VALUES(11,15,18);  

-- 要求：将F0609A里的数据与F0609B里的数据进行拼接，得到如下图所示结果。

在这里插入图片描述

SELECT  A1.A, A1.B, B1.C, B1.D, B1.E 
FROM (
	SELECT  ROW_NUMBER() OVER(ORDER BY a) id, A, B 
	FROM F0609A T1 
) A1 
FULL JOIN (
	SELECT  ROW_NUMBER() OVER(ORDER BY C) id, C, D, E 
	FROM F0609B T2 
) B1 
ON A1.id=B1.id

八、自连接的简单用法

CREATE TABLE F0613 ( 
	ID INT, 
	REDATE DATE, 
	TEMP INT
);  

INSERT INTO F0613 VALUES (1,'2020-1-1',10); 
INSERT INTO F0613 VALUES (2,'2020-1-2',18); 
INSERT INTO F0613 VALUES (3,'2020-1-3',15); 
INSERT INTO F0613 VALUES (4,'2020-1-4',20);  

-- 要求：查询出比前一天温度更高的所有日期。请使用至少两种方法求解。

-- 方法一 
SELECT ID 
FROM F0613 a 
WHERE a.temp > (
	SELECT temp 
	FROM F0613 b 
	WHERE a.ID = b.ID + 1
)  

-- 方法二 
SELECT f2.ID 
FROM F0613 f1,F0613 f2 
WHERE DATEDIFF(DAY,f1.ReDate,f2.ReDate) = 1 
AND f2.Temp > f1.Temp