面试题中的一道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