MySql高效分页SQL

 1         public string GetQuerySql(ITSPAreaQueryModel model, object state = null)
 2         {
 3             var sqlBuilder = new StringBuilder(850);
 4             sqlBuilder.AppendFormat(@"{0}
 5 /*---Script For Select Top N Rows---*/
 6 SELECT  SQL_CALC_FOUND_ROWS
 7         a.Id,
 8         a.AreaName ,
 9         a.Location ,
10         a.AreaHead ,
11         a.Remark ,
12         a.RowState,
13         a.CreateOn,
14         a.CreateBy,
15         a.UpdateOn,
16         a.UpdateBy
17 FROM ITSPArea as a", _SqlComment);
18             if (model.RowState == null)
19             {
20                 sqlBuilder.AppendFormat("{0}WHERE a.RowState IN ( 0,1 )", Environment.NewLine);
21             }
22             else
23             {
24                 sqlBuilder.AppendFormat("{0}WHERE a.RowState={1}", Environment.NewLine, model.RowState);
25             }
26             if (!string.IsNullOrEmpty(model.AreaName))
27             {
28                 sqlBuilder.AppendFormat("{0}and a.AreaName=@AreaName", Environment.NewLine);
29             }
30             if (!string.IsNullOrEmpty(model.AreaHead))
31             {
32                 sqlBuilder.AppendFormat("{0}and a.AreaHead=@AreaHead", Environment.NewLine);
33             }
34             sqlBuilder.AppendFormat("{0}order by a.CreateOn desc", Environment.NewLine);
35             sqlBuilder.AppendFormat("{0}LIMIT {1},{2};", Environment.NewLine, (model.PageIndex - 1) * model.PageSize, model.PageSize);
36             sqlBuilder.AppendFormat("{0}SELECT FOUND_ROWS()", Environment.NewLine);
37             return sqlBuilder.ToString();
38         }