sql - Access 查询中的返回值

Question

我需要有关 Access 中的查询的帮助。我需要返回截至今天为止的过去 14 天内拥有三个或更多订单且仍处于活动状态的客户名称。它还应该在结果中显示订单日期。这将填充在报告中并按“cusname”分组并显示每个“orderdate”。我尝试使用查询向导并输入下面的 sql，但它没有填充任何结果。有人可以帮忙吗？

Select customerid, count(*), cusname,orderdate,orderstatus
From tablename
Where orderstatus="active"
Group by customerid,cusname,orderdate,orderstatus
Having Count(*) >=3;

桌子：

CusName:|orderdate:
Mary     4/4/2021
Mary     4/3/2021
Mary     4/8/2021
Mary     3/23/2021
Bob      4/9/2021
Bob      4/1/2021

我期望的结果是：

桌子：

Customerid:|CusName:|orderdate:
1 Mary     4/4/2021
1 Mary     4/3/2021
1 Mary     4/8/2021

标签： sqlms-accessms-access-2016

Answer 1

尝试这个

SELECT t.customerid,
       t.cusname,
       t.orderdate,
       t.orderstatus
FROM   tablename AS t
WHERE  t.orderstatus = "active"
       AND t.orderdate > Dateadd("d", -14, DATE())
       AND (SELECT Count(t1.cusname)
            FROM   tablename AS t1
            WHERE  t.customerid = t1.customerid
                   AND t1.orderstatus = "active"
                   AND t1.orderdate > Dateadd("d", -14, DATE())) >= 3