订单数 欠发数 A库数 B库数
200.0000 100.0000 100.0000 0.0000
240.0000 240.0000 0.0000 0.0000
30.0000 30.0000 0.0000 0.0000
180.0000 180.0000 0.0000 0.0000结果
订单数 欠发数 A库数 B库数
200.0000 100.0000 100.0000
240.0000 240.0000
30.0000 30.0000
180.0000 180.0000
200.0000 100.0000 100.0000 0.0000
240.0000 240.0000 0.0000 0.0000
30.0000 30.0000 0.0000 0.0000
180.0000 180.0000 0.0000 0.0000结果
订单数 欠发数 A库数 B库数
200.0000 100.0000 100.0000
240.0000 240.0000
30.0000 30.0000
180.0000 180.0000
from tb
订单数,欠发数,
case when A库数=0.0000 then null end as A库数,
case when b库数=0.0000 then null end as B库数
from
tb
case A数 when A数 then A数 else null end A数,
case B数 when B数 then B数 else null end B数仍然是这个效果,非NULL
订单数 A数 B数
400.0000 100.0000 0.0000
200.0000 100.0000 0.0000
240.0000 0.0000 0.0000
30.0000 0.0000 0.0000
180.0000 0.0000 0.0000
订单数,欠发数,
cast(case when A库数=0.0000 then null end as varchar) as A库数,
cast(case when b库数=0.0000 then null end as varchar) as B库数
from
tb
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
订单数 float,
欠发数 float,
A库数 float,
B库数 float
)
go
--插入测试数据
insert into tb select 200.0000,100.0000,100.0000,0.0000
union all select 240.0000,240.0000,0.0000,0.0000
union all select 30.0000,30.0000,0.0000,0.0000
union all select 180.0000,180.0000,0.0000,0.0000
go
--代码实现select
订单数,欠发数,
case when A库数=0.0000 then '' else rtrim(A库数) end as A库数,
case when b库数=0.0000 then '' else rtrim(b库数) end as B库数
from
tb /*测试结果订单数 欠发数 A库数 B库数
---------------------------------
200 100 100
240 240
30 30
180 180 (4 行受影响)
*/
select 订单数,欠发数,
case when A库数<>0 then A库数 end,
case when B库数<>0 then B库数 end
from TB
(
[value] DECIMAL(10, 4)
)
INSERT #temp
SELECT 0.0000
GO
--SQL:
SELECT
[value] = CASE [value] WHEN 0 THEN NULL ELSE [value] end
FROM #temp