SQL分析——常见问题八

「爱情、让人受尽委屈。」 2023-09-30 22:01 11阅读 0赞

### 一、求解每组前N条记录 ###

CREATE TABLE F0325
    (
    	ID INT,
    	NAMES VARCHAR(20),
    	Salary INT,
    	DepartmentID INT
    )
    
    INSERT INTO F0325 VALUES
    (1, '曹操', 85000, 1),
    (2, '关羽', 80000, 2),
    (3, '刘备', 60000, 2),
    (4, '吕布', 90000, 1),
    (5, '张飞', 69000, 1),
    (6, '赵云', 85000, 1),
    (7, '黄忠', 70000, 1)
    
    CREATE TABLE F0325B
    (
    	ID INT,
    	Name VARCHAR(20)
    )
    
    INSERT INTO F0325B VALUES
    (1, 'IT'),
    (2, 'Sales')
    
    
    -- 要求：找出每个部门获得工资前三的所有员工
    -- 注意：至少两种方法求解！

解法一：采用DENSE\_RANK()窗口函数：

select a.NAMES '员工', a.Salary '工资', b.NAME '部门' 
    from (
    	select 
    	*, 
    	DENSE_RANK() OVER(PARTITION BY DepartmentID ORDER BY Salary dESC) rk
    	FROM F0325
    ) a 
    INNER JOIN F0325B b 
    ON a.DepartmentID = b.ID 
    WHERE a.rk <= 3
    ORDER BY a.DepartmentID;

解法二：自连接+子查询

SELECT 
    	f1.NAMES '员工',
    	d.NAME '部门',
    	f1.Salary '工资'
    FROM F0325 f1 
    INNER JOIN F0325B d ON f1.DepartmentID = d.ID 
    WHERE (
    	SELECT COUNT(DISTINCT f2.Salary)
    	FROM F0325 f2 
    	WHERE f2.Salary > f1.Salary 
    	AND f1.DepartmentID = f2.DepartmentID
    ) < 3
    ORDER BY d.NAME

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

### 二、集合论 ###

CREATE TABLE F0329 ( 
    	workid VARCHAR(20), 
    	RecDate DATE, 
    	RecTime VARCHAR(20), 
    	Time4 VARCHAR(20)  
    ) 
    	
    INSERT INTO F0329 VALUES 
    ('161181','2021-05-03','13:01','13:01'); 
    INSERT INTO F0329 VALUES 
    ('161181','2021-05-03','14:01','15:01'); 
    INSERT INTO F0329 VALUES 
    ('161181','2021-05-06','13:01','13:01'); 
    INSERT INTO F0329 VALUES 
    ('161182','2021-05-07','13:01','17:01'); 
    INSERT INTO F0329 VALUES 
    ('161182','2021-05-07','13:01','18:01'); 
    INSERT INTO F0329 VALUES 
    ('161182','2021-05-09','13:01','19:01');  
    
    -- 要求：希望按工号查找日期(RecDate)相同且记录数>1，
    -- 且Rectime=Time4

SELECT a.* 
    FROM F0329 a 
    JOIN ( 
    	SELECT workid,RecDate 
    	FROM F0329 
    	GROUP BY workid,RecDate 
    	HAVING COUNT(1) >1 
    ) b 
    ON a.workid=b.workid AND a.Recdate=b.RecDate 
    WHERE a.RecTime=a.Time4

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

### 三、表表达式和子查询的运用(sql server) ###

CREATE TABLE Sales ( 
    	store_name VARCHAR(100) COMMENT '门店名称', 
    	sale_date VARCHAR(20) COMMENT '销售日期', 
    	doc_id VARCHAR(40) COMMENT '订单编号', 
    	product_code VARCHAR(40) COMMENT '商品编号', 
    	qty INT COMMENT '销售量', 
    	amt DECIMAL(18,2) COMMENT '销售额' 
    )  
    
    INSERT INTO Sales VALUES 
    ('浙江杭州XX店','2022-03-03','10001','001',20,4000), 
    ('浙江宁波XX店','2022-03-04','10002','002',15,3000), 
    ('浙江温州XX店','2022-03-05','10003','002',3,600), 
    ('浙江台州XX店','2022-03-05','10003','002',2,400), 
    ('江苏南京XX店','2022-03-06','10004','001',3,600), 
    ('江苏无锡XX店','2022-03-08','10005','002',30,6000), 
    ('江苏苏州XX店','2022-03-09','10006','001',10,2000), 
    ('安徽合肥XX店','2022-03-10','10007','002',2,400), 
    ('安徽芜湖XX店','2022-03-17','10008','002',18,3600), 
    ('安徽蚌埠XX店','2022-03-23','10009','002',10,2000), 
    ('安徽安庆XX店','2022-03-24','10010','001',2,400)  
    
    -- 要求：求上个月每个省，城市销售额占比省份销售额大于等于10%的城市；

WITH CTE AS (
    	SELECT  sale_date 销售日期, 
    	SUBSTRING(store_name,1,2) 省份, 
    	SUBSTRING(store_name,3,2) 城市, 
    	amt 销售额 
    	FROM Sales 
    	WHERE sale_date >= DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1, 0) 
    	AND sale_date<DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0) )  
    	
    	-- 上月第一天 
    	-- SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1, 0) 
    	-- 本月第一天 
    	-- SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)  
    	
    SELECT * 
    FROM ( 
    	SELECT A.省份,A.城市,A.销售额 城市销售额, B.销售额 省份销售额,A.销售额*1.0/B.销售额 占比 
    	FROM (
    		SELECT 省份,城市,SUM(销售额) 销售额 
    		FROM CTE 
    		GROUP BY 省份,城市 
    	) A 
    	JOIN (
    		SELECT 省份,SUM(销售额) 销售额 
    		FROM CTE 
    		GROUP BY 省份 
    	) B 
    	ON A.省份=B.省份 
    ) T 
    WHERE T.占比>=0.1;

### 四、求解回环表 ###

CREATE TABLE F0331 ( 
    	SN INT, 
    	ID VARCHAR(10), 
    	OLD VARCHAR(10), 
    	NEW VARCHAR(10) 
    )  
    
    INSERT INTO F0331 VALUES  
    (1,'1','212','302'), 
    (2,'1','302','109'), 
    (3,'1','109','200'), 
    (4,'1','101','235'), 
    (5,'1','235','332'), 
    (6,'1','145','330')  
    
    -- 要求：求出每个回环的开始和结束的记录  
    -- 解题思路 
    -- 通过递归获取到每组回环数据记录

WITH T1 AS ( 
    	SELECT *,Old OLDEST 
    	FROM F0331 
    	WHERE SN = 1 
    	UNION ALL 
    	SELECT 
    		B.*, 
    		CASE WHEN A.NEW = B.Old THEN A.OLDEST ELSE B.Old END  
    	FROM T1 A,F0331 B  
    	WHERE B.SN = A.SN + 1 
    )  
    -- SELECT * FROM T1  
    -- 对每组回环数据取最大最小序列号，即可间接获取序列对应的回环开始和结束记录 
    SELECT 
    ( 
    -- 获取回环的开始记录 
    SELECT A.Old 
    FROM F0331 A 
    WHERE A.SN = MIN(X.SN) 
    ) StartValue, 
    ( 
    -- 获取回环的结束记录 
    SELECT A.New 
    FROM F0331 A 
    WHERE A.SN = MAX(X.SN) 
    ) EndValue 
    FROM T1 X  
    GROUP BY OLDEST

### 五、窗口函数求解时间问题 ###

CREATE TABLE F0406 ( 
    	物品 VARCHAR(10), 
    	购买日期 VARCHAR(20), 
    	单价 INT  
    );  
    
    INSERT INTO F0406 VALUES 
    ('A','2018-11-01',100), 
    ('A','2018-10-01',120), 
    ('A','2018-12-01',115), 
    ('B','2018-11-01',99), 
    ('B','2018-9-01',88)  
    
    -- 要求：依次查询出最新单价，上一次单价，上上次单价，如果不存在则用0替代。

WITH cte AS (
    	SELECT
    	*,
    	ROW_NUMBER() OVER(PARTITION BY 物品 ORDER BY 购买日期 DESC) AS rid
      FROM F0406
    )
    
    SELECT  
    	t1.物品,
    	t1.最新单价,
    	IFNULL(t2.单价,0) AS 上一次单价,
    	IFNULL(t3.单价,0) AS 上上次 
    FROM  (     
    	SELECT 物品, 单价 AS 最新单价     
    	FROM cte 
    	WHERE rid=1 
    ) AS t1 
    LEFT JOIN cte AS t2 
    ON t1.物品=t2.物品 AND t2.rid=2 
    LEFT JOIN cte AS t3 
    ON t1.物品=t3.物品 AND t3.rid=3

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

### 六、case when 的简单用法 ###

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

create table stu(
    	name VARCHAR(20),
    	course varchar(20),
    	score int 
    );
    
    insert into stu VALUES
    ('张三', '语文', 88),
    ('张三', '数学', 96),
    ('李四', '语文', 92),
    ('李四', '数学', 91);
    
    select * from stu;
    
    select 
    	name '姓名',
    	max(case when course = '语文' then score end) as '语文',
    	max(case when course = '数学' then score end) as '数学' 
    from stu 
    group by name

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

### 七、求解上下两行相减 ###

CREATE TABLE F0408 ( 
    	UID INT NOT NULL, 
    	CALLBACK_DATE DATE NOT NULL 
    ); 
    
    INSERT INTO F0408 VALUES (1,'2020-4-1');
    INSERT INTO F0408 VALUES (1,'2020-4-5');
    INSERT INTO F0408 VALUES (1,'2020-4-10'); 
    INSERT INTO F0408 VALUES (1,'2020-4-19'); 
    INSERT INTO F0408 VALUES (2,'2020-4-1'); 
    INSERT INTO F0408 VALUES (2,'2020-4-15'); 
    INSERT INTO F0408 VALUES (2,'2020-4-20'); 
    INSERT INTO F0408 VALUES (2,'2020-4-16'); 
    
    -- 要求：第一次召回不计费，后续如果间隔大于7天计费，否则不计费， 
    -- 再后续距离上一次计费大于7天计费，否则不计费，以此类推

SELECT 
    	*, 
    	CASE WHEN 
    		LAG(CALLBACK_DATE) OVER (PARTITION BY UID ORDER BY CALLBACK_DATE) IS NULL 
    		OR 
    		DATEDIFF(CALLBACK_DATE, LAG(CALLBACK_DATE) OVER (PARTITION BY UID ORDER BY CALLBACK_DATE))< 7 THEN '不计费'  ELSE '计费' 
    	END AS CHARGE 
    FROM F0408 
    ORDER BY UID,CALLBACK_DATE

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

### 八、巧用count窗口函数求解 ###

CREATE TABLE F0411 ( 
    	DOCNUM INT, 
    	STATUS VARCHAR ( 26 ) 
    ); 
    
    INSERT INTO F0411 VALUES( 33, 'FULL'); 
    INSERT INTO F0411 VALUES( 33, 'NOFULL');
    INSERT INTO F0411 VALUES( 34, 'FULL'); 
    INSERT INTO F0411 VALUES( 35, 'FULL'); 
    INSERT INTO F0411 VALUES( 35, 'NOFULL'); 
    INSERT INTO F0411 VALUES( 36, 'FULL'); 
    INSERT INTO F0411 VALUES( 37, 'FULL');
    INSERT INTO F0411 VALUES( 38, 'FULL');
    INSERT INTO F0411 VALUES( 38, 'NOFULL'); 
    
    -- 要求：只取状态为 FULL的 DOCNUM，如果有同时为NOFULL的DOCNUM则不取

SELECT t.Docnum 
    FROM (
    	SELECT
    		*,
    		COUNT( 1 ) OVER ( PARTITION BY Docnum ) AS RN 
    	FROM F0411 
    ) t 
    WHERE t.STATUS = 'FULL' AND t.RN = 1

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

[ab2f7710900c4e08963a5736c6d0ab6f.png]: https://img-blog.csdnimg.cn/ab2f7710900c4e08963a5736c6d0ab6f.png
[dd4c7857c179483ab2c21a9b03ab068f.png]: https://img-blog.csdnimg.cn/dd4c7857c179483ab2c21a9b03ab068f.png
[cb55e1cf2f0a4e51af77aaff8a8e7afe.png]: https://img-blog.csdnimg.cn/cb55e1cf2f0a4e51af77aaff8a8e7afe.png
[278be933eb5945feb3ab44e28cbcfad2.png]: https://img-blog.csdnimg.cn/278be933eb5945feb3ab44e28cbcfad2.png
[c7d50cb08df6478e8030ef99107ed582.png]: https://img-blog.csdnimg.cn/c7d50cb08df6478e8030ef99107ed582.png
[577ad4e4be2f4c598a5b19eb3db2d0e2.png]: https://img-blog.csdnimg.cn/577ad4e4be2f4c598a5b19eb3db2d0e2.png
[3aa89f0904984fc6b6d6d9f40847fc4c.png]: https://img-blog.csdnimg.cn/3aa89f0904984fc6b6d6d9f40847fc4c.png