我有一個用3個CTE撰寫的查詢，我需要只用一個CTE來獲得完全相同的結果。誰能幫助我？
WITH Q1(cid, sumEP) as
(
SELECT customerid, SUM(Extendedprice)
From "Invoices"
GROUP BY customerid
), Q2(cid, oid, mf) as
(
SELECT DISTINCT customerid, orderid, freight
FROM "發票"
), Q3 AS
(
SELECT cid, SUM（mf） AS smf
FROM Q2
GROUP BY cid
)
SELECT Q1.cid, sumEP smf AS total
FROM Q1
JOIN Q3 ON Q1.cid = Q3.cid
LIMIT10
uj5u.com熱心網友回復：
smf是每個訂單的freight的不同值的總和（對于同一個客戶），但是這個你可以用sum(distinct freight)
所以我建議這樣查詢：
WITH Q(customerid, orderid, total) as
(
SELECT customerid, orderid, SUM(Extendedprice) SUM(DISTINCT Freight)
FROM發票
GROUP BY customerid, orderid
)
SELECT customerid, SUM(total) AS total
FROM Q
GROUP BY customerid
訂單 BY 2 DESC
LIMIT 10;
當不存在同一客戶的不同訂單具有相同運費值的情況時，那么可以簡化為：
SELECT customerid, SUM(Extendedprice) SUM(DISTINCT freight) As total
FROM發票
GROUP BY customerid
訂單 BY 2 DESC
LIMIT10
注意，我添加了一個order by，這樣你就可以用背后的一些邏輯來限制結果--在這種情況下，按總數獲得前10名。根據需要進行修改。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/322148.html
標籤：
下一篇：透視資料T-SQL