Mysql面试题

最新推荐文章于 2024-09-15 20:24:46 发布

2301_77804289

最新推荐文章于 2024-09-15 20:24:46 发布

阅读量169

点赞数 3

文章标签： mysql 数据库面试

本文链接：https://blog.csdn.net/2301_77804289/article/details/137397735

版权

本文介绍了如何通过SQL查询统计公司80后和90后员工的数量占比，并计算销售数据的同比和环比增长。同时展示了如何使用开窗函数处理连续登陆和数据比较问题。

摘要由CSDN通过智能技术生成

1.求公司80，90后的员工信息，数量及其占比，用两列表示。

SELECT result,COUNT(birth) counts ,
(COUNT(birth)/(SELECT COUNT(*) FROM student)) ratio
FROM
(SELECT *,YEAR(s_birth) birth,CASE 
		WHEN YEAR(s_birth) >= 1980 AND YEAR(s_birth)<1990 THEN '80后'
		WHEN YEAR(s_birth) >= 1990 AND YEAR(s_birth)<2000  THEN '90后'
		END AS result
FROM student
) temp
WHERE result IS NOT NULL
GROUP BY result
ORDER BY result

2.计算同比，环比。

sales

product

2.1不使用开窗函数实现同环比

#计算环比，这个月比上个月多多少（或者这个月的今天比上个月的今天的多多少）,12月的上个月为1月（不是一年内）
SELECT YEAR(a.销售时间) yy, MONTH(a.销售时间)  mm,SUM(a.销售数量*b.产品单价),e.lastAll,
	CONCAT(IFNULL(ROUND((SUM(a.销售数量*b.产品单价)-lastAll)/lastAll*100,2),0),'%') 环比
FROM sales a
LEFT JOIN product b ON a.产品ID=b.产品ID
LEFT JOIN (SELECT YEAR(c.销售时间) yy1, MONTH(c.销售时间) mm1,SUM(c.销售数量*d.产品单价) lastAll
		FROM sales c
		LEFT JOIN product d ON c.产品ID=d.产品ID
		GROUP BY YEAR(c.销售时间),MONTH(c.销售时间)) e
 ON (YEAR(a.销售时间)=e.yy1 AND MONTH(a.销售时间)-1=e.mm1 
 
 OR(YEAR(a.销售时间)=yy1+1 AND MONTH(a.销售时间)=1 AND mm1=12))
 
 GROUP BY  YEAR(a.销售时间), MONTH(a.销售时间) 


#计算同比，与上一年的同月或者同日相比。以下为同月相比案例。
SELECT  YEAR(c.销售时间) yy,MONTH(c.销售时间) mm,     
       SUM(c.销售数量*d.产品单价) ss,
CONCAT(IFNULL(ABS(ROUND((SUM(c.销售数量*d.产品单价)-ss1)/ss1*100,2)),0),'%') 同比

FROM sales c
LEFT JOIN product d ON c.产品ID=d.产品ID
LEFT JOIN (SELECT MONTH(a.销售时间) mm1,
                    YEAR(a.销售时间) yy1,
                    SUM(a.销售数量*d.产品单价) ss1
          FROM sales a
          LEFT JOIN product d ON a.产品ID=d.产品ID
          GROUP BY mm1,yy1) a
          ON MONTH(c.销售时间) = a.mm1 
          AND YEAR(c.销售时间)= a.yy1+1 

 GROUP BY yy, mm
 ORDER BY yy,mm ASC



#同步环比结合为一个表
SELECT  *,     
       SUM(c.销售数量*d.产品单价) ss,
CONCAT(IFNULL(ABS(ROUND((SUM(c.销售数量*d.产品单价)-ss1)/ss1*100,2)),0),'%') 同比,
CONCAT(IFNULL(ABS(ROUND((SUM(c.销售数量*d.产品单价)-ss2)/ss2*100,2)),0),'%')  环比
FROM sales c
LEFT JOIN product d ON c.产品ID=d.产品ID
LEFT JOIN (SELECT MONTH(a.销售时间) mm1,
                    YEAR(a.销售时间) yy1,
                    SUM(a.销售数量*d.产品单价) ss1
          FROM sales a
          LEFT JOIN product d ON a.产品ID=d.产品ID
          GROUP BY mm1,yy1) a
          ON MONTH(c.销售时间) = a.mm1 
          AND a.yy1 = YEAR(c.销售时间)-1	 
 LEFT JOIN  (SELECT MONTH(a.销售时间) mm2,
                    YEAR(a.销售时间) yy2,
                    SUM(a.销售数量*d.产品单价) ss2
             FROM sales a
		     LEFT JOIN product d ON a.产品ID=d.产品ID
              GROUP BY mm2,yy2) b
ON (b.yy2 = YEAR(c.销售时间) AND b.mm2+1 = MONTH(c.销售时间) OR (yy2=YEAR(c.销售时间)-1 
AND b.mm2 = 12 AND MONTH(c.销售时间) = 1))
 GROUP BY YEAR(c.销售时间), MONTH(c.销售时间)
 ORDER BY YEAR(c.销售时间), MONTH(c.销售时间) ASC

2.2使用开窗函数实现同环比，lag(列名,n,null) 某字段前n行数据，lead(列名,n,null)

#开窗函数实现环比,适用于简单和连续月份的情况。
/*LAG() 其中lag作用就只是生成上个月的营业额*/ 
SELECT a.*,IFNULL((money-before_money)/before_money*100,0) 环比 FROM
(SELECT MONTH
 
,shop
 
,MONEY
 
,LAG(MONEY, 1, NULL) OVER 
 
(PARTITION BY shop ORDER BY MONTH 
 
) AS before_money
 
FROM temp_test12) a

3.展现连续登陆三天的用户信息

统配版本，如果是连续登陆n天，则把DATEDIFF(date1,last_login)=n-1,LAG函数参数为(date1,n-1)
思路：先去重，用开窗函数获取上两行的信息(去重后的)，如果能找到上两行的数据且日期相减为2，则列出
#1.展现连续登陆三天的用户信息,通过查重后求前两天的日期和当天日期的差值为lag函数中指定值
SELECT * FROM
(SELECT *,LAG(date1,2) OVER (PARTITION BY id ORDER BY date1) last_login 
	 FROM 
(SELECT DISTINCT id,date1 FROM temp_test13 
) c
) a
WHERE DATEDIFF(date1,last_login)=2
#改写1.成with ...as ..形式
WITH t1 AS 
(SELECT DISTINCT id,date1 FROM temp_test13 ),
     t2 AS
(SELECT *,LAG(date1,2) OVER (PARTITION BY id ORDER BY date1) last_login FROM  t1)
 
 SELECT * FROM t2 WHERE DATEDIFF(date1,last_login)=2

#展现连续登陆两天的用户信息,   按照用户分组，登录时间降序排序，获取上一次登录日期
SELECT * FROM
(SELECT *,LAG(date1,1) OVER (PARTITION BY id ORDER BY date1) last_login FROM temp_test13) a
WHERE DATEDIFF(date1,last_login)=1  #判定当前登录日期的上一天是否与上一次登录日期一致，一致则判定为连续登录