IFNULL(字段1,字段2),意思是当字段1是NULL时取字段2,不是NULL时取字段1的值。
举例: ifnull(schooladdress,homeaddress) address
以下代码摘自博客园仅供参考:
StringBuilder sb=new StringBuilder();
sb.append(" select ");
sb.append("t1.fullName,IFNULL(t2.actualHour,0)as actualHour,t1.planhour,t1.annotatorId,t1.annotatorId,IFNULL(t2.actualHour,0)/t1.planhour as ratio");
sb.append(" from ");
sb.append(" XXshipTB t1 ");
sb.append(" left outer join (select userid,sum(hours) as actualHour from dayworkTB where (Date(date) between '"+fromDate+"' and '"+endDate+"') group by userid) t2");
sb.append(" on ");
sb.append(" t1.annotatorId=t2.userid");
sb.append(" and abs(t2.actualHour-t1.planhour)>0.01");
sb.append(" ORDER by ");
sb.append(" ratio desc,t1.fullName asc");
String sql=sb.toString();