从MySQL中分组联接表的特定行中选择数据

Cirrus86 • 5 年前 • 1385 次点击

我有两张桌子,顾客和订单都是内部连接的。一个客户可以有几个相关联的订单。在我的选择中,我会按customers.id分组。我需要选择每个客户的最新订单,以及在该订单中花费的金额。目前,我可以选择最近的订单日期,但不知道如何选择订单日期所在行的金额。

这是我当前的查询:

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;

查询选择最近的日期,但不选择与最近的订单日期关联的金额。

表声明:

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)
);

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/54738

1385 次点击

文章 [ 3 ] | 最新文章 5 年前

• 1 楼

R Mac 5 年前

使用这个:

SELECT TOP 1
    t1.first_name, 
    t1.last_name, 
    t1.email, 
    t2.order_date,
    t2.amount
FROM customers t1
JOIN orders t2
    ON t1.id = t2.customer_id
ORDER BY
    t2.order_date

GROUP BY t1.id 如果目的是返回具有最新订单日期的所有行。如果每个订单都由 orders . 请注意,这不会计算出金额的总和。您必须在代码中执行此操作,否则将使用其他查询。还要注意,此查询的性能将受索引配置的影响。如果order_date不是索引的一部分,并且表包含大数据集,则可能无法执行此查询。

• 2 楼

db1975 5 年前

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;

• 3 楼

Gordon Linoff 5 年前

我建议在 where 条款:

SELECT c.*, o.*  -- or whatever columns you want
FROM customers c JOIN
     orders o
     ON c.id = o.customer_id
WHERE o.order_date = (SELECT max(o2.order_date)
                      FROM orders o2
                      WHERE o2.customer_id = o.customer_id
                     );

登录后回复