时间戳

首页 > 解决方案 > 从 MySQL 中分组和连接表的特定行中选择数据

mysql - 从 MySQL 中分组和连接表的特定行中选择数据

问题描述

我有两个表，客户和订单是内部连接的。一个客户可以有多个与其关联的订单。在我的选择中，我然后按customers.id 分组。我需要选择每个客户最近的订单，以及在该订单上花费的金额。目前，我可以选择最近的order_date，但不知道如何选择与order_date在同一行的金额。

这是我当前的查询：

SELECT 
    first_name, 
    last_name, 
    email, 
    MAX(order_date) AS recent_order, 
    amount -- this needs to select amount associated with recent_order
FROM customers
JOIN orders
    ON customers.id = orders.customer_id
GROUP BY customers.id;

该查询选择最近的日期，但不选择与最近的 order_date 关联的金额。

表声明：

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id) REFERENCES customers(id)
);

标签： mysqlsqlgroup-by

解决方案

SELECT 
    first_name, 
    last_name, 
    email, 
    MAX(order_date) AS recent_order, 
    (SELECT amount FROM orders WHERE order_date = MAX(order_date) AND customers.id = orders.customer_id) as amount
FROM customers
JOIN orders
    ON customers.id = orders.customer_id
GROUP BY customers.id;

或者

SELECT 
    first_name, 
    last_name, 
    email, 
    order_date AS recent_order, 
    amount AS recent_order_amount
FROM customers
JOIN orders
    ON customers.id = orders.customer_id

GROUP BY customers.id
ORDER BY orders.order_date DESC;

推荐阅读