访问同一表上的Distinct Count和Count的子查询

Question

你好，我浏览了论坛一段时间，在这里问我的第一个问题。我有点不知所措，想知道是否能帮上忙。我正在使用Access，还没有在网上找到这个问题的好答案。

我有一个名为tblTransactions的表，用于Access 2013上的事务。它看起来像这样：

Lıcene_ID<1>

Transaction_ID	客户编号	产品ID
1	111	1		1
2	111	1	2
3	222	1	2
4	111	2	1
5	222	2	1
6	222	2	2
7	333	1

这应该对您有效。

我的方法是把你的问题分解成几个组成部分。

此查询检索产品，格式为请求。

select customer_no, count(t.prod_id) as Count_Uniq_Prods, 
sum(p.prod_price) as Sum_Prods_Price
from  ( 
select customer_no, prod_id
from tblTransactions t
group by customer_no, prod_id
) t
inner join tblProd p on
t.prod_id = p.prod_id
group by customer_no

此查询检索许可证，其格式为请求。

select customer_no, count(t. license_id) as Count_Licenses, 
sum(l.license_price) as Sum_Licenses_Price
from tblTransactions t
inner join tblLicense l on 
t.license_id = l.license_id
group by customer_no

最后，我们将它们放在一起，得到以下内容：

select distinct  p.customer_no, Count_Uniq_Prods, 
Count_Licenses,
Sum_Prods_Price,
Sum_Licenses_Price         
from (
select customer_no, count(t.prod_id) as Count_Uniq_Prods, 
sum(p.prod_price) as Sum_Prods_Price
from  ( 
select customer_no, prod_id
from tblTransactions t
group by customer_no, prod_id
) t
inner join tblProd p on
t.prod_id = p.prod_id
group by customer_no

) p
inner join (
select customer_no, count(t. license_id) as Count_Licenses, 
sum(l.license_price) as Sum_Licenses_Price
from tblTransactions t
inner join tblLicense l on 
t.license_id = l.license_id
group by customer_no
) l
on p.customer_no = l.customer_no