是否使用仪器 仪器类别否 现场采样仪器设备是 现场采样仪器设备如何在sql中判断是 如果为否的话 仪器类别就不显示
sql1="select a.id$,a.ITEMTYPEID$,b.NAME2$,a.monitorMETHODID$,b.NAME2 as 项目类型,c.NAME as 监测项目名称,d.NAME as 标准方法,iscertified as 是否计量认证,"+
"personNum as 持证人数,ozi.name as 监测机构,"+
"a.isuseapparatus as 是否使用仪器,e.name as 仪器类别 from MONITORITEMSINFO a,ITEMTYPE b,Organizationinfo ozi,MONITORITEMS c,MONITORMETHODS d,APPARATUSTYPE e "+
"where a.ITEMTYPEID = b.ID and a.MONITORID = c.ID and a.monitorMETHODID=d.ID and a.orgid=ozi.id and e.id=a.apparatustype";
sql1="select a.id$,a.ITEMTYPEID$,b.NAME2$,a.monitorMETHODID$,b.NAME2 as 项目类型,c.NAME as 监测项目名称,d.NAME as 标准方法,iscertified as 是否计量认证,"+
"personNum as 持证人数,ozi.name as 监测机构,"+
"a.isuseapparatus as 是否使用仪器,e.name as 仪器类别 from MONITORITEMSINFO a,ITEMTYPE b,Organizationinfo ozi,MONITORITEMS c,MONITORMETHODS d,APPARATUSTYPE e "+
"where a.ITEMTYPEID = b.ID and a.MONITORID = c.ID and a.monitorMETHODID=d.ID and a.orgid=ozi.id and e.id=a.apparatustype";
"personNum as 持证人数,ozi.name as 监测机构,"+
"a.isuseapparatus as 是否使用仪器,'仪器类别=’case a.isuseapparatus = '是' then e.name else '' end from MONITORITEMSINFO a,ITEMTYPE b,Organizationinfo ozi,MONITORITEMS c,MONITORMETHODS d,APPARATUSTYPE e "+
"where a.ITEMTYPEID = b.ID and a.MONITORID = c.ID and a.monitorMETHODID=d.ID and a.orgid=ozi.id and e.id=a.apparatustype";不同数据库case when的写法是不一样的,不知道楼主是什么数据库
select a.id$,a.ITEMTYPEID$,b.NAME2$,a.monitorMETHODID$,b.NAME2 as 项目类型,c.NAME as 监测项目名称,"+
"d.NAME as 标准方法,iscertified as 是否计量认证,"+
"personNum as 持证人数,ozi.name as 监测机构,a.isuseapparatus as 是否使用仪器 ,case when a.isuseapparatus='是' then e.name else '' end as 仪器类别 from MONITORITEMSINFO a,"+
"ITEMTYPE b,Organizationinfo ozi,MONITORITEMS c,MONITORMETHODS d,APPARATUSTYPE e where "+
"a.ITEMTYPEID = b.ID and a.MONITORID = c.ID and "+
" a.monitorMETHODID=d.ID and a.orgid=ozi.id and e.id=a.apparatustype但如果e.id=a.apparatustype apparatustype 是空的就不出来 怎么在case加一个条件判断呢?
"personNum as 持证人数,ozi.name as 监测机构,"+
"a.isuseapparatus as 是否使用仪器,'仪器类别=’case when a.isuseapparatus = '是' then e.name when a.isuseapparatus is null then '' else '' end from MONITORITEMSINFO a,ITEMTYPE b,Organizationinfo ozi,MONITORITEMS c,MONITORMETHODS d,APPARATUSTYPE e "+
"where a.ITEMTYPEID = b.ID and a.MONITORID = c.ID and a.monitorMETHODID=d.ID and a.orgid=ozi.id and e.id=a.apparatustype";
e.id=a.apparatustype apparatustype 是空的也出来 怎么在case加一个条件判断呢?
是apparatustype为空的出来 不是isuseapparatus
根据那个条件变
就把条件 写到case when 后边
select a.id,a.ITEMTYPEID,b.NAME2,a.monitorMETHODID,b.NAME2 as 项目类型,c.NAME as 监测项目名称,
d.NAME as 标准方法,iscertified as 是否计量认证,
personNum as 持证人数,ozi.name as 监测机构,
a.isuseapparatus as 是否使用仪器 ,
case when a.isuseapparatus='是' then aa.name else '' end as 仪器类别
from MONITORITEMSINFO a left join ITEMTYPE b on a.ITEMTYPEID = b.ID left join Organizationinfo ozi on a.orgid=ozi.id
left join MONITORITEMS c on a.MONITORID = c.ID left join MONITORMETHODS d on a.monitorMETHODID=d.ID
left join( select a.ISUSEAPPARATUS,a.APPARATUSTYPE,e.name from MONITORITEMSINFO a left outer join APPARATUSTYPE e on e.id=a.apparatustype) aa
on aa.APPARATUSTYPE=a.apparatustype
我写出来了 但不知道写的到底怎么样 谁帮我看看呢 谢谢大家 用的外连接