如果不存在数据，则返回数据 - 用数据填充 null

Question

我创建了一个查询，我希望结果如下所示：

Reporting Date    Fund    AssetClass    %
31/10/2012        1       Equity        10
31/10/2012        1       Bond          40
31/10/2012        1       Cash          40
31/10/2012        1       Balanced      10
31/10/2012        1       Other         0

我上面遇到的问题是 % 为 0，并且显然没有显示，因为没有数据。 但是我希望它显示。

所以我认为最好的解决方案是创建一个包含所有资产类别的临时表，然后从我的工作表中将右外连接到它，以便它填充一行没有数据。 代码如下：

SELECT 
    ReportingDate
,   PortfolioID
,   AC.AssetClass
,   ROW_NUMBER() OVER (PARTITION BY PortfolioID ORDER BY SUM(Percentage) DESC) AS [Rank]
,   CAST(SUM(Percentage) AS DECIMAL(22,1))  AS [Weight]
FROM @Worktable as WT
RIGHT OUTER JOIN @AssetClass AS AC
    ON WT.AssetClass = AC.AssetClass
GROUP BY WT.ReportingDate, WT.PortfolioID, AC.AssetClass
ORDER BY [Weight] DESC

我的问题是，当它返回时，它看起来像这样：

Reporting Date    Fund    AssetClass    %
31/10/2012        1       Equity        10
31/10/2012        1       Bond          40
31/10/2012        1       Cash          40
31/10/2012        1       Balanced      10
NULL              NULL    Other         NULL

如何在此脚本中用数据填充 NULL？ 有没有其他更好的方法可以做到这一点？

Answer 1

SELECT 
    CASE WHEN Reporting IS NULL THEN MAX(Reporting) OVER (PARTITION BY (SELECT 1)) ELSE Reporting END AS Reporting
,   CASE WHEN Fund IS NULL THEN MAX(Fund) OVER (PARTITION BY (SELECT 1)) ELSE Fund END AS Fund
,   PortfolioID
,   AC.AssetClass
,   ROW_NUMBER() OVER (PARTITION BY PortfolioID ORDER BY SUM(Percentage) DESC) AS [Rank]
,   CAST(SUM(ISNULL(Percentage, 0)) AS DECIMAL(22,1))  AS [Weight]
FROM @Worktable as WT
RIGHT OUTER JOIN @AssetClass AS AC
    ON WT.AssetClass = AC.AssetClass
GROUP BY WT.ReportingDate, WT.PortfolioID, AC.AssetClass
ORDER BY [Weight] DESC