Access SQL-表2中按城市计算的月数与表1不同

Question

我有一张桌子(订单)，上面有CustomerID，产品，销售和日期(Yyyy)。

CustomerID链接到CustomerName的Customer表。Customer表有Customername、City、State、Country字段。

我想数一数每个城市订购的日历年的唯一月份数。

不幸的是，因为我只能统计所有客户和日期的不同月份。我怎样才能把它绑到顾客餐桌上的每一个城市？

SELECT Count(*) AS CountOfMonths
FROM (
SELECT DISTINCT Month(Date), Year(Date)
FROM Orders );

谢谢!

Answer 1

就像这样：

select country, city, state,
       count(*) as NumDistinctMonths
from (select c.country, c.state, c.city,
             year(o.date) as yyyy, month(o.date) as mm, count(*) as cnt
      from orders as o inner join
           customers as c
           on o.CustomerID = c.CustomerId
      group by c.country, c.state, c.city, year(o.date), month(o.date)
     ) as ccsym
group by country, city, state;

Answer 2

只需按年份和城市加入表格和汇总，并计算不同的月份：

select c.country, c.state, c.city, year(o.date), count(distinct month(o.date))
from orders o
join customers c on c.customername = o.customerid
group by c.country, c.state, c.city, year(o.date)
order by c.country, c.state, c.city, year(o.date);

添加一个WHERE子句，如果您只想看到一年。

更新： MS Access不支持COUNT(DISTINCT expresssion)。因此，您需要一个独立的子查询：

select c.country, c.state, c.city, order_year, count(*)
from
(
  select distinct c.country, c.state, c.city, year(o.date) as order_year, month(o.date)
  from orders o
  join customers c on c.customername = o.customerid
) as dist_months
group by country, state, city, order_year
order by country, state, city, order_year;