Question

SQL数字显示格式

因为业务需要，公司需要查询所有产品从今天开始之前60天的库存情况和出入口详细信息，我使用的查询语句如下：
SQL codeselect t1.FNumber,convert(varchar,t1.FDate,104) as FDate,t2.iJCQuantity,t1.RKSL,t1.CKSL
from

(
SELECT
Rs.cInvCode as FNumber,
R.dDate as FDate ,
W.cWhCode as FcWhCode,
I.cInvCode as FcInvCode,
(CASE WHEN R.bRdFlag<>0 THEN '入库' ELSE '出库' END) AS SFBZ,

Vt.cVouchName AS DJLX,
(CASE WHEN R.bRdFlag<>0 THEN Rs.iQuantity ELSE '' END) AS RKSL,
(CASE WHEN R.bRdFlag<>0 THEN '' ELSE Rs.iQuantity END) AS CKSL,
(CASE WHEN R.bRdFlag<>0 THEN Rs.iUnitCost ELSE '' END) AS RKDJ,
(CASE WHEN R.bRdFlag<>0 THEN convert(varchar(20),Rs.iPrice) ELSE '' END) AS RKJE

FROM Customer C
RIGHT OUTER JOIN Department D
RIGHT OUTER JOIN SaleType
RIGHT OUTER JOIN Vendor V
RIGHT OUTER JOIN VouchType Vt
INNER JOIN Warehouse W
INNER JOIN Inventory I
INNER JOIN RdRecords Rs
INNER JOIN RdRecord R

ON Rs.ID = R.ID
ON I.cInvCode = Rs.cInvCode
ON W.cWhCode = R.cWhCode
ON Vt.cVouchType = R.cVouchType
ON V.cVenCode = R.cVenCode
ON SaleType.cSTCode = R.cSTCode LEFT OUTER JOIN PurchaseType Pt
ON R.cPTCode = Pt.cPTCode LEFT OUTER JOIN Rd_Style Rd
ON R.cRdCode = Rd.cRdCode
ON D.cDepCode = R.cDepCode
ON C.cCusCode = R.cCusCode

AND ( R.[cWhCode]='0001'
OR R.[cWhCode]='0002'
OR R.[cWhCode]='0003'
OR R.[cWhCode]='0004'
OR R.[cWhCode]='0005'
OR R.[cWhCode]='0006'
OR R.[cWhCode]='0007'
OR R.[cWhCode]='0008'
OR R.[cWhCode]='0009'
OR R.[cWhCode]='9001'
OR R.[cWhCode]='9002' )

)
t1,

(
SELECT CS.cWhCode as FcWhCode, CS.cInvCode as FcInvCode ,

LTRIM(STR(CS.iQuantity,20,2)) AS iJCQuantity

FROM (Warehouse AS W INNER JOIN CurrentStock AS CS ON W.cWhCode = CS.cWhCode) INNER JOIN Inventory AS I ON

CS.cInvCode = I.cInvCode
WHERE (1=1) AND W.cWhCode = '0001' or W.cWhCode='0003'
) t2
where t1.FcWhCode=t2.FcWhCode and t1.FCInvCode=t2.FCInvCode
and datediff(day,t1.FDate,getdate())<=60
order by convert(varchar,t1.FDate,121)

其中某一产品的显示结果为
220820205 31.05.2010 93.00 98.0 0.0
其中“220820205 ”为产品编码，“31.05.2010”为日期，“93.00”为库存数量，“98.0”为入库数量，“0.0”为出库数量，可现在公司要求产品编码应该显示为“220 820 205”，每三个数字一个空格，好像是德国那边的数字格式，还有要求入库和出库数量如果为零的话必须使用“空”代替，个人感觉这两个要求的实现需要在前面第一行定义的时候加上参数，就跟日期那个一样，可就是不知道怎么加参数，加什么参数。
希望各位高手能指点一二，在此先谢过了！

Answer 1

印像中没有那种参数,不过可以用以下的方法实现,可能笨了一点,供你参考
conver 函数将money的数据类型转为字符串时可以转为带逗号的格式如convert(varchar,323232.11)=323,232.11,我们可以利用这种格式,先转为money,再转为字符串,然后把里面的逗号替换为空格再去掉后面的小数点来实现你的要求,如在你的sql语句里这样写:
select replace(replace(convert(varchar,convert(money,t1.FNumber),1),',',' ' ),'.00','')
其中通过convert(varchar,convert(money,t1.FNumber),1) 把它转换为了xxx, xxx,xxx.00格式,然后通过replace函数将逗号和'.00'替换掉

以上代码在sqlserver里测试过

Answer 2

这是sqlplus命令，用于格式化输出，在sqlplus中执行。
SQL>column id for 999,999
SQL>column name for a10
SQL>column age for 999

Answer 3

用sql 里写函数把结果转一下

Answer 4

select stuff(stuff(t1.FNumber,4,0,' '),8,0,' ') as FNumber,convert(varchar,t1.FDate,104) as FDate,t2.iJCQuantity,(case when ltrim(str(t1.RKSL))='0' then '' else cast(t1.RKSL as varchar(20)) end) as RKSL,(case when ltrim(str(t1.CKSL))='0' then '' else cast(t1.CKSL as varchar(20)) end) as CKSL
from