訪問相同表上不同計數和計數的子查詢

你好我已經瀏覽了一段時間的論壇，在這里問我的第一個問題。我有點糾結，想知道是否可以得到一些幫助。我正在使用 Access，但尚未在網上找到該問題的良好答案。

我有一個名為 tblTransactions 的表，用于 Access 2013 上的事務。它看起來像這樣：

tblProd 看起來像：

tblLicence 看起來像：

客戶購買該產品一次，可獲得該產品的多個許可證。該產品只支付一次，但針對擁有的所有許可證。

我想為交易創建一個摘要串列。我無法在客戶編號旁邊列印它有多少不同的產品以及它總共有多少許可證。

我想要的輸出應該是這樣的：

我為前 3 列嘗試了不同的方法。

1. 當我嘗試使用子查詢時，客戶編號和產品計數是正確的，但它也會從許可證中洗掉重復項。

SELECT C.Customer_No, T2.Count_Uniq_Prods, T2.Count_Licences" & _
        FROM" & _
            (SELECT T1.Customer_No, T1.Count_Uniq_Prods, Count(L?cence_ID) As Count_Licences"
            FROM" & _
                (SELECT DISTINCT Customer_No, L?cence_ID, Count(Prod_ID) As Count_Uniq_Prods
                FROM tblTransactions  GROUP BY Customer_No, L?cence_ID ) AS T1
            GROUP BY T1.Customer_No, T1.Count_Uniq_Prods) AS T2
        INNER JOIN tblTransactions  AS C
        ON T2.Customer_No = C.Customer_No" & _
        GROUP BY C.Customer_No, T2.Count_Uniq_Prods, T2.Count_Licences;

2. 當我嘗試左連接操作時，我可以成功分別獲取產品和許可證的結果，但是當我想在單個表中獲取它時，結果不是我想要的。

它適用于 Customer_No、Count_Uniq_Prods、Sum_Prods_Price：

SELECT T.Customer_No,
Count(T.Prod_ID), SUM(tblProd.Prod_Price) AS Sum_Prods_Price
FROM ((SELECT DISTINCT Customer_No, Prod_ID FROM tblTransactions ) AS T
LEFT JOIN tblProd ON tblProd.Prod_ID= T.Prod_ID)
GROUP BY T.Customer_No;

它適用于 Customer_No、Count_Licences、Sum_Licences_Price：

SELECT T.Customer_No,
Count(T.L?cence_ID), SUM(tblLicence.[L?cence_Price]) AS Sum_Licences_Price
FROM ((SELECT Customer_No, L?cence_ID FROM tblTransactions ) AS T
LEFT JOIN tblLicence ON tblLicence.L?cence_ID = T.L?cence_ID)
GROUP BY T.Customer_No

但是，當我將一個作為另一個內部的子查詢時，我無法在兩個結果中都達到預期的結果。

我希望我能夠解釋清楚。提前感謝您的幫助。

uj5u.com熱心網友回復：

這應該適合你。

我的方法是把你的問題分解成它的組成部分。

此查詢以您請求的格式檢索產品。

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

標籤：sql 数据库 毫秒访问 ms-access-2013

上一篇：什么python物件型別與ms-access日期/時間欄位兼容？

下一篇：在表單打開時將物件保留在記憶體中（訪問）