TSQL:使用列列的动态顺序用于货币柱数

Question

我们在数据库中有一个动态查询，如下

以下是我们的示例查询。

 Select * from (
SELECT 
dbo.FormatAmountforCustomQuery(FORMAT(SUM(pcptlc.Value),'N')) AS [Planned] 
,dbo.FormatAmountforCustomQuery(FORMAT((REPLACE(REPLACE([Committed],',',''),'$','') - SUM(pcptlc.Value)),'N')) AS [To Be Planned]   
,dbo.FormatAmountforCustomQuery(FORMAT(SUM(pcptla.Value),'N')) AS [Actual]   
,dbo.FormatAmountforCustomQuery(FORMAT((REPLACE(REPLACE([Committed],',',''),'$','') - SUM(pcptla.Value)),'N')) AS [Remaining] 
from XPV.MyFIlterView d1 
) as FilteredView
ORDER BY cast(replace(3,'$','') as float) 

我们想使用上述查询列号进行订单，但它不起作用，它使用列名来工作，请您建议。

Answer 1

尝试此

Select * from (
SELECT 
dbo.FormatAmountforCustomQuery(FORMAT(SUM(pcptlc.Value),'N')) AS [Planned] 
,dbo.FormatAmountforCustomQuery(FORMAT((REPLACE(REPLACE([Committed],',',''),'$','') - SUM(pcptlc.Value)),'N')) AS [To Be Planned]   
,dbo.FormatAmountforCustomQuery(FORMAT(SUM(pcptla.Value),'N')) AS [Actual]   
,dbo.FormatAmountforCustomQuery(FORMAT((REPLACE(REPLACE([Committed],',',''),'$','') - SUM(pcptla.Value)),'N')) AS [Remaining] 
from XPV.MyFIlterView d1 
) as FilteredView
ORDER BY 1,4 DESC

您也可以与Clouse一起使用

WITH FilteredView AS (
SELECT 
dbo.FormatAmountforCustomQuery(FORMAT(SUM(pcptlc.Value),'N')) AS [Planned] 
,dbo.FormatAmountforCustomQuery(FORMAT((REPLACE(REPLACE([Committed],',',''),'$','') - SUM(pcptlc.Value)),'N')) AS [To Be Planned]   
,dbo.FormatAmountforCustomQuery(FORMAT(SUM(pcptla.Value),'N')) AS [Actual]   
,dbo.FormatAmountforCustomQuery(FORMAT((REPLACE(REPLACE([Committed],',',''),'$','') - SUM(pcptla.Value)),'N')) AS [Remaining] 
from XPV.MyFIlterView d1 
)

SELECT * FROM FilteredView
ORDER BY 1,4 DESC