SQL合并多个字段的模糊查询 -

标题: SQL合并多个字段的模糊查询 [打印本页]

作者: bibi 时间: 2015-4-18 20:53
标题: 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"))); //时间范围 CTime类型

//字段合并的模糊查询 MYSQL concat(email, address) like 'like%df%'

if(!strKeyWord.IsEmpty()) //strKeyWord待查询的关键字

{

//SQL Server中IsNull(expression, replace)有替换功能,

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

//strKeyWord.GetBuffer(0));

//Access需要结合IsNull ( expression ), iif (condition, value_if_true, value_if_false )函数

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

strKeyWord.GetBuffer(0));

sql += str;

}

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

============================================================================

ps: 上面的%%%s%%是C/C++的格式化字符串输出, %%表示输出%, %s表示输出字符串, 默认给搜索关键字前后加上%号, 这是SQL模糊匹配的符号,

改写
========================================================================
CString sql, str;
sql.Format(_T("SELECT * FROM tbPatient"));

if (!m_bDisplayAll)//非全部显示则添加搜索条件
{
      str.Format(_T(" WHERE admitDateBETWEEN 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);

欢迎光临 (http://www.51hei.com/bbs/)