我想顯示 Invoice_Amount 的運行總計。這是我當前的查詢：
SELECT cust_name, COUNT(*) as Invoice_Amount, Invoice.invoice_date
FROM Customer, Invoice
WHERE Customer.customer_id = Invoice.customer_id
GROUP BY Invoice.customer_id, Customer.cust_name,invoice_date;
這是當前的輸出：
cust_name Invoice_Amount invoice_date
Smith Bros Ltd 1 1996-04-20 00:00:00.000
Smith & Smith Solicitors 1 1996-05-22 00:00:00.000
BigBus Ltd 1 1996-05-30 00:00:00.000
ANIMAID 1 1996-06-12 00:00:00.000
Chopsticks 1 1996-06-20 00:00:00.000
Chopsticks 1 1998-04-20 00:00:00.000
我希望 Invoice_Amount 在這兩種情況下都像這樣讀取 2：
cust_name Invoice_Amount invoice_date
Smith Bros Ltd 1 1996-04-20 00:00:00.000
Smith & Smith Solicitors 1 1996-05-22 00:00:00.000
BigBus Ltd 1 1996-05-30 00:00:00.000
ANIMAID 1 1996-06-12 00:00:00.000
Chopsticks 2 1996-06-20 00:00:00.000
Chopsticks 2 1998-04-20 00:00:00.000
這樣我最終可以按照以下方式做一些事情：
HAVING (COUNT(*) > 1)
我將如何得到這個結果
uj5u.com熱心網友回復：
不需要 aGROUP BY或 a HAVING，因為您實際上并沒有按最終結果中的任何內容進行分組。
;;;/* CTE with leading semi-colons for safety */;;;WITH src AS
(
SELECT c.cust_name, i.invoice_date,
COUNT(i.invoice_date) OVER (PARTITION BY i.customer_id)
AS Invoice_Count
FROM dbo.Customer AS c
INNER JOIN dbo.Invoice AS i
ON c.customer_id = i.customer_id
)
SELECT cust_name, Invoice_Count, invoice_date
FROM src
-- WHERE Invoice_Count > 1;
uj5u.com熱心網友回復：
好吧，從您的資料來看，invoice_date和的組合cust_name似乎是唯一的 - 因為 COUNT(*) 總是回傳 1。
您現在似乎需要您呼叫的計數值invoice_amount來計算相同的值cust_name。“Chopsticks”在您的報告中出現了兩次，對于“Chopsticks”，您需要值 2。但是，您仍然希望保留這兩行。
對聚合資料進行排序但仍回傳與輸入相同行數的函式不是 GROUP BY 或聚合函式，它們是視窗函式或 OLAP/分析函式。
因此，從您的分組查詢開始，然后從中選擇，應用 OLAP 函式，然后依次從該外部查詢中選擇，過濾 OLAP 函式的結果：
WITH
grp AS (
SELECT
cust_name
, count(*) AS invoice_amount
, invoice.invoice_date
FROM customer
JOIN invoice ON customer.customer_id = invoice.customer_id
GROUP BY
invoice.customer_id
, customer.cust_name
, invoice_date;
)
,
olap AS (
SELECT
cust_name
, SUM(invoice_amount) OVER(PARTITION BY cust_name) AS invoice_amount
, invoice_date
FROM grp
)
SELECT
*
FROM olap
WHERE invoice_amount > 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/452424.html