sql - 该子查询出错

Question

已关闭。此问题不符合 Stack Overflow guidelines 。目前不接受答案。

---

已关闭 7 年前。

• 编辑问题以包含 desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem 。这将有助于其他人回答问题。
• 此问题是由拼写错误或无法再重现的问题引起的。虽然类似的问题可能是 on-topic在这里，这个问题的解决方式不太可能帮助 future 的读者。

Improve this question

此查询:

left join (select tSpent, Customerid
from   (select SUM(spent) as tSpent, Customerid,
        RN = ROW_NUMBER() OVER (PARTITION BY Customerid ORDER BY Customerid DESC)
        from   Customer_Sales
        WHERE Customerid is not null)
              where   RN = 1) Sales ON Customer_Sales.Customerid = Data.Customerid

在 RN=1 上给我一个错误行:

Incorrect syntax near the keyword 'where'

Answer 1

(select tSpent, Customerid
 from (select SUM(spent) as tSpent, Customerid,
        RN = ROW_NUMBER() OVER (PARTITION BY Customerid ORDER BY Customerid DESC)
        from   Customer_Sales
        WHERE Customerid is not null) t --use a alias for the derived table
 where RN = 1) Sales ON Customer_Sales.Customerid = Data.Customerid

不确定为什么使用row_number。您还应该按 customerid 分组。

内部查询可以简化为

select SUM(spent) as tSpent, Customerid
from  Customer_Sales
WHERE Customerid is not null
group by customerid

因为您只想获取每个客户的总支出。