SQL分析——常见问题十一

拼搏现实的明天。 2023-09-30 22:03 12阅读 0赞

### 一、删除重复记录行 ###

CREATE TABLE F0614 ( 
    	ID INT, 
    	Phone VARCHAR(20) 
    );  
    
    INSERT INTO F0614 VALUES (1,'13512345678'); 
    INSERT INTO F0614 VALUES (2,'13512345678'); 
    INSERT INTO F0614 VALUES (3,'13012345678');  
    
    -- 要求：删除表中所有重复的电话号码，重复的电话号码只保留ID最小的那个 
    -- 至少使用2种方法！

-- 方法一 
    DELETE FROM F0614 f1,F0614 f2 
    WHERE f1.Phone=f2.Phone AND f1.ID>f2.ID  
    
    -- 方法二 
    DELETE FROM F0614 
    WHERE ID IN ( 
    	SELECT f.CID 
    	FROM ( 
    		SELECT a.ID as CID 
    		FROM F0614 a 
    		LEFT JOIN F0614 b 
    		ON a.Phone=b.Phone 
    		WHERE a.id>b.ID 
    	) f 
    )  
    
    -- 方法三 
    DELETE FROM F0614
    WHERE ID NOT IN ( 
    	SELECT b.minnum 
    	FROM ( 
    		SELECT MIN(ID) minnum 
    		FROM F0614 
    		GROUP BY Phone 
    	) b 
    )

### 二、时间循环累加 ###

CREATE TABLE F0617 ( 
    	ID INT, 
    	EndTime DATETIME 
    );  
    
    INSERT INTO F0617 VALUES (1,'2020/5/26 16:00'); 
    INSERT INTO F0617 VALUES (2,'2020/5/26 17:30');  
    
    -- 要求：每条记录每次增加30分钟，增加3次

WITH RECURSIVE CTE AS ( 
    	SELECT ID,ENDTIME,1 AS NUM  
    	FROM F0617 
    	UNION ALL 
    	SELECT ID,DATE_ADD(c.ENDTIME,INTERVAL 30 MINUTE),c.NUM+1  
    	FROM CTE c WHERE NUM<4 
    )  
    
    SELECT * 
    FROM CTE c 
    ORDER BY c.ID,c.ENDTIME

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

### 三、分别使用窗口函数和自连接求解 ###

CREATE TABLE F0622 ( 
    	play_id INT, -- 玩家ID 
    	device_id INT, -- 设备ID 
    	event_date DATE, -- 登陆日期 
    	games_played INT -- 玩家登陆玩的游戏数量 
    );  
    
    INSERT INTO F0622 VALUES(1,2,'2016/3/1', 5); 
    INSERT INTO F0622 VALUES(1,2,'2016/5/2', 6); 
    INSERT INTO F0622 VALUES(1,3,'2017/6/25',1); 
    INSERT INTO F0622 VALUES(3,1,'2016/3/2', 0); 
    INSERT INTO F0622 VALUES(3,4,'2018/7/3', 5);  
    
    -- 要求：查询每个玩家每天累计玩游戏的数量有多少？  
    -- 这是一个分组累计的题目，我们只需要对每个组按顺序求和即可，具体提供两种解法:

-- 解法一:使用开窗函数可以快速求解。 
    select  
    	play_id, 
    	event_date, 
    	sum(games_played) over(partition by play_id order by event_date) games_played_so_far 
    from F0622;  
    	
    -- 解法二 
    select  
    	a1.play_id, 
    	a1.event_date, 
    	sum(a2.games_played) games_played_so_far 
    from F0622 a1,F0622 a2 
    where a1.play_id=a2.play_id and a1.event_date>=a2.event_date 
    group by a1.play_id,a1.event_date 
    order by play_id,event_date;

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

### 四、求解互加好友问题 ###

CREATE TABLE F0629 ( 
    	requester_id INT, 
    	accepter_id INT, 
    	accept_date DATE 
    );  
    
    INSERT INTO F0629 VALUES (1,2,'2016-6-3'); 
    INSERT INTO F0629 VALUES (1,3,'2016-6-8'); 
    INSERT INTO F0629 VALUES (2,3,'2016-6-8'); 
    INSERT INTO F0629 VALUES (3,4,'2016-6-9');  
    
    -- 要求：查询出谁拥有最多的好友，以及他的好友数目

SELECT 
    	IDS ID,
    	COUNT(*) NUM 
    FROM (
    	SELECT REQUESTER_ID IDS 
    	FROM F0629 
    	UNION ALL 
    	SELECT ACCEPTER_ID IDS 
    	FROM F0629 
    ) T 
    GROUP BY IDS 
    ORDER BY COUNT(*) DESC
    LIMIT 1

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

### 五、求解均值问题 ###

CREATE TABLE F0705 (
    	ID int, 
    	NAME varchar(22), 
    	MON int, 
    	STATE varchar(21), 
    	AMOUNT int 
    ); 
    
    INSERT INTO F0705 VALUES 
    (1,'张三',201901,'A',9000), 
    (2,'张三',201901,'B',9000), 
    (3,'张三',201901,'E',9000), 
    (4,'李四',201902,'A',1800), 
    (5,'李四',201902,'C',1800), 
    (6,'王五',201902,'C',30000), 
    (7,'王五',201902,'F',30000);  
    
    -- 要求将每个NAME的AMOUNT按STATE进行平均分，预计得到如下结果

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

SELECT 
    	a.ID, 
    	a.NAME, 
    	a.MON, 
    	a.STATE, 
    	a.AMOUNT / ( 
    		SELECT COUNT(1)  
    		FROM F0705 b  
    		WHERE a.MON=b.MON AND b.NAME=a.NAME 
    	) AS AMOUNT 
    FROM F0705 a;

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

### 六、两种常见关联查询 ###

CREATE TABLE F0711A( 
    	ITEM VARCHAR(20), 
    	S_Name VARCHAR(20) 
    );  
    
    INSERT INTO F0711A VALUES('跑步','张三'); 
    INSERT INTO F0711A VALUES('游泳','张三'); 
    INSERT INTO F0711A VALUES('跳远','李四'); 
    INSERT INTO F0711A VALUES('跳高','王五');  
    
    CREATE TABLE F0711B ( 
    	ITEM VARCHAR(20), 
    	S_Name VARCHAR(20), 
    	Result VARCHAR(10) 
    );  
    
    INSERT INTO F0711B VALUES('跑步','张三','胜'); 
    INSERT INTO F0711B VALUES('游泳','张三','胜'); 
    INSERT INTO F0711B VALUES('跳高','王五','胜');  
    
    -- 要求：查询出每个人参加的项目数，以及获胜的项目数，预计结果如下图。

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

-- 解法一： 
    
    SELECT 
    	ISNULL(t1.S_Name,t2.S_Name) AS bid, 
    	ISNULL(t1.anum,0) AS anum, 
    	ISNULL(t2.bnum,0) AS bnum 
    FROM (
    	SELECT S_Name,COUNT(1) AS anum 
    	FROM F0711A 
    	GROUP BY S_Name 
    ) t1 
    FULL JOIN ( 
    	SELECT 
    		S_Name, 
    		SUM(CASE WHEN Result='胜' THEN 1 ELSE 0 END) bnum 
    	FROM F0711B 
    	GROUP BY S_Name 
    ) t2 
    ON t2.S_Name=t1.S_Name;   
    
    -- 解法二： 
    SELECT 
    	S_Name,
    	COUNT(1) AS anum,
    	IFNULL( (SELECT 
    						SUM(CASE WHEN Result='胜' THEN 1 ELSE 0 END) 
    						FROM F0711B b 
    						WHERE a.S_Name=b.S_Name), 0) AS bnum 
    FROM F0711A a 
    GROUP BY S_Name

### 七、求解跨月请假问题 ###

CREATE TABLE F0721 ( 
    	请假开始时间 DATE, 
    	请假结束时间 DATE, 
    	姓名 VARCHAR(20) 
    );  
    
    INSERT INTO F0721 VALUES ('2020-5-20','2020-5-25','张三'); 
    INSERT INTO F0721 VALUES ('2020-5-30','2020-6-1','张三'); 
    INSERT INTO F0721 VALUES ('2020-5-24','2020-5-25','李四'); 
    INSERT INTO F0721 VALUES ('2020-5-26','2020-6-5','李四'); 
    INSERT INTO F0721 VALUES ('2020-4-25','2020-6-5','王五');  
    
    -- 要求：求出每个员工每个月的请假天数，预计结果如下：

SELECT 
    	姓名,
    	MONTH(请假开始时间) 月份, 
    	SUM(DATEDIFF(DAY,请假开始时间,请假结束时间)+1) 总天数 
    FROM ( 
    	SELECT 
    		姓名, 
    		CASE WHEN T2.number=0 THEN T1.请假开始时间  
    			ELSE DATEADD(MONTH,T2.number,T1.STARTDAY) END 请假开始时间, 
    		CASE WHEN DATEADD(DAY,-1,DATEADD(MONTH,T2.number+1,T1.STARTDAY)) < T1.请假结束时间 THEN DATEADD(DAY,-1,DATEADD(MONTH,T2.number+1,T1.STARTDAY)) 
    			ELSE T1.请假结束时间 END 请假结束时间 
    	FROM ( 
    		SELECT 
    			请假开始时间,
    			请假结束时间,
    			姓名,  
    			DATEADD(DAY,1-DAY(请假开始时间),请假开始时间) STARTDAY, 
    			DATEDIFF(MONTH,请假开始时间,请假结束时间) CS 
    		FROM F0721 
    	) T1 
    	JOIN MASTER..spt_values T2 
    	ON T2.number<=DATEDIFF(MONTH,T1.请假开始时间,T1.请假结束时间) 
    	WHERE T2.type='P' 
    ) T 
    GROUP BY 姓名, MONTH(请假开始时间) 
    ORDER BY 1,2

### 八、求解日期比较问题 ###

CREATE TABLE F0722 (
    	ID INT,
    	REDATE DATE,
    	TEMP INT 
    );
    
    INSERT INTO F0722 VALUES (1, '2020-1-1', 10);
    INSERT INTO F0722 VALUES (2, '2020-1-2', 18);
    INSERT INTO F0722 VALUES (3, '2020-1-3', 15);
    INSERT INTO F0722 VALUES (4, '2020-1-4', 20);
    
    -- 要求：查找昨天的日期相比温度更高的所有日期的id

-- 方法一
    select a.ID 
    from F0722 a
    where a.temp > (
    	select b.temp 
    	from F0722 b 
    	where DATE_ADD(b.REDATE,INTERVAL 1 DAY) = a.REDATE 
    )
    
    -- 方法二
    select a.ID 
    from F0722 a, F0722 b 
    where DATEDIFF(a.REDATE,b.REDATE) = 1 and a.TEMP > b.TEMP

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

[312516cf1e224b57b5fd437e42eb63c6.png]: https://img-blog.csdnimg.cn/312516cf1e224b57b5fd437e42eb63c6.png
[750197eccc3148d3ac3fe6f96c4068ac.png]: https://img-blog.csdnimg.cn/750197eccc3148d3ac3fe6f96c4068ac.png
[4ca2e4d3db084234aa9d57ac8ca937ba.png]: https://img-blog.csdnimg.cn/4ca2e4d3db084234aa9d57ac8ca937ba.png
[6f43016843ba43428bdc9615e54c1e8d.png]: https://img-blog.csdnimg.cn/6f43016843ba43428bdc9615e54c1e8d.png
[37e2239ad63847b8843ae670e77bdfe7.png]: https://img-blog.csdnimg.cn/37e2239ad63847b8843ae670e77bdfe7.png
[e31f5860267e4cb69d758fa29659fd6c.png]: https://img-blog.csdnimg.cn/e31f5860267e4cb69d758fa29659fd6c.png
[7850a61de6694017baf7b49e67776d7c.png]: https://img-blog.csdnimg.cn/7850a61de6694017baf7b49e67776d7c.png