高顿教育线下笔试题

1、假设有几张表，他们的结构如下：
客户表customer：（customer_id，customer_name）
订单表orders：（order_id，customer_id，order_time）
客户行为轨迹表action：（customer_id，action_time）
客户沟通记录表communication：（customer_id，communicated_time）
数据示例：

a.请编写一个sql查询，按照每个客户最新的下单时间倒序输出客户姓名
错误写法：

正确写法：

SELECT
	customer_name 
FROM
	(
SELECT
	customer.customer_id,
	customer_name,
	MAX( order_time ) AS latest_order_time 
FROM
	customer
	JOIN ORDERs ON customer.customer_id = ORDERs.customer_id 
GROUP BY
	customer.customer_id,
	customer.customer_name 
	) AS subquery 
ORDER BY
	latest_order_time DESC;

b.请编写一个sql查询，已知一个客户id，查询客户的最新下单时间、最新行为时间、最新沟通时间。（客户表数量级为千万，每个客户的行为和沟通可能超过几千条）
解析：该题不能用时间的order by去写，不满足题意

SELECT
	c.customer_id,
	MAX( o.order_time ) AS latest_order_time,
	MAX( a.action_time ) AS latest_action_time,
	MAX( co.communicated_time ) AS latest_communicated_time 
FROM
	customer c
	JOIN orders o ON c.customer_id = o.customer_id
	JOIN action a ON c.customer_id = a.customer_id
	JOIN communication co ON c.customer_id = co.customer_id 
WHERE
--动态传参
	c.customer_id = 2
GROUP BY
	c.customer_id;