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

来源：互联网发布：免费恢复软件下载编辑：程序博客网时间：2024/05/16 14:37

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