http://user.qzone.qq.com/382164370#!app=2&via=QZ.HashRefresh&pos=1346996967
代码是在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);