引言
在项目中一个报表查询功能需要查多张表,用到了内联查询。
代码如下:
public DataSet GetConfirmList(FindB_0001InfoEntity entity)
{
try
{
Database db = DBHelper.CreateDataBase();
StringBuilder sqlStr = new StringBuilder();
//报表需要查询内容
sqlStr.Append(" select * from (");
sqlStr.Append(" select ROW_NUMBER() OVER(ORDER BY ID desc) AS RowIndex,* from ( ");
sqlStr.Append(" select B_0001Info.*,A_0004Info.Name as aname,A_0001Info.StateValue as ZhuangTai,A_0009Info.EmployeeName as aen,A_0015Info.DepartmentName as adep from B_0001Info ");
sqlStr.Append(" inner join A_0004Info on A_0004Info.PlatformID=B_0001Info.PlatformID ");
sqlStr.Append(" inner join A_0001Info on A_0001Info.StateID=B_0001Info.StateID");
sqlStr.Append(" inner join A_0009Info on A_0009Info.EmployeeNO=B_0001Info.ApplicantNo");
sqlStr.Append(" inner join A_0015Info on A_0015Info.DepartmentNo=B_0001Info.Applicantdepartment");
sqlStr.Append(" where B_0001Info.StateID =6 and B_0001Info.Applicantdepartment='" + entity.DepartmentNO + "' ");
sqlStr.Append(" and B_0001Info.SubmitTime between '" +entity.BeginDatetime+ "' and '" +entity.EndDatetime+ "' ");
sqlStr.Append(" ) as b0001) as temp where ");
sqlStr.Append(" RowIndex>" + entity.StartIndex + " and ");
sqlStr.Append(" RowIndex<=" + entity.endIndex + " ");
//查询结果以便分页
sqlStr.Append(" select count(B_0001Info.ID) from B_0001Info where StateID=6 ");
sqlStr.Append(" and Applicantdepartment='" + entity.DepartmentNO + "' ");
sqlStr.Append(" and SubmitTime between '" + entity.BeginDatetime + "' and '" + entity.EndDatetime + "' ");
DbCommand cmd = db.GetSqlStringCommand(sqlStr.ToString());
DataSet ds = db.ExecuteDataSet(cmd);
return ds;
}
catch (Exception ex)
{
log4netHelper.WriteLog(typeof(B_0001InfoDAL), ex);
return null;
}
}
看起来是不是代码是不是庞多又繁杂。其实原理很简单。
内联查询
内联查询,联合查询的一种。具体来一个例子你便知道了。
select * from Name;
ID Name
1 张三
2 李四
3 王五
select * from Company;
ID Name
1 银行
2 软件
4 房地产
select a.*, b.* from Name as a inner join Company as b on a.ID=b.ID;
ID Name ID Company
1 张三 1 银行
2 李四 2 软件
未完待续
从上面可以看出,两张表ID一样的都被查询了出来组成一个新表。当然联合查询还有左联,右联,全联。项目中由于sql语句过长,还用到了stringbuilder,它是怎么运用的呢?和stringbuilder和stringbuffer还有string又有什么区别,更多精彩内容下几篇博客继续介绍。