select cast( sum(cast(replace(Specs,'米','0') as decimal(10,2))) as varchar)+'米' from BA_Material
where MaterialSortCode = '02040' 1楼大哥 我这个应该转换decimal类型 然后 这个怎么从varchar类型转换成decimal类型 原本Specs的类型是varchar类型 谢谢了
create table BA_Material(MaterialCode varchar(10),MaterialName varchar(10),Specs varchar(10), Model varchar(10),Quantity decimal(18,6),ParentCode varchar(10)) insert BA_Material select 'T0322QZB01','起重臂','10.23米','TC...',1.000000,'101-322' union all select 'T0322QZB09','起重臂','10.22米','TC...',1.000000,'101-322' union all select 'T0322QZB02','起重臂','10米','Q70/...',1.000000,'101-323' union all select 'T0322QZB09','起重臂','5米','Q70/...',1.000000,'101-323' select cast(SUM(cast(replace(Specs,'米','')as decimal(18,2)))as varchar(100))+'米' from BA_Material /* 35.45米 */
DROP TABLE BA_Material create table BA_Material ( MaterialCode varchar(10) ,MaterialName varchar(10),Specs varchar(10), Model varchar(10),Quantity decimal(18,6),ParentCode varchar(10) ) go insert BA_Material select 'T0322QZB01','起重臂','10.23米','TC...',1.000000,'101-322' union all select 'T0322QZB09','起重臂','10.22米','TC...',1.000000,'101-322' union all select 'T0322QZB02','起重臂','10米','Q70/...',1.000000,'101-323' union all select 'T0322QZB09','起重臂','5米','Q70/...',1.000000,'101-323'; with cte as ( select row_number()over(order by(select 0))id,*,cast(replace(Specs,'米','')as decimal(18,2))Specs1 from BA_Material ) select id,MaterialCode,MaterialName,SUM(Specs1) Specs,model,Quantity,ParentCode from cte group by grouping sets ( (id,MaterialCode,MaterialName,model,Quantity,ParentCode), () )
where MaterialSortCode = '02040' 1楼大哥 我这个应该转换decimal类型 然后 这个怎么从varchar类型转换成decimal类型 原本Specs的类型是varchar类型 谢谢了
create table BA_Material(MaterialCode varchar(10),MaterialName varchar(10),Specs varchar(10),
Model varchar(10),Quantity decimal(18,6),ParentCode varchar(10))
insert BA_Material
select 'T0322QZB01','起重臂','10.23米','TC...',1.000000,'101-322' union all
select 'T0322QZB09','起重臂','10.22米','TC...',1.000000,'101-322' union all
select 'T0322QZB02','起重臂','10米','Q70/...',1.000000,'101-323' union all
select 'T0322QZB09','起重臂','5米','Q70/...',1.000000,'101-323' select cast(SUM(cast(replace(Specs,'米','')as decimal(18,2)))as varchar(100))+'米'
from BA_Material
/*
35.45米
*/
create table BA_Material
(
MaterialCode varchar(10)
,MaterialName varchar(10),Specs varchar(10),
Model varchar(10),Quantity decimal(18,6),ParentCode varchar(10)
)
go
insert BA_Material
select 'T0322QZB01','起重臂','10.23米','TC...',1.000000,'101-322'
union all
select 'T0322QZB09','起重臂','10.22米','TC...',1.000000,'101-322'
union all
select 'T0322QZB02','起重臂','10米','Q70/...',1.000000,'101-323'
union all
select 'T0322QZB09','起重臂','5米','Q70/...',1.000000,'101-323'; with cte as
(
select row_number()over(order by(select 0))id,*,cast(replace(Specs,'米','')as decimal(18,2))Specs1 from BA_Material
)
select id,MaterialCode,MaterialName,SUM(Specs1) Specs,model,Quantity,ParentCode
from cte
group by grouping sets
(
(id,MaterialCode,MaterialName,model,Quantity,ParentCode),
()
)