mysql和sql2008兼容group by中带函数的情况 max

love聊网络那点事：http://weibo.com/u/3923942488

SELECT ISNULL(b.ALL_COUNT, 0) ALL_COUNT,ISNULL(a.YP_COUNT, 0) YP_COUNT,w.WBS_NAME NAME,w.NODE_CODE NODE_CODE ,s.SYSTEM_NAME , w.* 
	FROM T_PROJECT_WBS w 
		LEFT JOIN (
			SELECT AIRCREW_ID, SUBSTRING(MAX(i.WBS_CODE), 1, len(MAX(i.WBS_CODE))-  ?) PARENT_CODE, 
			CASE WHEN ISNULL(MIN(STATE),0)=4 
				THEN COUNT(DISTINCT WBS_ID) 
				ELSE 0 
				END YP_COUNT 
			FROM t_project_inspect_info i  
			WHERE i.AT_PAGE_NO=1 AND i.PROJECT_ID=?  AND i.SYSTEM_ID = ?   
			GROUP BY AIRCREW_ID, SUBSTRING(i.WBS_CODE, 1, len(i.WBS_CODE)-  ?)  ) a 
			ON w.NODE_CODE = a.PARENT_CODE AND w.AIRCREW_ID = a.AIRCREW_ID  
		LEFT JOIN (  
			SELECT AIRCREW_ID, SUBSTRING(MAX(t.NODE_CODE), 1, len(MAX(t.NODE_CODE))-  ?) PARENT_CODE, COUNT(1) ALL_COUNT 
			FROM T_PROJECT_WBS t  WHERE t.PROJECT_ID=?  AND t.SYSTEM_ID = ?  
			GROUP BY AIRCREW_ID, SUBSTRING(t.NODE_CODE, 1, len(t.NODE_CODE)-  ?)  ) b 
			ON w.NODE_CODE = b.PARENT_CODE AND w.AIRCREW_ID = b.AIRCREW_ID   
		LEFT JOIN (
			SELECT MAX(SYSTEM_NAME) FROM t_standard_system a 
			GROUP BY a.SYSTEM_ID) s 
			ON s.SYSTEM_ID = w.SYSTEM_ID   
		WHERE w.PROJECT_ID=?  AND w.SYSTEM_ID = ? AND len(w.NODE_CODE) = len(?)+? AND w.NODE_CODE LIKE ?  
		ORDER BY node_code;
		
不加max：mysql可以运行成功，sqlserver2008客户端可以运行成功，但是在程序中会报WBS_CODE不存在group by中的错误。
		
		
			
			
			
			

             love聊网络那点事： http://weibo.com/u/3923942488