使用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金额,
|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
