面试题：MySQL复杂计算字段与数据拼接在多表场景中的应用

Question

现有两个表，`orders`表包含`order_id`、`customer_id`和`order_date`字段；`customers`表包含`customer_id`、`customer_name`和`city`字段。要求编写SQL查询，显示每个订单的详细信息，包括`order_id`、`customer_name`（将`customers`表中的`customer_name`字段与所在`city`拼接，格式为`customer_name (city)`），以及计算出从订单日期到当前日期的天数作为一个新的计算字段`days_since_order`。

Answer 1

SELECT
    o.order_id,
    CONCAT(c.customer_name, ' (', c.city, ')') AS customer_name,
    DATEDIFF(CURRENT_DATE, o.order_date) AS days_since_order
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id;