这是最近数分面试小伙伴遇到的mysql面试题，在网上查了一下，没有完整答案，所以特意整理了一下，帮助正准备或已经在面试中的伙伴，每道题不止一种写法，不保证完全正确，供参考思路哈～ 欢迎留言交流，共同进步～
第一题：
现有以下三张表
CREATE TABLE `hospital` ( `HospitalId` varchar(36) NOT NULL,
`HospitalName` varchar(100) DEFAULT NULL,
`ProvinceName` varchar(45) DEFAULT NULL,
`CityName` varchar(45) DEFAULT NULL,
`Disabled` bit(1) NULL DEFAULT 0,
PRIMARY KEY (`HospitalId`)
) COMMENT='医院表'
;
CREATE TABLE `product` (
`ProductId` varchar(36) NOT NULL,
`ProductName` varchar(100) NULL,
`Price` decimal(10, 2) NOT NULL COMMENT '单价',
PRIMARY KEY (`ProductId`)
) COMMENT='产品表'
;
CREATE TABLE `sales` (
`SalesId` varchar(36) NOT NULL,
`HospitalId` varchar(36) NOT NULL,
`Period` date NOT NULL COMMENT '日期yyyy/mm/dd',
`ProductId` varchar(36) NOT NULL,
`SalesVolume` decimal(10, 2) NOT NULL COMMENT '销售数量',
PRIMARY KEY (`SalesId`),
CONSTRAINT `fk_hospitalid` FOREIGN KEY (`HospitalId`) REFERENCES `hospital` (`HospitalId`),
CONSTRAINT `fk_productid` FOREIGN KEY (`ProductId`) REFERENCES `product` (`ProductId`)
) COMMENT='销量表'
;
写出SQL语句：查询产品名称=“A药品”，在北京医院2018~2019两年的销售“金额”，排除两年销售金额总和>1000000的医院，要求查询结果如下表。
思路：
1、首先将城市是北京，产品名称是A药品， 2018和2019年销售额之和大于 1000000 的医院id 和 医院名查询出来
2、考虑按月份显示销售额，这里为了代码简洁直接用了年份，通过 sum(if())算出各个医院的每年的销售额
3、最后将两个子查询 按照 医院id进行连接，得到符合条件的最终结果
答案：
SELECT * FROM
(SELECT
hospitalid,hospitalname
FROM
hospital WHERE cityname='北京' AND hospitalid IN (
select hospitalid from product LEFT JOIN sales ON product.productid = sales.productid
WHERE productname='a药品' AND YEAR(period) in (2018,2019)
GROUP BY hospitalid
HAVING SUM(price * salesvolume) > 1000000
)) as t1
INNER JOIN
(
SELECT hospitalid,
sum(if(YEAR(period) = '2018',price * SalesVolume ,0)) as '2018',
sum(if(YEAR(period) = '2019',price * SalesVolume ,0)) as '2019'
FROM sales LEFT JOIN product on sales.ProductId = product.ProductId GROUP BY hospitalid) as t2
ON t1.hospitalid = t2.hospitalid
运行结果
第二题（与第一题同样的数据）：
写出SQL语句，查询题1的销量表中2019年任意连续三个月销售额都>0的医院。
返回字段：HospitalId，SalesMonth(2019年销量>0的所有月份，逗号隔开)
思路：
1、写一个子查询，筛选出2019年，销售额>0的医院，窗口函数按医院id进行分组 按医院ID，日期排序，并建辅助列 row_num
2、通过第一个子查询得到的月份Period_month和辅助列row_num，做减法（目的是通过相等的差，判断是否连续）
3、对 两者之差 sig_num ，做个分组，count 大于等于3，即符合条件，最后用GROUP_CONCAT 将连续月份拼接起来
答案：
SELECT
Hospitalid,
count( sig_num ) AS count_sig_num,
GROUP_CONCAT( Period_month )
FROM
(
SELECT
Hospitalid,
Period_month,
row_num,
CONVERT ( Period_month, signed ) - CONVERT ( row_num, signed ) AS sig_num
FROM
(
SELECT
Hospitalid,
MONTH ( Period ) Period_month,
row_number() over ( PARTITION BY Hospitalid ORDER BY Hospitalid, Period ) row_num
FROM
sales
WHERE
YEAR ( Period ) = '2019'
AND salesvolume > 0
) t1
) t2
GROUP BY
Hospitalid,
sig_num
HAVING
count_sig_num >= 3;
运行结果
第三题：
以下是微信聊天记录表
CREATE TABLE `wechat` (
`WechatId` varchar(36) NOT NULL,
`WechatFriendId` varchar(36) NOT NULL COMMENT '每个ID表示一对好友',
`ChatTime` datetime NOT NULL COMMENT '聊天时间',
`IsSend` bit NOT NULL COMMENT '我方发送:1，用户回复:0',
`Content` text NULL COMMENT '内容',
PRIMARY KEY (`WechatId`),
) COMMENT='微信聊天';
写出SQL语句：按月统计2020年的微信回复率
发送次数 = 一组好友在一个自然天内的所有发送记录计为1次。
回复率计算公式 =（发送次数在两个自然天内被回复）/发送次数*100%
思路：
1、先统计 2020年每月的微信发送次数（注意每个日然人一日发送多次记为1次）
2、再统计 2020年每月的微信回复的次数（注意这里回复和发送的用户要对应，还有个重要条件48小时内回复）
3、算出 回复率 = 回复次数/发送次数
答案：
with send as(
SELECT DATE_FORMAT(t1.ChatTime,'%Y-%m') cm,COUNT(1) send_cnt
FROM (
select
ChatTime,
count(distinct WechatFriendId) send_cnt
from wechat
where IsSend = 1 and year(ChatTime) = '2020'
group by 1) t1
GROUP BY 1
),
response as(
SELECT DATE_FORMAT(a.ChatTime,'%Y-%m') cm,
count(distinct a.WechatFriendId) resp_cnt
from
(select WechatFriendId,ChatTime from wechat where year(ChatTime) = '2020' and IsSend=1) a
left join
(select WechatFriendId,ChatTime from wechat where year(ChatTime) = '2020' and IsSend=0) b
on
a.WechatFriendId = b.WechatFriendId
where
datediff(b.ChatTime, a.ChatTime) <= 2 AND datediff(b.ChatTime, a.ChatTime) >= 0
group by 1
)
select
cm, resp_cnt,send_cnt,concat(round(resp_cnt/send_cnt,4)*100,'%') as '回复率'
from
send join response using(cm)
运行结果
未完。。。 持续更新中。。。
网友评论