SQL分页、多表联合、多表联合分页

1.SQL分页

with Row as
(
select jyid=ROW_NUMBER() over(order by OverTime),*from TYcOverLogs 
) 
select top 2 * from Row where jyid not in (select top 0 jyid from Row order by jyid)order by jyid

其中，jyid是一个虚拟的int变量，目的是为了给表添加一个虚拟的int编号，方便分页；而 OverTime则是分页依据的字段，因为有的时候，有的人的建表习惯未必会添加一个int型的主键作为标序。

以上语句是一个标准的分页语句，第一个top后面的数字为pagesize，第二个top后面的数据是第几页开始的序号，可以直接套用，如下：

 #region 获取分页内容
        /// <summary>
        /// 获取分页内容
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static List<TRgzwLogs> GetPageContent(int PageIndex, int PageSize)
        {

            List<TRgzwLogs> rgzwlogs = new List<TRgzwLogs>();
            try
            {
                StringBuilder strSql = new StringBuilder();
                string tabName, PKfld;
                tabName = "TRgzwLogs";//主表名
                PKfld = "CreateTime";//排序参照字段

                strSql.Append("with Row as ");
                strSql.Append(" ( ");
                strSql.Append(" select jyid=ROW_NUMBER() over(order by " + PKfld + " desc ),*from " + tabName);
                strSql.Append(" ) ");
                strSql.Append(" select top " + PageSize.ToString() + " * from Row ");
                strSql.Append(" where jyid not in (select top " + (PageSize * (PageIndex - 1)).ToString() + " jyid from Row order by jyid)order by jyid ");

                SqlParameter[] cmdParams = { };
                DataSet dsResult = SQLHelper.ExecuteDataset(GlobalData.DBConnStr, CommandType.Text, strSql.ToString(), cmdParams);
                foreach (DataRow dataRow in dsResult.Tables[0].Rows)
                {
                    rgzwlogs.Add(new TRgzwLogs(dataRow));
                }


            }
            catch (Exception ex)
            {
                string error = ex.Message;
            }

            return rgzwlogs;
        }
        #endregion

2.多表联合

举例两张表做测试：

表1：student  截图如下：

  

表2：course  截图如下：

一、外连接（左连接、右连接、完全外连接）

 

a.左连接  左表为主 行数为左表行数  left join 或者 left outer join

select * from student left join course on student.ID=course.ID

执行结果：

左外连接包含left join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL).

注：此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。

 

b.右连接 右表为主，行数为右表行数 right join 或 right outer join

select * from student right join course on student.ID=course.ID

执行结果：

 

右外连接包含right join右表所有行，如果左表中某行在右表没有匹配，则结果中对应左表的部分全部为空(NULL)。

注：同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。

 

c.完全外连接 行数为左右表行数的集合 full join 或 full outer join

select * from student full join course on student.ID=course.ID

执行结果：

完全外连接包含full join左右两表中所有的行，如果右表中某行在左表中没有匹配，则结果中对应行右表的部分全部为空(NULL)，如果左表中某行在右表中没有匹配，则结果中对应行左表的部分全部为空(NULL)。

 

二、内连接

a.内连接  join 或 inner join

select * from student inner join course on student.ID=course.ID

 

执行结果：

inner join 是比较运算符，只返回符合条件的行。

此时相当于：

select * from student,course where student.ID=course.ID

 

三、交叉连接

a.交叉连接 cross join

1.概念：没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

select * from student cross join course

 

执行结果：

如果我们在此时给这条SQL加上WHERE子句的时候比如SQL:

select * from student cross join course where student.ID=course.ID

此时将返回符合条件的结果集，结果和inner join所示执行结果一样。

 

四、两表关系为一对多，多对一或多对多时的连接语句

当然上面两表为一对一关系，那么如果表A和表B为一对多、多对一或多对多的时候，我们又该如何写连接SQL语句呢？

其实两表一对多的SQL语句和一对一的SQL语句的写法都差不多，只是查询的结果不一样，当然两表也要略有改动。

 

比如表1的列可以改为：

 

Sno Name Cno

 

表2的列可以改为：

 

Cno CName

这样两表就可以写一对多和多对一的SQL语句了，写法和上面的一对一SQL语句一样。

 

下面介绍一下当两表为多对多的时候我们该如何建表以及些SQL语句。

新建三表：

表A:  student 截图如下：

表B:  course 截图如下：

表C:  student_course 截图如下：

一个学生可以选择多门课程，一门课程可以被多个学生选择，因此学生表student和课程表course之间是多对多的关系。

当两表为多对多关系的时候，我们需要建立一个中间表student_course，中间表至少要有两表的主键，当然还可以有别的内容。

select s.Name,C.Cname from student_course as sc left join student as s on s.Sno=sc.Sno left join course as c on c.Cno=sc.Cno

执行结果：

此条SQL执行的结果是学生选课的情况。

 

3.多表联合+分页

#region 获取分页内容
        /// <summary>
        /// 获取分页内容
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static List<Tycoverlogs> GetPageContent(int PageIndex, int PageSize)
        { 
        
            List<Tycoverlogs> ycoverlogs=new List<Tycoverlogs>();
            try
            {
                StringBuilder strSql = new StringBuilder();
                string tabName, PKfld;
                tabName = "TYcOverLogs";
                PKfld = "OverTime";

                strSql.Append("with Row as ");//分页
                strSql.Append(" ( ");
                strSql.Append(" select jyid=ROW_NUMBER() over(order by " + PKfld + " desc ),*from " + tabName);
                strSql.Append(" ) ");
                strSql.Append(" select top " + PageSize.ToString() + " * from Row ");
                strSql.Append(" left join TYcYt on Row.YcId=TYcYt.Id  ");//多表左连接
                strSql.Append(" where jyid not in (select top " + (PageSize * (PageIndex - 1)).ToString() + " jyid from Row order by jyid)order by jyid ");              
               
             
                SqlParameter[] cmdParams = { };
                DataSet dsResult = SQLHelper.ExecuteDataset(GlobalData.DBConnStr, CommandType.Text, strSql.ToString(), cmdParams);
                foreach (DataRow dataRow in dsResult.Tables[0].Rows)
                {
                    Tycoverlogs ycoverlogs1 = new Tycoverlogs(dataRow);
                    if (dataRow["Id1"] != DBNull.Value)
                    {
                        ycoverlogs1.YcYt = new TYcYt(dataRow);
                        ycoverlogs1.YcYt.Id = DataRowHelper.GetValue<int>(dataRow, "Id1");

                    }
                    ycoverlogs.Add(ycoverlogs1);                    
                }


            }
            catch (Exception ex)
            {
                string error = ex.Message;
            }

            return ycoverlogs;
        }
        #endregion

 ---------------------------------------------------------------------------------------------------------姜彦 20170601