標題: 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待查詢的關(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表示輸出字符串, 默認給搜索關(guān)鍵字前后加上%號, 這是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需要結(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.torrancerestoration.com/bbs/) |
Powered by Discuz! X3.1 |