组合查询问题

Question

我想实现的是，根据员工编号，查询年份，月份，3个条件可任意组合查询，当分开查询时正确的，组合查询出错误，因为查询语句里面重复了   group by  attendanceEmloyeeNo,employeeName,departmentName   语句，有高人帮忙解决下，修改SQL语句也行在业务层里
/*得到某员工在某年某月的考勤签到次数,迟到次数的sql语句*/
        private  string SQL_SELECT_AttendanceNUM ="select attendanceEmloyeeNo ,employeeName  ,departmentName ,count(*) as chidaoNum  from attendanceState where not ( datepart(hh,attendanceStartTime) <=departmentStartHour and datepart(mi,attendanceStartTime) <departmentStartMin) " ;        private  string SQL_SELECT_AttendanceNUM_NO = " and attendanceEmloyeeNo=@attendanceEmloyeeNo group by  attendanceEmloyeeNo,employeeName,departmentName ";        private string SQL_SELECT_AttendanceNUM_YEAR = " and attendanceYear=@attendanceYear group by  attendanceEmloyeeNo,employeeName,departmentName ";        private string SQL_SELECT_AttendanceNUM_MONTH = " and attendanceMonth=@attendanceMonth group by  attendanceEmloyeeNo,employeeName,departmentName ";  /*各种sql语句的参数常量字符串*/
        private const string PARM_ATTENDANCE_ID = "@attendanceId";
        private const string PARM_ATTENDANCE_EMPLOYEENO = "@attendanceEmloyeeNo";
        private const string PARM_ATTENDANCE_YEAR = "@attendanceYear";
        private const string PARM_ATTENDANCE_MONTH = "@attendanceMonth";
        private const string PARM_ATTENDANCE_DAY = "@attendanceDay";
        private const string PARM_ATTENDANCE_START_TIME = "@attendanceStartTime";
        private const string PARM_ATTENDANCE_END_TIME = "@attendanceEndTime"; /*根据员工编号，考勤年份，月份查询考勤统计信息*/
        public DataSet QueryAtendanceNum(string attendanceEmployeeNo, int attendanceYear, int attendanceMonth)
        {
            /*保存参数的个数*/
            int parmCount = 0;            /*保存年份和月份查询条件信息参数对象的链表*/
            ArrayList array = new ArrayList();
            /*如果填写了查询的员工编号*/
            if (attendanceEmployeeNo != "")
            {
                /*参数个数加1*/
                parmCount++;
                /*构建员工编号参数信息*/
                SqlParameter parm_attendanceEmployeeNo = new SqlParameter(PARM_ATTENDANCE_EMPLOYEENO, SqlDbType.VarChar);
                parm_attendanceEmployeeNo.Value = attendanceEmployeeNo;
                array.Add(parm_attendanceEmployeeNo);
                SQL_SELECT_AttendanceNUM += SQL_SELECT_AttendanceNUM_NO;
            }
            /*如果填写了查询的年份*/
            if (attendanceYear != 0)
            {
                parmCount++;
                SqlParameter parm_attendanceYear = new SqlParameter(PARM_ATTENDANCE_YEAR, SqlDbType.Int);
                parm_attendanceYear.Value = attendanceYear;
                array.Add(parm_attendanceYear);
                SQL_SELECT_AttendanceNUM += SQL_SELECT_AttendanceNUM_YEAR; 
             }
            /*如果填写了查询的月份*/
            if (attendanceMonth != 0)
             {
                parmCount++;
                SqlParameter parm_attendanceMonth = new SqlParameter(PARM_ATTENDANCE_MONTH, SqlDbType.Int);
                parm_attendanceMonth.Value = attendanceMonth;
                array.Add(parm_attendanceMonth);
                SQL_SELECT_AttendanceNUM += SQL_SELECT_AttendanceNUM_MONTH; 
             }
           
            /*建立调用数据层代码的参数数组*/
            SqlParameter[] parms = new SqlParameter[parmCount];
            /*得到各个参数*/
            for (int i = 0; i < array.Count; i++)
            {
                parms[i] = this.GetItemsParms(array)[i];
            }
            /*调用数据层执行查询并返回Dataset*/
            return DBOperation.GetDataSet(DBOperation.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_AttendanceNUM, parms);
            
        }

Answer 1

/*根据员工编号，考勤年份，月份查询考勤统计信息*/
public DataSet QueryAtendanceNum(string attendanceEmployeeNo, int attendanceYear, int attendanceMonth)
{
    System.Data.SqlClient.SqlParameter parameter = null;
    System.Collections.ArrayList parmsList = new System.Collections.ArrayList();
    System.Text.StringBuilder sb = new System.Text.StringBuilder("");    string groupby = " group by attendanceEmloyeeNo,employeeName,departmentName ";    sb.Append(" select attendanceEmloyeeNo ,employeeName  ,departmentName , count(*) as chidaoNum ");
    sb.Append(" from attendanceState where not ( datepart(hh,attendanceStartTime) <=departmentStartHour ");
    sb.Append(" and datepart(mi,attendanceStartTime) <departmentStartMin) ");    if (attendanceEmployeeNo != "")
    {
        sb.Append(" and attendanceEmloyeeNo=@attendanceEmloyeeNo ");        parameter = new System.Data.SqlClient.SqlParameter("@attendanceEmloyeeNo", SqlDbType.VarChar);
        parameter.Value = attendanceEmployeeNo;
        parmsList.Add(parameter);
    }    if (attendanceYear != 0)
    {
        sb.Append(" and attendanceYear=@attendanceYear ");        parameter = new System.Data.SqlClient.SqlParameter("@attendanceYear", SqlDbType.Int);
        parameter.Value = attendanceYear;
        parmsList.Add(parameter);
    }    if (attendanceMonth != 0)
    {
        sb.Append(" and attendanceMonth=@attendanceMonth ");        parameter = new System.Data.SqlClient.SqlParameter("@attendanceYear", SqlDbType.Int);
        parameter.Value = attendanceMonth;
        parmsList.Add(parameter);
    }    sb.Append(groupby);    return DBOperation.GetDataSet(DBOperation.CONN_STRING_NON_DTC, CommandType.Text, sb.ToString(), parmsList.ToArray());
} 
大致如此, 不需要定义这么多常量,变量的. 又不复杂. 

Answer 2

对了,最后一个
parmsList.ToArray()需要转换一下, 留给你自己做吧.

Answer 3

group by 
ISNULL( attendanceEmloyeeNo,@attendanceEmloyeeNo=attendanceEmloyeeNo,
ISNULL( employeeName,@attendanceEmloyeeNo=employeeName,
ISNULL( departmentName,@attendanceEmloyeeNo=departmentName如果有参数直接这样判断

Answer 4

group by 
ISNULL( attendanceEmloyeeNo,@attendanceEmloyeeNo)=attendanceEmloyeeNo,
ISNULL( employeeName,@attendanceEmloyeeNo)=employeeName,
ISNULL( departmentName,@attendanceEmloyeeNo)=departmentName
少打了两个括号,