如何从MySQL表中选择具有最大值的行

I have a table with multiple rows for each customer and and a visit_date. The visit date can be null as well.

我有一个表，每个客户都有多行，还有一个visit_date。访问日期也可以为空。

My tables are as below:

我的表格如下：

customers:

顾客：

id  |  name      | email 
1   |  John Doe1 | [e-mail] 
2   |  John Doe2 | [e-mail]
3   |  John Doe3 | [e-mail]

store_customers

store_customers

id |  customer_id  | store_id | email_optedin |  visit_date 
1  |     1         |   1      |   1         |   2015-11-30
2  |     1         |   2      |   1         |   2016-05-08
3  |     1         |   3      |   1         |    null
4  |     2         |   1      |   1         |   2015-04-30
5  |     2         |   2      |   1         |   2015-08-40
6  |     2         |   3      |   1         |   2015-12-12
7  |     3         |   1      |   1         |   null
8  |     3         |   2      |   1         |   null
9  |     3         |   3      |   1         |   null

I am trying to retrieve customers who either have not had a visit to the any of the three stores or have not visited since a specified date (e.g. 2016-04-15).

我正在尝试检索那些未访问过三家商店中任何一家或自指定日期以来未访问过的客户（例如2016-04-15）。

I am expecting customers 2 and 3 but not 1.

我期待客户2和3但不是1。

I tried this query:

我试过这个查询：

select distinct * from customers 
inner join store_customers on store_customers.customer_id = customers.id 
    where customers.email != '' and 
    store_customer.store_id in (1,2,3) and customers.emailStatus not in ('Unverified','Bounced','Spammed') 
    and 
    (
       store_customer.email_optedin = 1 
       and max(store_customers.visit_date) <= '2016-04-15'  
       or account_customer.visit_date is null
    );

This does not work. I somehow need to, for the set of store ids), I need to select customers who have either not had any visit (all nulls for visit date for the specified stores) or the if one or more visit dates are available then compare the max date to the specified date.

这不起作用。我需要，对于商店ID的集合，我需要选择没有任何访问的客户（指定商店的访问日期的所有空值）或者如果一个或多个访问日期可用，则比较最大值日期到指定日期。

I found similar questions but none of the answers has worked for me, mainly because of the requirement of selecting either those customers who have no visit or if they do atleast one, then to compare the latest visit date from the set of stores in the joined table.

我发现了类似的问题，但没有一个答案对我有用，主要是因为需要选择那些没有访问的客户或者他们至少做过一个，然后比较加入的商店集中的最新访问日期表。

I am trying to do this all in one query but if that is not possible then I can break it up as well. I also do not want to change the order of joins because there are many other things added on to this query and changing the order of joins may become a problem.

我试图在一个查询中完成所有这一切，但如果这是不可能的，那么我也可以分解它。我也不想更改连接的顺序，因为此查询中添加了许多其他内容，并且更改连接的顺序可能会成为问题。

I really appreciate any help that can be provided.

我真的很感激可以提供任何帮助。

Regards,

问候，

Waqar

瓦卡

1 个解决方案

#1

Try this query

试试这个查询

SELECT

        customers.id,
        customers.name,
        MAX(store_customers.visit_date)

    FROM

    customers LEFT JOIN store_customers on customers.id = store_customers.customer_id

    GROUP BY customers.id,customers.name

    HAVING MAX(store_customers.visit_date) < '2016-04-15' OR MAX(store_customers.visit_date) IS NULL