<問題>ACCESS的 DISTINCT 、子查詢問題

sql access 資料庫

0805cyc 2023-04-13 16:37:21 ‧ 875 瀏覽

分享至

今天是用ACCESS 做練習，要COUNT(LINE.INV_NUMBER) 發現數字不對，會把LINE.INV_NUMBER本身的數量算進去，有查到可用DISTINCT只計算不同的商品數。會忽略每個商品的數量，但ACCESS不支援 COUNT( DISTINCT LINE.INV_NUMBER)這種寫法，
有查到需用"子查詢表示"，嘗試找網路資料寫過但都還是有錯誤，想在板上詢問大師們需如何改寫，謝謝

此語法ACCESS不支援

SELECT INVOICE.CUS_CODE, COUNT( DISTINCT LINE.INV_NUMBER) AS num_of_invoices, SUM(LINE.LINE_PRICE*LINE.LINE_UNITS) AS total_purchase_amount
FROM INVOICE, LINE, CUSTOMER
WHERE  LINE.INV_NUMBER=INVOICE.INV_NUMBER AND INVOICE.CUS_CODE = CUSTOMER.CUS_CODE
GROUP BY INVOICE.CUS_CODE;

嘗試改成'子查詢'

SELECT INVOICE.CUS_CODE,(SELECT COUNT(*) FROM (SELECT DISTINCT INV_NUMBER FROM LINE WHERE INV_NUMBER= INVOICE.INV_NUMBER)) AS number_of_invoices, SUM(LINE.LINE_PRICE*LINE.LINE_UNITS) AS total_purchase_amount
FROM INVOICE, LINE, CUSTOMER
WHERE  LINE.INV_NUMBER=INVOICE.INV_NUMBER AND INVOICE.CUS_CODE = CUSTOMER.CUS_CODE
GROUP BY INVOICE.CUS_CODE;

錯誤顯示:要求輸入INVOICE.INV_NUMBER的參數值

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

davidliu9116

iT邦研究生 2 級 ‧ 2023-09-04 13:29:19

SELECT a.CUSCODE
     , b.numofinvoices
     , c.totalpurchaseamount
  FROM INVOICE as a left join
       (select DISTINCT INVNUMBER, count(INVNUMBER) as  numofinvoices
          from LINE
         group by b.INVNUMBER
       ) as b on b.INVNUMBER=a.INVNUMBER  left join
       (select INVNUMBER, SUM(LINEPRICELINEUNITS) AS totalpurchaseamount
          from LINE
         group by b.INVNUMBER
       ) as c on c.INVNUMBER=a.INVNUMBER;