SQL分析——常见问题十二

柔光的暖阳◎ 2023-09-30 22:14 42阅读 0赞

### 一、上下行空值替换 ###

CREATE TABLE F0726 (
    	LDate DATE NOT NULL, 
    		Value1 INT NULL, 
    		Value2 INT NULL
    );
    
    INSERT INTO F0726 VALUES('2020-11-25', 500 ,200);
    INSERT INTO F0726 VALUES('2020-11-24', Null, 200);
    INSERT INTO F0726 VALUES('2020-11-23', Null, 250);
    INSERT INTO F0726 VALUES('2020-11-22', 300 ,Null);
    INSERT INTO F0726 VALUES('2020-11-21', 200 ,320);
    
    -- 要求：如果某列的字段在该日期为空值，查询结果显示为之前最接近日期的非空值

SELECT 
    	A.LDate,
    	(SELECT Value1 
    	FROM F0726 B
    	WHERE B.LDate<=A.LDate
    	AND B.Value1 IS NOT NULL
    	ORDER BY A.LDate
    	LIMIT 1
    	) Value1,
    	(SELECT Value2 
    	FROM F0726 B
    	WHERE B.LDate<=A.LDate
    	AND B.Value2 IS NOT NULL
    	ORDER BY A.LDate
    	LIMIT 1
    ) Value2
    FROM F0726 A

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

### 二、连续登录问题 ###

CREATE TABLE F0727 (
    	UID VARCHAR(10),
    	LOADTIME DATE 
    );
    
    INSERT INTO F0727 VALUES ('201', '2017/01/28');
    INSERT INTO F0727 VALUES ('201', '2017/01/29');
    INSERT INTO F0727 VALUES ('202', '2017/01/29');
    INSERT INTO F0727 VALUES ('202', '2017/01/30');
    INSERT INTO F0727 VALUES ('203', '2017/01/30');
    INSERT INTO F0727 VALUES ('201', '2017/01/31');
    INSERT INTO F0727 VALUES ('202', '2017/01/31');
    INSERT INTO F0727 VALUES ('201', '2017/02/01');
    INSERT INTO F0727 VALUES ('202', '2017/02/01');
    INSERT INTO F0727 VALUES ('201', '2017/02/02');
    INSERT INTO F0727 VALUES ('203', '2017/02/02');
    INSERT INTO F0727 VALUES ('203', '2017/02/03');
    
    -- 要求：查询各个用户最长的连续登录天数

select uid, max(cnt) cnt 
    from (
    	select uid, grp_no, count(*) cnt 
    	from (
    		select uid, loadtime,
    			(datediff(loadtime, '1970-01-01') - row_number() over(partition by uid order by uid, loadtime)) grp_no
    		from F0727
    	) a 
    	group by uid, grp_no 
    ) a 
    group by uid

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

### 三、存储过程创建日历 表 ###

-- 使用SQL创建一张日历表，要求有年、月、日、星期及具体日期
    
    CREATE TABLE CALENDAR_INFO
     (
    		 COUNTRY VARCHAR(5),-- '国家'
    		 CDR_CODE VARCHAR(5) ,-- '日历代码'
    		 DATE_NAME VARCHAR(20) , -- '日期名称'
    		 YEAR CHAR(4), -- '年'
    		 MONTH CHAR(2), -- '月'
    		 WEEK CHAR(6), -- '星期'
    		 DAY CHAR(2), -- '日'
    		 CDR_DATE CHAR(10), -- '日历日期'
    		 CREATOR VARCHAR(32), -- '创建人'
    		 CREATE_DATE DATETIME , -- '创建日期'
    		 CREATE_INST VARCHAR(20), -- '创建机构'
    		 DEL_FLAG CHAR(1) DEFAULT 0 , -- '删除标志' 0:不删除，1:已删除
    		 TS DATETIME DEFAULT GETDATE() -- '时间戳'
     );
    
    
    CREATE PROC PROC_CALENDAR(@YEAR INT )
    --定义一个名称为PROC_CALENDAR，且带输入参数@YEAR的存储过程
    AS
    BEGIN
    --AS BEGIN是语法要求
    --定义4个不同类型的变量@i，@START_DATE,@END_DATE,@DATE_COUNT
    DECLARE @i INT;
    DECLARE @START_DATE VARCHAR(20);
    DECLARE @END_DATE VARCHAR(20);
    DECLARE @DATE_COUNT INT;
      SET @i=0;
    --定义一年的开始日期，用CONCAT函数将年份和月份日期拼接起来
      SET @START_DATE= CONCAT(@YEAR, '-01-01');
    --定义一年的结束日期,就是取下一年的1月1日
      SET @END_DATE = CONCAT(@YEAR+1,'-01-01');
    --如果表已经存在要新建的日历，则先删除，防止重复写数据
      DELETE FROM CALENDAR_INFO WHERE YEAR = @YEAR;
    --计算开始到结束日期一共有多少天，DATEDIFF函数计算日期的间隔天数
      SET @DATE_COUNT = DATEDIFF(DAY,@START_DATE,@END_DATE);
    
    --建立循环，条件是@i小于一年的天数
      WHILE @i < @DATE_COUNT
      BEGIN 
    --开始循环插入数据
          INSERT INTO CALENDAR_INFO (COUNTRY,CDR_CODE,DATE_NAME,
    YEAR,MONTH,WEEK,DAY,CDR_DATE,CREATOR,CREATE_DATE,CREATE_INST)
           SELECT
           'CN',
           'CN01',
          --CONVERT函数将@START_DATE转换成指定的格式YYYY-MM-DD
           CONVERT(CHAR(10),@START_DATE,111) DATE_NAME,
          --DATENAME获取@START_DATE的年份并用COVERT转换为字符格式
           CONVERT(CHAR(4),DATENAME(YEAR, @START_DATE)) YEAR,
          --DATENAME获取@START_DATE的月份并用COVERT转换为字符格式
           CONVERT(CHAR(2),DATENAME(MONTH, @START_DATE)) MONTH,
          --DATENAME获取@START_DATE的星期并用COVERT转换为字符格式
           CONVERT(CHAR(6),DATENAME(WEEKDAY, @START_DATE)) WEEK,
          --DATENAME获取@START_DATE的日期并用COVERT转换为字符格式
           CONVERT(CHAR(2),DATENAME(DAY, @START_DATE)) DAY,
           CONVERT(CHAR(10),@START_DATE,120) CDR_DATE,
           'SQL数据库开发',
           GETDATE() CREATE_DATE,
           'SQL数据库开发'
         
          --插入完成一条记录后，对@i进行自加1处理，开始插入下一条
          SET @i=@i+1;
          --更新@START_DATE的值，在原来的基础上使用DATEADD函数增加一天
          SET @START_DATE=CONVERT(CHAR(10),DATEADD(DAY, 1, @START_DATE),120);
      END 
    END;
    
    --调用存储过程
    EXEC PROC_CALENDAR 2023;
    
    --查询数据核实准确性
    SELECT * FROM CALENDAR_INFO;

### 四、四种去重方法 ###

create table T_orders ( 
    	customer_id int, 
    	order_id int, 
    	orderdate date 
    );  
    
    insert into T_orders values (1,111,'2023-01-21'); 
    insert into T_orders values (1,201,'2023-01-22'); 
    insert into T_orders values (1,121,'2023-01-23'); 
    insert into T_orders values (2,109,'2023-01-22'); 
    insert into T_orders values (2,103,'2023-01-23'); 
    insert into T_orders values (3,209,'2023-01-22'); 
    insert into T_orders values (4,211,'2023-01-23'); 
    insert into T_orders values (5,232,'2023-01-24');  
    
    select * from T_orders;  
    
    -- Q:统计有多少客户下单

-- distinct 
    select count(distinct customer_id) cnt 
    from T_orders;  
    
    -- group by  
    select count(t.customer_id) cnt 
    from ( 
    	select customer_id 
    	from T_orders 
    	group by customer_id 
    ) t  
    
    -- row_number() 
    select sum(case when rn=1 then 1 else 0 end) cnt 
    from ( 
    	select 
    		customer_id, 
    		row_number() over (partition by customer_id order by orderdate) rn 
    	from T_orders 
    ) t;  
    
    -- union 
    select count(customer_id) cnt 
    from ( 
    	select customer_id 
    	from T_orders 
    	union 
    	select customer_id 
    	from T_orders 
    ) t

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

### 五、三种分页方法(sql server) ###

CREATE TABLE TestTable ( 
    	ID int 
    );  
    
    insert into TestTable values (1); 
    insert into TestTable values (3); 
    insert into TestTable values (4); 
    insert into TestTable values (5); 
    insert into TestTable values (7); 
    insert into TestTable values (9); 
    insert into TestTable values (11); 
    insert into TestTable values (12); 
    insert into TestTable values (13); 
    insert into TestTable values (15); 
    insert into TestTable values (16); 
    insert into TestTable values (17); 
    insert into TestTable values (19); 
    insert into TestTable values (20); 
    insert into TestTable values (24); 
    insert into TestTable values (25); 
    insert into TestTable values (22); 
    insert into TestTable values (21); 
    insert into TestTable values (20);   
    
    -- Q:按ID的顺序获取第11-15条记录

-- 方案一：利用NOT IN和SELECT TOP分页语句形式  
    
    SELECT TOP 5 * 
    FROM TestTable 
    WHERE ID NOT IN (
    	SELECT TOP 10 ID 
    	FROM TestTable 
    	ORDER BY ID 
    ) ORDER BY ID    
    
    -- 方案二：利用ID大于多少和SELECT TOP分页语句形式  
    SELECT TOP 5 * 
    FROM TestTable 
    WHERE ID > ( 
    	SELECT MAX(id) 
    	FROM  (
    		SELECT TOP 10 id 
    		FROM  TestTable 
    		ORDER BY id
    	) AS T
    ) ORDER BY ID   
    
    -- 方案三：利用SQL Server中的特性ROW_NUMBER进行分页   
    SELECT * FROM ( 
    	SELECT ROW_NUMBER() OVER(ORDER BY ID) AS rn,* 
    	FROM TestTable 
    ) AS mytable  
    where rn between 11 and 15  
    
    -- 扩展：返回随机5条记录 
    SELECT TOP 5 * 
    FROM TestTable 
    ORDER BY NEWID();

### 六、三种累加方法 ###

create table Add_Num ( 
    	OrderDate date, 
    	Amount int 
    );  
    
    insert into Add_Num values ('2021-09-01',100); 
    insert into Add_Num values ('2021-09-02',200); 
    insert into Add_Num values ('2021-09-05',100); 
    insert into Add_Num values ('2021-09-08',300); 
    insert into Add_Num values ('2021-09-12',100); 
    insert into Add_Num values ('2021-09-14',200);  
    
    -- 要求:将表里的Amount数据按日期顺序进行累加。

-- 方法一：开窗函数 
    
    SELECT  
    	OrderDate,
    	Amount,
    	SUM(Amount) OVER(ORDER BY OrderDate) Amount  
    FROM Add_Num   
    
    -- 方法二：子查询 
    SELECT 
    	A.OrderDate, 
    	( SELECT SUM(B.AMOUNT) 
    		FROM ADD_NUM B 
    		WHERE B.OrderDate <= A.OrderDate ) Amount 
    FROM ADD_NUM A   
    
    -- 方法三：笛卡尔积 
    SELECT A.OrderDate, SUM(B.AMOUNT) Amount 
    FROM ADD_NUM A 
    CROSS JOIN ADD_NUM B 
    WHERE B.OrderDate <= A.OrderDate 
    GROUP BY A.OrderDate

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

### 七、两种行转列的方法 ###

CREATE TABLE F0805A ( 
    	ID INT, 
    	机房 VARCHAR(20) 
    ); 
    
    CREATE TABLE F0805B ( 
    	ID INT, 
    	机房ID INT, 
    	主机名称 VARCHAR(20), 
    	主机状态 INT 
    );  
    
    INSERT INTO F0805A VALUES (1,'机房A'); 
    INSERT INTO F0805A VALUES (2,'机房B'); 
    INSERT INTO F0805A VALUES (3,'机房C');  
    INSERT INTO F0805B VALUES (1,1,'主机A',1); 
    INSERT INTO F0805B VALUES (2,1,'主机B',1); 
    INSERT INTO F0805B VALUES (3,1,'主机C',2); 
    INSERT INTO F0805B VALUES (4,2,'主机D',0); 
    INSERT INTO F0805B VALUES (5,2,'主机E',1);  
    
    -- Q:希望可以看到每个机房的主机状态分别有多少个？

-- 方法一：case when 
    SELECT  
    	A.机房, 
    	SUM(CASE WHEN B.主机状态=0 THEN 1 ELSE 0 END) '0', 
    	SUM(CASE WHEN B.主机状态=1 THEN 1 ELSE 0 END) '1', 
    	SUM(CASE WHEN B.主机状态=2 THEN 1 ELSE 0 END) '2' 
    FROM F0805A A 
    LEFT JOIN F0805B B 
    ON A.ID=B.机房ID 
    GROUP BY A.机房   
    
    -- 方法二：PIVOT 
    SELECT * 
    FROM ( 
    	SELECT A.机房,B.主机名称,B.主机状态 
    	FROM F0805A A 
    	LEFT JOIN F0805B B 
    	ON A.ID=B.机房ID 
    ) X PIVOT ( COUNT(主机名称) FOR 主机状态 IN ([0],[1],[2]) ) XX

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

### 八、如何每隔15分钟分组 ###

CREATE TABLE F0810 ( 
    	ID INT, 
    	TIMES VARCHAR(50) 
    );  
    
    INSERT INTO F0810 VALUES (1,'2019-12-25 11:01'); 
    INSERT INTO F0810 VALUES (2,'2019-12-25 11:03'); 
    INSERT INTO F0810 VALUES (3,'2019-12-25 11:05'); 
    INSERT INTO F0810 VALUES (4,'2019-12-25 11:09'); 
    INSERT INTO F0810 VALUES (5,'2019-12-25 11:17'); 
    INSERT INTO F0810 VALUES (6,'2019-12-25 11:19'); 
    INSERT INTO F0810 VALUES (7,'2019-12-25 11:29'); 
    INSERT INTO F0810 VALUES (8,'2019-12-25 11:37');  
    
    -- Q:查询出每隔15分钟的记录数

SELECT 
    	FROM_UNIXTIME(t.unit_time * 900, 
    	'%Y-%m-%d %H:%i:%s') Times,  
    	t.cnt 
    FROM ( 
    	SELECT 
    		CEIL(UNIX_TIMESTAMP(Times) / 900) as unit_time,  
    		count(ID) cnt 
    	FROM F0810  
    	GROUP BY unit_time 
    ) t;

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

[e57276db96db437d8e2bceefbcd52e60.png]: https://img-blog.csdnimg.cn/e57276db96db437d8e2bceefbcd52e60.png
[f41fc085e3274b548dd3265afab3f695.png]: https://img-blog.csdnimg.cn/f41fc085e3274b548dd3265afab3f695.png
[bca16d316ae44bd9abcbcd415729b4d4.png]: https://img-blog.csdnimg.cn/bca16d316ae44bd9abcbcd415729b4d4.png
[d5ec4cd946434f00bc6f9165ab79256d.png]: https://img-blog.csdnimg.cn/d5ec4cd946434f00bc6f9165ab79256d.png
[c7ab1c386a4e4a0baf99e7d47d5c16b8.png]: https://img-blog.csdnimg.cn/c7ab1c386a4e4a0baf99e7d47d5c16b8.png
[2f99f2cb3f694f6badba7f77352bb200.png]: https://img-blog.csdnimg.cn/2f99f2cb3f694f6badba7f77352bb200.png