以下sql代码查询的是部门每个项目占该部门所有项目总工时的比例,我现在想查询该部门每个项目，以及非项目（PlanId=0）占公司总工时的比例
strSql = " SELECT "
+ " t1.DeptNo, "
+ " 'ItemNo=' + t1.DeptNo + '&Year=" + strYear + "' AS ItemArgs, "
+ " d.CnName AS ItemCName, "
+ " t1.mYear, "
+ " t1.mMonth AS MonthCName, "
+ " t1.PlanID, "
+ " ISNULL(p.PName,t1.PlanID) AS CName, "
+ " t1.pHour, "
+ " t2.tHour, "
+ " t1.pHour/t2.tHour AS Rate "
+ " FROM "
+ " ( "
+ " SELECT "
+ " '" + strDeptNo + "' AS DeptNo, "
+ " SUBSTRING(CONVERT(VARCHAR(10),[DateTime],110),7,4) AS mYear, "
+ " SUBSTRING(CONVERT(VARCHAR(10),[DateTime],110),1,2) AS mMonth, "
+ " PlanID, "
+ " SUM([hour]) AS pHour "
+ " FROM ManHour "
+ " WHERE DepNo IN (SELECT DeptNo FROM Department WHERE StruDeptNo LIKE '%" + strDeptNo + "%') "
+ " AND PlanID <> '0' "
+ " GROUP BY "
+ " SUBSTRING(CONVERT(VARCHAR(10),[DateTime],110),7,4), "
+ " SUBSTRING(CONVERT(VARCHAR(10),[DateTime],110),1,2), "
+ " PlanID "
+ " ) AS t1 "
+ " LEFT JOIN "
+ " ( "
+ " SELECT "
+ " SUBSTRING(CONVERT(VARCHAR(10),[DateTime],110),7,4) AS mYear, "
+ " SUBSTRING(CONVERT(VARCHAR(10),[DateTime],110),1,2) AS mMonth, "
+ " SUM([hour]) AS tHour "
+ " FROM ManHour "
+ " GROUP BY "
+ " SUBSTRING(CONVERT(VARCHAR(10),[DateTime],110),7,4), "
+ " SUBSTRING(CONVERT(VARCHAR(10),[DateTime],110),1,2) "
+ " ) AS t2 ON t1.mYear = t2.mYear and t1.mMonth = t2.mMonth "
+ " LEFT JOIN "
+ " Department AS d ON d.DeptNo = t1.DeptNo "
+ " LEFT JOIN "
+ " M_Project AS p ON p.PCode = t1.PlanID "
+ " WHERE t1.mYear = '" + strYear + "' "
+ " ORDER BY t1.DeptNo,t1.mMonth,t1.PlanID ASC";
------解决思路----------------------
楼主还是贴出 示例数据 + 期望结果
------解决思路----------------------
看你的SQL语句大致可以定位改这两个地方的where条件，但是具体怎么改，还要你自己看看代码和要求了，那个非项目的应该可以直接该PlanId=0 这个的：
