【SQL】— 面试SQL，订单金额统计

面试题中的一道sql题，比较考察对sql函数的掌握程度，记录一下，方便学习。

 

需求：

 

        一张客户表 tb_cust 如下：

CUST_ID	varchar(30)	客户编号（primary key）
CERTIFICATE_NO	varchar(18)	身份证号（not null）

        

        一张订单表tb_oreder 如下：

 

CUST_ID	varchar(30)	客户编号
APPLY_ID	varchar(50)	订单编号（primary key）
AMOUNT	double	订单金额

根据上面两张表提供的信息，写出如下效果图所示的sql语句。

注意：

1.年龄信息没有具体提供。

2.不能使用 union 等连接字符。

 

答案如下：

select 
		case when k.flag = 1 then '30岁以下' when k.flag = 2 then '30-50岁之间' when k.flag = 3 then '50岁以上' ELSE '总计' end as'年龄区间',
		COUNT(DISTINCT(a.cust_id)) as '客户数量',COUNT(k.flag) as '订单数量', SUM(AMOUNT) as '订单总额' 
		from tb_oreder a 
		left join 
		(select c.cust_id,IF(c.age < 30,1,IF(c.age<50,2,3)) as flag 
			from (select cust_id,FLOOR(PERIOD_DIFF(EXTRACT(YEAR_MONTH from NOW()),substring(CERTIFICATE_NO FROM 7 FOR 6))/12) as age 
						from tb_cust) c
		 )k on a.CUST_ID = k.CUST_ID 
			GROUP BY k.flag WITH ROLLUP;

知识点整理：

 

1.Mysql中的with rollup是用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息。

2.if函数：if(a > b ? true : false)效果等同于java中的三目运算符。

3.PERIOD_DIFF(P1,P2):计算周期P1和P2之间的月数。 P1和P2格式为YYMM或YYYYMM。注意周期参数 P1 和 P2 都不是日期值

4.mysql的 substring(str from pos for len) 起点从1开始。例如：当str为身份证号：37152719990101034时，substring(str from 7 for 6)的值为：199101

版权声明：本文为博主原创文章，未经博主允许不得转载。https://blog.csdn.net/graylf/article/details/80275021