sql-server-2008 – TSQL将总和转换为Money

使用sql Server 2008,我试图让其中一些列返回$xxx,xxx.xx

这是我正在使用的查询(此查询然后一些更新只是为了计算数字并在结尾选择## tempshow)

SELECT 
    CASE GROUPING(s.StoreID) WHEN 1 THEN '' ELSE s.StoreID END [StoreID],CASE GROUPING(p.VendorID) WHEN 1 THEN '' ELSE p.VendorID END [VendorID],SUM(d.Quantity) AS [UnitSold],CAST(SUM(d.amount * d.quantity) AS DECIMAL(18,2)) AS Amount,CAST(SUM(d.Discount) AS DECIMAL(18,2)) AS Discount,CAST(SUM((d.Amount * d.Quantity - d.Discount)) AS DECIMAL(18,2)) AS ExtSold,CAST(SUM(d.Cost * d.Quantity) AS DECIMAL(18,2)) AS Cost,CAST(0 AS DECIMAL(18,2)) AS Profit,2)) AS OnHand,2)) AS OnHandRetail,2)) AS OnHandCost,2)) AS ReceivedCost,2)) AS ReceivedRetail,2)) AS ReceivedQty,2)) AS Margin,CAST(0 AS DECIMAL(12,2)) AS TurnOver,CAST(0 AS INTEGER) AS OnOrder 
INTO
    ##tempshow
FROM 
    RPTrs s,RPTrsd d,RPIv i,RPProducts p
WHERE 
    s.ReceiptNO = d.ReceiptNO and 
    s.StoreID = d.StoreID and 
    i.UPC = d.UPC and 
    i.StoreID = d.StoreID and 
    p.ProductID = i.IVProduct and 
    s.StoreID = '01' and
    s.TRSDate > GETDATE()-20 and 
    p.Service = 0 
GROUP BY 
    GROUPING SETS((s.StoreID,p.VendorID),())

哪个回报：

我试过了

CAST(SUM(d.amount * d.quantity)AS MONEY)作为金额,

和

SUM(CAST((d.amount * d.quantity)AS MONEY))AS金额,

预期输出(加上与此Amount列相同的其他列)：

|StoreID | VendorID | UnitSold | Amount
---------------------------------------------
1 | 01     | 0000     | 0        | $0.00
2 | 01     | am       | 62       | $6,275.00
3 | 01     | AO       | 58       | $18,964.00
4 | 01     | payless  | 6        | $1,383.36
5 |        |          | 126      | $26,622.36

我需要Amount,Discount,ExtSold,Cost,Profit,OnHandRetail,OnHandCost,ReceivedCost,ReceivedRetail为货币格式

解决方法

这是应该在表示层上完成的,但是如果你需要在sql中执行此操作,则可以使用：

'$'+convert(varchar(50),CAST(amount as money),-1) amount

这是一个例子：

;with cte (amount)
as
(
    select 123254578.00 union all
    select 99966.00 union all
    select 0.00 union all
    select 6275.00 union all 
    select 18964.00 union all 
    select 1383.36 union all
    select 26622.36
)
select '$'+convert(varchar(50),-1) amount
from cte

见SQL Fiddle with Demo.这将返回：

|          AMOUNT |
-------------------
| $123,254,578.00 |
|      $99,966.00 |
|           $0.00 |
|       $6,275.00 |
|      $18,964.00 |
|       $1,383.36 |
|      $26,622.36 |

注意：这在sql Server 2012中会更容易,因为您可以使用FORMAT()

;with cte (amount)
as
(
    select 123254578.00 union all
    select 99966.00 union all
    select 0.00 union all
    select 6275.00 union all 
    select 18964.00 union all 
    select 1383.36 union all
    select 26622.36
)
select '$'+FORMAT(amount,'#,0.0000') amount
from cte

见SQL Fiddle with Demo

sql-server-2008 – TSQL将总和转换为Money

解决方法

猜你在找的MsSQL相关文章