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

傷城~ 2023-09-30 22:03 10阅读 0赞

### 一、均等分问题 ###

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均等分,如下图所示

![在这里插入图片描述][e68784b948254047bc2d3c9d17723c50.png]

-- 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的开始与结束时间，其中，开始-换班是一组，开始-结束是一组

![在这里插入图片描述][132404116a9043f3a8e2dc327075f6e2.png]

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;

![在这里插入图片描述][d26bdd5adbc24b30aa98a173aac97caf.png]

### 五、巧解递归问题 ###

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' 都是叶子节点，因为它们都有父节点同时没有孩子节点。 
    -- 注意:如果树中只有一个节点，你只需要输出它的根属性

![在这里插入图片描述][9607373e2d4a4b8bb4fb7054e58a4284.png]

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

![在这里插入图片描述][03fdbee345ba44028424a14c2d7c9eb8.png]

### 六、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;

![在这里插入图片描述][e65cfe0a51b84c97ae803076653ed3e7.png]

### 七、数据拼接 ###

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里的数据进行拼接，得到如下图所示结果。

![在这里插入图片描述][4259e9e398ad4c3ba3fa0af94628095d.png]

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

![在这里插入图片描述][93090466c7de49699208cb897cfb25a0.png]

[e68784b948254047bc2d3c9d17723c50.png]: https://img-blog.csdnimg.cn/e68784b948254047bc2d3c9d17723c50.png
[132404116a9043f3a8e2dc327075f6e2.png]: https://img-blog.csdnimg.cn/132404116a9043f3a8e2dc327075f6e2.png
[d26bdd5adbc24b30aa98a173aac97caf.png]: https://img-blog.csdnimg.cn/d26bdd5adbc24b30aa98a173aac97caf.png
[9607373e2d4a4b8bb4fb7054e58a4284.png]: https://img-blog.csdnimg.cn/9607373e2d4a4b8bb4fb7054e58a4284.png
[03fdbee345ba44028424a14c2d7c9eb8.png]: https://img-blog.csdnimg.cn/03fdbee345ba44028424a14c2d7c9eb8.png
[e65cfe0a51b84c97ae803076653ed3e7.png]: https://img-blog.csdnimg.cn/e65cfe0a51b84c97ae803076653ed3e7.png
[4259e9e398ad4c3ba3fa0af94628095d.png]: https://img-blog.csdnimg.cn/4259e9e398ad4c3ba3fa0af94628095d.png
[93090466c7de49699208cb897cfb25a0.png]: https://img-blog.csdnimg.cn/93090466c7de49699208cb897cfb25a0.png