久久久久久久999_99精品久久精品一区二区爱城_成人欧美一区二区三区在线播放_国产精品日本一区二区不卡视频_国产午夜视频_欧美精品在线观看免费

標(biāo)題: SQL合并多個(gè)字段的模糊查詢 [打印本頁(yè)]

作者: bibi 時(shí)間: 2015-4-18 20:53
標(biāo)題: SQL合并多個(gè)字段的模糊查詢

代碼是在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"))); //時(shí)間范圍 CTime類(lèi)型

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

if(!strKeyWord.IsEmpty()) //strKeyWord待查詢的關(guān)鍵字

{

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

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

//strKeyWord.GetBuffer(0));

//Access需要結(jié)合IsNull ( expression ), iif (condition, value_if_true, value_if_false )函數(shù)

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表示輸出字符串, 默認(rèn)給搜索關(guān)鍵字前后加上%號(hào), 這是SQL模糊匹配的符號(hào),

改寫(xiě)
========================================================================
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需要結(jié)合IsNull ( expression ), iif (condition, value_if_true, value_if_false )函數(shù)

            //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.zg4o1577.cn/bbs/)