/// <summary>
/// 分页SQL调用方法
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "")
{
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
sql.CountSql = strSql.ToString();
//'select ' + @Fields + ' from (
//select ' + @Fields + ', ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as rownum
//from ' + @Tables + @strWhere + @strGroupBy + ') as tmpTable
//where rownum > ' + CONVERT(nvarchar(10), @startRowIndex) +
//' and rownum <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ')'
//SELECT TOP 页大小 *
//FROM
//(
//SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1 where ? group by ?
//) A
//WHERE RowNumber > 页大小*(页数-1)
strSql.Clear();
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
strSql.AppendFormat("{0} ", fieldList);
if (!tableName.IsNullEmpty())
{
strSql.Append("from ( ");
strSql.AppendFormat("select {0}, row_number() over (order by {1}) as rownum ", fieldList, orderBy);
strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
strSql.AppendFormat(") as tmpTable where rownum > {0} and rownum <= {1}", (pageIndex - 1) * pageSize, (pageIndex - 1) * pageSize + pageSize);
}
sql.DataSql = strSql.ToString();
return(sql);
}
/// <summary>
/// 分页SQL调用方法
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "")
{
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
sql.CountSql = strSql.ToString();
strSql.Clear();
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
strSql.AppendFormat("{0} ", fieldList);
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
strSql.AppendFormat("limit {1} offset {0} ", (pageIndex - 1) * pageSize, pageSize);
sql.DataSql = strSql.ToString();
return(sql);
}
/// <summary>
/// 分页SQL调用方法
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "")
{
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
sql.CountSql = strSql.ToString();
//SELECT * FROM (
//SELECT MY_TABLE.*,ROWNUM AS MY_ROWNUM FROM (
// 括号里写实际的需要查询的SQL语句**/
//) AS MY_TABLE WHERE ROWNUM <=200/**这里是一页中的最后一条记录**/
//) WHERE MY_ROWNUM>=10 /**这里是一页中的第一条记录**/
//select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
//select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;
//select * from(select t.*,rownum rn from(select * from t_xiaoxi order by cid desc) t where rownum<10000) where rn>9980;
strSql.Clear();
strSql.Append("select ");
strSql.AppendFormat("{0} ", fieldList);
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} (select {1},rownum as my_rownum from (", tableName, fieldList);
}
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
strSql.AppendFormat("{0} ", fieldList);
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
strSql.AppendFormat(") as my_table where rownum<{0}) where my_rownum>{1}", pageSize * (pageIndex - 1) + pageSize, pageSize * (pageIndex - 1));
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
sql.DataSql = strSql.ToString();
return(sql);
}
/// <summary>
/// 分页SQL调用方法 只允许主键排序
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "")
{
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
sql.CountSql = strSql.ToString();
//select * from Student
//where Id in (
//select top 10 Id
//from(select top 3200010 Id from Student order by Id)t
//order by Id desc)
//order by Id
//SELECT * FROM (
//SELECT TOP 页面容量 * FROM (
//SELECT TOP 页面容量*当前页码 * FROM
//表 WHERE 条件 ORDER BY 字段A ASC
//)AS TEMPTABLE1 ORDER BY 字段A DESC
//) AS TEMPTABLE2 ORDER BY 字段A ASC
StringBuilder orderByExt = new StringBuilder();
foreach (string order in orderBy.Split(','))
{
string order2 = order.Trim();
if (order2.EndsWith(" desc", true, null))
{
orderByExt.AppendFormat("{0} {1},", order2.Left(order2.Length - 5), "asc");
}
else
{
orderByExt.AppendFormat("{0} {1},", order2.EndsWith(" asc", true, null) ? order2.Left(order2.Length - 4) : order2, "desc");
}
}
orderByExt.RemoveLastChar(",");
strSql.Clear();
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
if (pageSize == 1)
{
strSql.AppendFormat("top {0} ", pageSize);
strSql.AppendFormat("{0} ", fieldList);
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
}
else
{
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("{1} from (select top {0} {1} from (select top {3} {1} from {2} ", pageSize, fieldList, tableName, pageSize * pageIndex);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
strSql.AppendFormat(") as Top1 {0}) as Top2 {1} ", "order by " + orderByExt, "order by " + orderBy);
}
sql.DataSql = strSql.ToString();
return(sql);
}
/// <summary>
/// 分页SQL调用方法 MaxTop MinTop 只允许主键排序
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "")
{
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
sql.CountSql = strSql.ToString();
//顺序写法：
//SELECT TOP 页大小 *
//FROM table1
//WHERE id >=(
//SELECT ISNULL(MAX(id),0)
//FROM (
//SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id
//) A)
//ORDER BY id
//降序写法：
//SELECT TOP 页大小 *
//FROM table1
//WHERE id <=(
//SELECT ISNULL(MIN(id),0)
//FROM (
//SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id Desc
//) A)
//ORDER BY id Desc
strSql.Clear();
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
strSql.AppendFormat("top {0} ", pageSize);
strSql.AppendFormat("{0} ", fieldList);
if (pageIndex == 1)
{
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
}
else
{
if (!tableName.IsNullEmpty() && orderBy.EndsWith(" desc", true, null))
{
strSql.AppendFormat("from {0} where {1} < (select isnull(min({1}),0) from (select top {2} {1} from {3} ", tableName, pk, pageSize * (pageIndex - 1), tableName);
}
else if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} where {1} > (select isnull(max({1}),0) from (select top {2} {1} from {3} ", tableName, pk, pageSize * (pageIndex - 1), tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
strSql.Append(") tempMaxTop ) ");
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
}
sql.DataSql = strSql.ToString();
return(sql);
}
/// <summary>
/// 分页SQL调用方法
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "")
{
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
sql.CountSql = strSql.ToString();
//SELECT TOP 页大小 *
//FROM table1
//WHERE id NOT IN
//(
//SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
//)
//ORDER BY id
strSql.Clear();
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
strSql.AppendFormat("top {0} ", pageSize);
strSql.AppendFormat("{0} ", fieldList);
if (pageIndex == 1)
{
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} ", tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
}
else
{
if (!tableName.IsNullEmpty())
{
strSql.AppendFormat("from {0} where ({1} not in ( select top {2} {1} from {3} ", tableName, pk, pageSize * (pageIndex - 1), tableName);
}
if (!where.IsNullEmpty())
{
strSql.AppendFormat("where {0} ", where);
}
if (!groupBy.IsNullEmpty())
{
strSql.AppendFormat("group by {0} ", groupBy);
}
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
strSql.Append(")) ");
if (!orderBy.IsNullEmpty())
{
strSql.AppendFormat("order by {0} ", orderBy);
}
}
sql.DataSql = strSql.ToString();
return(sql);
}