SQL多字段模糊查询

​

代码是在VC++里面:

CString sql, str;
  sql.Format(_T("SELECT * FROM tbPatient WHERE admitDate  BETWEEN CDate('%s %s') AND CDate('%s %s')"),
    dtStartDate.Format(_T("%x")), dtStartTime.Format(_T("%X")),
    dtEndDate.Format(_T("%x")), dtEndTime.Format(_T("%X")));


  //字段合并的模糊查询 MYSQL concat(email,address) like 'like%df%'
  if(!strKeyWord.IsEmpty())
  {
    //SQL Server中IsNull(expression, replace)有替换功能, 
    //Access需要结合IsNull ( expression ), iif (condition, value_if_true, value_if_false )函数
    
    //str.Format(_T(" AND IsNull(name, ' ') & IsNull(Empid, ' ') LIKE '%%%s%%'"), 
    //  strKeyWord.GetBuffer(0));


    str.Format(_T(" AND iif(IsNull(name),' ',name) & iif(IsNull(Empid),' ',Empid) LIKE  '%%%s%%'"), 
      strKeyWord.GetBuffer(0));


    sql += str;
  }


  TRACE1("%s\n", sql);

一种改写的代码

CString sql, str;
  sql.Format(_T("SELECT * FROM tbPatient"));
  
  if (!m_bDisplayAll)   //非全部显示则添加搜索条件
  {
    str.Format(_T(" WHERE admitDate BETWEEN CDate('%s %s') AND CDate('%s %s')"),
      dtStartDate.Format(_T("%x")), dtStartTime.Format(_T("%X")),
      dtEndDate.Format(_T("%x")), dtEndTime.Format(_T("%X")));


    sql += str;


    //字段合并的模糊查询 MYSQL concat(email, address) like 'like%df%'
    if(!strKeyWord.IsEmpty())
    {
      //SQL Server中IsNull(expression, replace)有替换功能, 
      //Access需要结合IsNull ( expression ), iif (condition, value_if_true, value_if_false )函数


      //str.Format(_T(" AND IsNull(name, ' ') + IsNull(Empid, ' ') LIKE '%%%s%%'"), 
      //  strKeyWord.GetBuffer(0));


      str.Format(_T(" AND iif(IsNull(name),' ',name) & iif(IsNull(Empid),' ',Empid) LIKE  '%%%s%%'"), 
        strKeyWord.GetBuffer(0));


      sql += str;
    }
  }


  //sql += _T(" ORDER BY ASC");     //DESC, 添加排序


  TRACE1("%s\n", sql);