SQL分析——常见问题九

快来打我* 2023-09-30 22:02 12阅读 0赞

### 一、员工打卡问题 ###

CREATE TABLE F0415 (
    	UserId int,
    	CheckIn datetime
    );
    
    INSERT INTO F0415 VALUES (1, '2018/12/1 8:25');
    INSERT INTO F0415 VALUES (1, '2018/12/1 8:26');
    INSERT INTO F0415 VALUES (1, '2018/12/1 17:02');
    INSERT INTO F0415 VALUES (1, '2018/12/2 8:27');
    INSERT INTO F0415 VALUES (2, '2018/12/1 8:26');
    INSERT INTO F0415 VALUES (2, '2018/12/1 17:03');
    INSERT INTO F0415 VALUES (2, '2018/12/1 17:29');
    INSERT INTO F0415 VALUES (2, '2018/12/1 18:01');
    
    -- 要求：1、每天上午8:00-9:00，下午16:00-18:00这两个时间段内的最早记录视为【有效】
    --      2、在这两个时间段内其它打卡数据显示【重复】，否则视为【无效】

SELECT
    	t1.UserID,
    	t1.CheckIn,
    	CASE WHEN t1.状态 = 0 THEN '无效' 
    			 WHEN t1.状态 > 0 AND t1.状态2 = 1 THEN '有效' 
    			 WHEN t1.状态 > 0 AND t1.状态2 >= 2 THEN '重复' 
    	END AS 状态 
    FROM (
    	SELECT
    		*,
    		-- 按用户，状态，日期进行分组 
    		ROW_NUMBER ( ) OVER (PARTITION BY t.UserID,t.状态,date(t.CheckIn ) ORDER BY t.CheckIn ) AS 状态2 
    	FROM (
    		select 
    			*,
    			case when time(CheckIn) between '08:00:00' and '09:00:00' then 1
    					 when time(CheckIn) between '16:00:00' and '18:00:00' then 2
    					 else 0 
    			end as 状态
    		from F0415
    	) t 
    ) t1 
    ORDER BY t1.UserID, t1.CheckIn

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

### 二、求解金额分摊问题 ###

CREATE TABLE F0420 (
    	订单号 VARCHAR(10), 
    	充值日期 DATE, 
    	充值金额 FLOAT, 
    	充值产品 VARCHAR(100), 
    	有效天数 INT 
    );  
    
    INSERT INTO F0420 VALUES('1001','2020-07-01',500.00,'初一数学提高班',90); 
    INSERT INTO F0420 VALUES('1002','2020-08-04',1000.00,'成人英语口语突破班',30); 
    INSERT INTO F0420 VALUES('1003','2020-09-10',2000.00,'初三数学提高班',240); 
    INSERT INTO F0420 VALUES('1004','2020-11-15',3000.00,'高三语文作文提高班',360); 
    INSERT INTO F0420 VALUES('1005','2020-12-20',2000.00,'高一物理精讲班',60);  
    
    /* 
    	 要求：先需要根据财务的需求，根据充值日期、有效天数和充值金额分摊到2020年最后一天， 
       即2020年12月31日。 
    	 1、如果有效天数小于从充值日期到2020-12-31日的天数，则全部分摊到2020年 
    	 2、如果有效天数大于从充值日期到2020-12-31日的天数，则需要按剩余天数比例分摊到2020年 
    	 例如：2020-09-10这天充值了2000元，从2020-09-10到2020-12-31日这一天总共有113天， 
    	 实际有效期为240天，那么到2020-12-31日这一天，需要分摊这2000元的金额计算方式为： 
    	 2000/240*113=941.6629。
    	 如果有效天数小于到2020-12-31日这天的天数，那么就全部分摊。 
    */

SELECT 
    	a.订单号, 
    	a.充值日期, 
    	a.充值金额, 
    	a.充值产品, 
    	a.有效天数, 
    	CASE WHEN a.有效天数<= d THEN a.充值金额 ELSE a.充值金额/a.有效天数 *d  END AS 分摊金额,  
    	充值金额 - CASE WHEN a.有效天数<= d THEN a.充值金额  ELSE a.充值金额/a.有效天数 *d  END AS 剩余金额 
    FROM (
    	SELECT * ,DATEDIFF('2020-12-31', 充值日期)+1 AS d  
    	FROM F0420 
    ) a

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

### 三、递归求解组织结构问题 ###

CREATE TABLE F0421 ( 
    	ORGID int, 
    	DEPTNAME varchar(20), 
    	PARENTID int
    );  
    
    INSERT INTO F0421 VALUES(100,'集团',0); 
    INSERT INTO F0421 VALUES(101,'人事部',100); 
    INSERT INTO F0421 VALUES(102,'财务部',100); 
    INSERT INTO F0421 VALUES(103,'薪酬组',101); 
    INSERT INTO F0421 VALUES(104,'招聘组',101); 
    INSERT INTO F0421 VALUES(105,'会计组',102); 
    INSERT INTO F0421 VALUES(106,'出纳组',102); 
    
    -- 要求：将每个部门的组织架构按顺序排列，如下图。

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

WITH RECURSIVE cte AS ( 
    -- 取出叶子节点 
    	SELECT *,a.ORGID AS Leaf,100000 AS RN 
    	FROM F0421 a 
    	WHERE NOT EXISTS ( 
    		SELECT 1 
    		FROM F0421 b  
    		WHERE b.PARENTID = a.ORGID 
    	) 
    	UNION ALL 
    	-- 自连接获取子节点与父节点的关联关系 
    	SELECT a.*,l.Leaf,l.rn-1 
    	FROM F0421 a 
    	INNER JOIN cte l 
    	ON a.ORGID = l.PARENTID 
    ) 
    
    SELECT cte.ORGID,cte.DEPTNAME,cte.PARENTID ,Leaf,rn 
    FROM cte  
    ORDER BY cte.Leaf,rn

### 四、求解省市区对应关系 ###

CREATE TABLE F0426 (
    	ID INT, 
    	CODE INT, 
    	NAME VARCHAR(20), 
    	PARENTCODE INT 
    ); 
    
    INSERT INTO F0426 VALUES (1,10000,'浙江',0); 
    INSERT INTO F0426 VALUES (2,20000,'安徽',0); 
    INSERT INTO F0426 VALUES (3,11000,'杭州',10000); 
    INSERT INTO F0426 VALUES (4,12000,'宁波',10000); 
    INSERT INTO F0426 VALUES (5,21000,'合肥',20000); 
    INSERT INTO F0426 VALUES (6,22000,'宣城',20000); 
    INSERT INTO F0426 VALUES (7,11100,'萧山',11000); 
    INSERT INTO F0426 VALUES (8,11300,'滨江',11000); 
    INSERT INTO F0426 VALUES (9,11300,'富阳',11000);  
    
    -- 要求：得出如下图所示的结果。

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

SELECT * 
    FROM (
    	SELECT NAME AS NAME_1, '' AS NAME_2, '' AS NAME_3, 1 AS LEVEL 
    	FROM F0426 
    	WHERE PARENTCODE=0 
    	UNION ALL 
    	SELECT A.NAME, B.NAME, '', 2 
    	FROM F0426 A 
    	JOIN F0426 B ON A.CODE=B.PARENTCODE AND A.PARENTCODE=0 
    	UNION ALL 
    	SELECT A.NAME, B.NAME, C.NAME, 3 
    	FROM F0426 A
    	JOIN F0426 B ON A.CODE=B.PARENTCODE AND A.PARENTCODE=0 
    	JOIN F0426 C ON B.CODE=C.PARENTCODE 
    ) AS A 
    ORDER BY NAME_1,NAME_2,LEVEL

### 五、求解背包问题 ###

CREATE TABLE F0506 (	
    	ID INT, 
    	NUM INT 
    ); 
    
    INSERT INTO F0506 VALUES (1,20); 
    INSERT INTO F0506 VALUES (2,18); 
    INSERT INTO F0506 VALUES (3,22); 
    INSERT INTO F0506 VALUES (4,26); 
    INSERT INTO F0506 VALUES (5,35); 
    INSERT INTO F0506 VALUES (6,42); 
    INSERT INTO F0506 VALUES (7,37); 
    INSERT INTO F0506 VALUES (8,76); 
    INSERT INTO F0506 VALUES (9,62); 
    INSERT INTO F0506 VALUES (10,18);  
    
    -- 要求：任意几个数的和大于95并且小于100，求出符合要求的数据组合
    -- 例如：2，9，10对应的数相加： 18+62+18=98

-- SQL Server解法 ;
    WITH CTE AS ( 
    	SELECT 
    		*, CAST(ID AS VARCHAR(8000)) PATH, 
    		NUM TOTAL 
    	FROM F0506 
    	UNION ALL 
    	SELECT 
    		B.ID,
    		B.NUM, 
    		A.PATH+'-'+RTRIM(B.ID), 
    		A.TOTAL+B.NUM 
    	FROM CTE A 
    	JOIN F0506 B 
    	ON A.ID<B.ID AND A.TOTAL<100 
    ) 
    
    SELECT ID,NUM  
    FROM F0506, (
    	SELECT TOP 1 PATH  
    	FROM CTE  
    	WHERE TOTAL>95 AND TOTAL<100 
    	ORDER BY NEWID() 
    ) A 
    WHERE CHARINDEX('-'+RTRIM(ID)+'-','-'+PATH+'-') > 0   
    
    -- MySQL解法 
    WITH RECURSIVE CTE AS ( 
    	SELECT 
    		*, 
    		CAST(ID AS CHAR(2000)) PATH, 
    		NUM TOTAL 
    	FROM F0506 
    	UNION ALL 
    	SELECT 
    		B.ID,
    		B.NUM, 
    		CONCAT(A.PATH,'-',RTRIM(B.ID)), 
    		A.TOTAL+B.NUM 
    	FROM CTE A  
    	JOIN F0506 B 
    	ON A.ID<B.ID AND A.TOTAL<100 
    ) 
    
    SELECT ID,NUM  
    FROM F0506, (
    	SELECT PATH  
    	FROM CTE  
    	WHERE TOTAL>95 AND TOTAL<100 
    	ORDER BY rand() LIMIT 1 
    ) A 
    WHERE LOCATE(CONCAT('-',RTRIM(ID),'-'),CONCAT('-',PATH,'-'))>0

### 六、一列转多列(sql server) ###

CREATE TABLE F0507 (
    	DATE1 DATETIME
    ); 
    
    GO 
    DECLARE @DATE1 DATETIME SET @DATE1='2021-1-25' 
    WHILE @DATE1<GETDATE() 
    BEGIN  
    	INSERT INTO F0507 VALUES(@DATE1) 
    	SELECT @DATE1=DATEADD(M,1,@DATE1) 
    END 
    GO  
    
    -- 要求：将上述日期分成两列，其中奇数月份一列，偶数月份一列;

WITH CTE AS ( 
    	SELECT 
    		ROW_NUMBER() OVER (ORDER BY DATE1) ID,
    		* 
    	FROM F0507 
    ) 
    
    SELECT A.DATE1,B.DATE1 DATE2 
    FROM (
    	SELECT ROW_NUMBER() OVER (ORDER BY DATE1) ID,DATE1 
    	FROM CTE 
    	WHERE ID%2=1 
    ) A 
    JOIN ( 
    	SELECT ROW_NUMBER() OVER (ORDER BY DATE1) ID,DATE1 
    	FROM CTE WHERE ID%2=0 
    ) B 
    ON A.ID=B.ID

### 七、求解连续分组问题 ###

CREATE TABLE F0516 ( 
    	X INT, 
    	L VARCHAR(10) 
    );  
    
    INSERT INTO F0516 VALUES(1, 'A'); 
    INSERT INTO F0516 VALUES(2, 'A'); 
    INSERT INTO F0516 VALUES(3, 'A'); 
    INSERT INTO F0516 VALUES(4, 'A'); 
    INSERT INTO F0516 VALUES(5, 'B'); 
    INSERT INTO F0516 VALUES(6, 'B'); 
    INSERT INTO F0516 VALUES(7, 'B'); 
    INSERT INTO F0516 VALUES(8, 'A'); 
    INSERT INTO F0516 VALUES(9, 'A'); 
    
    -- 要求：将连续的数据组合起来，得到如下结果

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

SELECT 
    	L, 
    	-- sql server: CONVERT(VARCHAR(10),MIN(x))  +'-' +CONVERT(VARCHAR(10),MAX(x)) AS R 
    	CONCAT(MIN(x), '-', MAX(x)) AS R 
    FROM ( 
    	SELECT 
    		X, 
    		L, 
    		ROW_NUMBER() OVER(PARTITION BY l ORDER BY x) AS rn, 
    		x-ROW_NUMBER() OVER(PARTITION BY l ORDER BY x) AS y  
    	FROM F0516 
    ) a  
    GROUP BY y,l

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

### 八、 窗口函数的妙用 ###

Create table F0518 ( 
    	商品 nvarchar(22), 
    	日期 Date, 
    	单价 int, 
    	数量 int 
    ); 
    
    Insert INTO F0518 VALUES ('桌子','2022-01-01',100,1); 
    Insert INTO F0518 VALUES ('椅子','2022-01-01',50,4); 
    Insert INTO F0518 VALUES ('桌子','2022-01-02',100,2); 
    Insert INTO F0518 VALUES ('桌子','2022-01-02',105,1);  
    
    -- 要求：查找每种商品 最近一天 的日平均单价

SELECT  
    	商品, 
    	日期, 
    	SUM(单价 * 数量) * 1.0 / SUM(数量) AS 平均单价 
    FROM ( 
    	SELECT 
    		* , 
    		DENSE_RANK() OVER ( PARTITION BY 商品 ORDER BY 日期 DESC ) AS RN 
    	FROM F0518 
    ) AS a 
    WHERE RN = 1 
    GROUP BY 商品,日期;

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

[2d5dfd0273824282ac34e713fc17e739.png]: https://img-blog.csdnimg.cn/2d5dfd0273824282ac34e713fc17e739.png
[0a1cae5aedb04eee9ca5e6c2ca5d1e77.png]: https://img-blog.csdnimg.cn/0a1cae5aedb04eee9ca5e6c2ca5d1e77.png
[4895f8d02b7742608b4c60b740ad6f0d.png]: https://img-blog.csdnimg.cn/4895f8d02b7742608b4c60b740ad6f0d.png
[b88e4bed0ea94dd8ac2af63bba293916.png]: https://img-blog.csdnimg.cn/b88e4bed0ea94dd8ac2af63bba293916.png
[47acc437d1ab46a79901219cb187fcae.png]: https://img-blog.csdnimg.cn/47acc437d1ab46a79901219cb187fcae.png
[ac8ec7170de44deda64a76e8c6c74091.png]: https://img-blog.csdnimg.cn/ac8ec7170de44deda64a76e8c6c74091.png
[2888bc30f72044758136022d9a40548d.png]: https://img-blog.csdnimg.cn/2888bc30f72044758136022d9a40548d.png