declare @t table
(数量 int,单位 varchar(10))
insert @t
select 15,'瓶' union all
select 23,'杯' union all
select 15,'袋' union all
select 18,'盒' union all
select 78,'瓶' union all
select 10,'瓶'select case when 单位='瓶' then cast(数量*1.0/35 as decimal(10,2))
when 单位='杯' then cast(数量*1.0/48 as decimal(10,2))
when 单位='盒' then cast(数量*1.0/20 as decimal(10,2))
else 数量 end as 数量,
case when 单位='瓶' then '篮'
when 单位='杯' then '箱'
when 单位='盒' then '箱'
else 单位 end as 单位
from @t数量 单位
-------------- ----------
.43 篮
.48 箱
15.00 袋
.90 箱
2.23 篮
.29 篮(所影响的行数为 6 行)
(数量 int,单位 varchar(10))
insert @t
select 15,'瓶' union all
select 23,'杯' union all
select 15,'袋' union all
select 18,'盒' union all
select 78,'瓶' union all
select 10,'瓶'select case when 单位='瓶' then cast(数量*1.0/35 as decimal(10,2))
when 单位='杯' then cast(数量*1.0/48 as decimal(10,2))
when 单位='盒' then cast(数量*1.0/20 as decimal(10,2))
else 数量 end as 数量,
case when 单位='瓶' then '篮'
when 单位='杯' then '箱'
when 单位='盒' then '箱'
else 单位 end as 单位
from @t数量 单位
-------------- ----------
.43 篮
.48 箱
15.00 袋
.90 箱
2.23 篮
.29 篮(所影响的行数为 6 行)
create table #t(id int identity(1,1),num int, unit varchar(20))insert into #t(num,unit) select 15,'瓶'
insert into #t(num,unit) select 23,'杯'
insert into #t(num,unit) select 15,'袋'
insert into #t(num,unit) select 18,'盒'
insert into #t(num,unit) select 78,'瓶'
insert into #t(num,unit) select 10,'瓶'
select * from #t order by idselect
case
when unit ='瓶' then round(num /35.0,2)
when unit ='杯' then round(num /48.0,2)
when unit ='盒' then round(num /20.0,2)
else num
end as num,
case
when unit ='瓶' then '篮'
when unit ='杯' then '箱'
when unit ='盒' then '箱'
else unit
end as unit
from #t
order by iddrop table #t
(数量 int,单位 varchar(10))
insert @t
select 15,'瓶' union all
select 23,'杯' union all
select 15,'袋' union all
select 18,'盒' union all
select 78,'瓶' union all
select 10,'瓶'
select case when 单位='瓶' then cast(数量/35 as varchar) + '蓝' + cast(数量%35 as varchar) + '瓶'
when 单位='杯' then cast(数量/48 as varchar) + '箱' + cast(数量%48 as varchar)+ '杯'
when 单位='盒' then cast(数量/20 as varchar) + '箱' + cast(数量%20 as varchar)+ '盒'
else cast(数量 as varchar) + 单位 end
from @t
select case when 单位='瓶' then (case when 数量/35>=1 then cast(数量/35 as varchar) + '蓝' + cast(数量%35 as varchar) + '瓶' else cast(数量 as varchar) + 单位 end )
when 单位='杯' then (case when 数量/48>=1 then cast(数量/48 as varchar) + '箱' + cast(数量%48 as varchar)+ '杯' else cast(数量 as varchar) + 单位 end )
when 单位='盒' then (case when 数量/20>=1 then cast(数量/20 as varchar) + '箱' + cast(数量%20 as varchar)+ '盒' else cast(数量 as varchar) + 单位 end )
else cast(数量 as varchar) + 单位 end
from @t