表:
a,b,c,d,e,f 这些列我想这样:
select a,b ,c,d,e,f ,(如果有名为g的列,则f*g as h,否则f*0 as h) where ......
如何构造这样的sql语句?注:g列可能有,也可能没有
a,b,c,d,e,f 这些列我想这样:
select a,b ,c,d,e,f ,(如果有名为g的列,则f*g as h,否则f*0 as h) where ......
如何构造这样的sql语句?注:g列可能有,也可能没有
,case when exists(select 1 from INFORMATION_SCHEMA.COLUMNS where table_name ='tbl' and column_name= 'g' )
then f*g else 0 end as h
from tbl
where ...
dbo.AssayMainB.MaterialName, dbo.AssayMainB.EnterpriseName,
dbo.AssayMainB.CarNum, dbo.CarInfoB.CarNo, dbo.CarInfoB.CarWeight,
dbo.AssaySubB.AssayName, dbo.AssaySubB.AssayValue
FROM dbo.AssayMainB INNER JOIN
dbo.AssaySubB ON
dbo.AssayMainB.AssayID = dbo.AssaySubB.AssayID INNER JOIN
dbo.CarInfoB ON dbo.AssayMainB.AssayID = dbo.CarInfoB.AssayID
WHERE (dbo.AssayMainB.MaterialID = '''+@materialid+''') AND
(dbo.AssayMainB.AssayDate BETWEEN '''+@begintime+''' AND
'''+@endtime+''') AND (dbo.AssayMainB.WorkState = 2) AND
(dbo.AssayMainB.AuditingState = 1) AND (dbo.AssayMainB.ParkFlag = 0))TEM'set @s=''
select @s=@s+',sum(case Assayname when '''+assayname+''' then assayvalue else 0.00 end) as ' +'"' +assayname+ '"'
from ( select DISTINCT(assayname) from AssaySubB where dbo.AssaySubB.upid=@MaterialID ) a select @s='SELECT AssayID as 化验流水号, MaterialID as 物料编号, MaterialName as 物料名称, enterprisename as 供货商,
carno as 车牌号码,carweight as 净重 '+@s+' from ' +@where +
' group by AssayID, MaterialID, MaterialName,EnterpriseName, CarNo, CarWeight ORDER BY ASSAYID'现在我想在动态语句中,如果有assayname为水,就水*净重,否则0*净重
material为标本名称,有铁矿石、焦炭等等,这些标本化验有水含量,有的没有,我想统一处理,有就乘上真实值,没有就乘0,
干基净重=净重*水含量,形成这样的动态sql语句
select a,b,c,d,e,f,干基重量,化验项目名称 from ....
assayid assayname assayvalue
1 fe2 0.34
1 al203 0.56
1 水含量 0.2
........动态sql语句说是将子表的行转为列.
dbo.AssayMainB.MaterialName, dbo.AssayMainB.EnterpriseName,
dbo.AssayMainB.CarNum, dbo.CarInfoB.CarNo, dbo.CarInfoB.CarWeight,
dbo.AssaySubB.AssayName, dbo.AssaySubB.AssayValue
FROM dbo.AssayMainB INNER JOIN
dbo.AssaySubB ON
dbo.AssayMainB.AssayID = dbo.AssaySubB.AssayID INNER JOIN
dbo.CarInfoB ON dbo.AssayMainB.AssayID = dbo.CarInfoB.AssayID
WHERE (dbo.AssayMainB.MaterialID = '''+@materialid+''') AND
(dbo.AssayMainB.AssayDate BETWEEN '''+@begintime+''' AND
'''+@endtime+''') AND (dbo.AssayMainB.WorkState = 2) AND
(dbo.AssayMainB.AuditingState = 1) AND (dbo.AssayMainB.ParkFlag = 0))TEM' select @s='SELECT AssayID as 化验流水号, MaterialID as 物料编号, MaterialName as 物料名称, enterprisename as 供货商,
carno as 车牌号码,carweight as 净重 ,sum(case Assayname when ''水含量'' then assayvalue else 0.00 end) as 水含量 from ' +@where +
' group by AssayID, MaterialID, MaterialName,EnterpriseName, CarNo, CarWeight ORDER BY ASSAYID'
--是不是你说的意思?