none
使用Union联接两种表后再进行分页,谢谢! RRS feed

  • 问题

  • 使用Union联接两种表后,这两种表都有ROW_NUMBER()对其进行了排序,查询出来结果如图,所以再想用ROW_NUMBER() 对这个查询出来的表进行排序分页.有木有方法啊!
    SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY app.Processingtime DESC) AS pos, app.userid,app.Id AS aid,app.Type,app.Loanamount,app.LoanUse,app.Locationarea, app.Collateralname,app.LoanTitle,app.LoanContent,app.Addtime,app.Processingtime,app.IsIndex,app.LoanCity, app.CountHit,app.CountLeave,pro.ProvinceName,city.CityName,men.Id AS mid,men.TrueName,men.HeadInphoto,men.Headphoto FROM dbo.tb_Apply AS app INNER JOIN RegionalismProvinceCode pro ON app.Locationarea=pro.ProvinceID INNER JOIN RegionalismCityCode city ON city.CityID=app.LoanCity INNER JOIN dbo.tb_Menbermation AS men ON app.Userid=men.Userid UNION SELECT ROW_NUMBER() OVER (ORDER BY app.Processingtime DESC) AS pos, app.userid,app.Id AS aid,app.Type,app.Loanamount,app.LoanUse,app.Locationarea, app.Collateralname,app.LoanTitle,app.LoanContent,app.Addtime,app.Processingtime,app.IsIndex,app.LoanCity,app.CountHit,app.CountLeave, pro.ProvinceName,city.CityName,ent.Id AS eid,ent.TrueName,ent.HeadInphoto,ent.Headphoto FROM dbo.tb_Apply AS app INNER JOIN RegionalismProvinceCode pro ON app.Locationarea=pro.ProvinceID INNER JOIN RegionalismCityCode city ON city.CityID=app.LoanCity INNER JOIN dbo.tb_MenberEnterprise AS ent ON app.Userid=ent.Userid) AS sp
    2012年8月6日 3:40

答案

  • select row_number ...

    from (

    select ...

    union

    select ...

    ) as u

    顺便说一下,union会去重,一般是用union all


    想不想时已是想,不如不想都不想。

    2012年8月6日 4:06
    版主
  • select row_number ...

    from (

    select ...

    union

    select ...

    ) as u

    顺便说一下,union会去重,一般是用union all


    想不想时已是想,不如不想都不想。

    谢谢,弄出来个了!

    SELECT * FROM (
    SELECT ROW_NUMBER() OVER ( ORDER BY Processingtime DESC ) AS row_id, *
    FROM   (
          SELECT  app.userid, app.Id AS aid, app.Type, app.Loanamount, app.LoanUse, app.Locationarea, app.Collateralname, app.LoanTitle,
                  app.LoanContent, app.Addtime, app.Processingtime, app.IsIndex, app.LoanCity, app.CountHit, app.CountLeave, pro.ProvinceName,
                  city.CityName, men.Id AS mid, men.TrueName, men.HeadInphoto, men.Headphoto
          FROM    dbo.tb_Apply AS app
          INNER JOIN RegionalismProvinceCode pro
          ON      app.Locationarea = pro.ProvinceID
          INNER JOIN RegionalismCityCode city
          ON      city.CityID = app.LoanCity
          INNER JOIN dbo.tb_Menbermation AS men
          ON      app.Userid = men.Userid
          UNION
          SELECT  app.userid, app.Id AS aid, app.Type, app.Loanamount, app.LoanUse, app.Locationarea, app.Collateralname, app.LoanTitle,
                  app.LoanContent, app.Addtime, app.Processingtime, app.IsIndex, app.LoanCity, app.CountHit, app.CountLeave, pro.ProvinceName,
                  city.CityName, ent.Id AS eid, ent.TrueName, ent.HeadInphoto, ent.Headphoto
          FROM    dbo.tb_Apply AS app
          INNER JOIN RegionalismProvinceCode pro
          ON      app.Locationarea = pro.ProvinceID
          INNER JOIN RegionalismCityCode city
          ON      city.CityID = app.LoanCity
          INNER JOIN dbo.tb_MenberEnterprise AS ent
          ON      app.Userid = ent.Userid
        ) AS sp
    ) AS asp WHERE row_id BETWEEN 1 AND 10

    2012年8月6日 5:14

全部回复

  • select row_number ...

    from (

    select ...

    union

    select ...

    ) as u

    顺便说一下,union会去重,一般是用union all


    想不想时已是想,不如不想都不想。

    2012年8月6日 4:06
    版主
  • select row_number ...

    from (

    select ...

    union

    select ...

    ) as u

    顺便说一下,union会去重,一般是用union all


    想不想时已是想,不如不想都不想。

    谢谢,弄出来个了!

    SELECT * FROM (
    SELECT ROW_NUMBER() OVER ( ORDER BY Processingtime DESC ) AS row_id, *
    FROM   (
          SELECT  app.userid, app.Id AS aid, app.Type, app.Loanamount, app.LoanUse, app.Locationarea, app.Collateralname, app.LoanTitle,
                  app.LoanContent, app.Addtime, app.Processingtime, app.IsIndex, app.LoanCity, app.CountHit, app.CountLeave, pro.ProvinceName,
                  city.CityName, men.Id AS mid, men.TrueName, men.HeadInphoto, men.Headphoto
          FROM    dbo.tb_Apply AS app
          INNER JOIN RegionalismProvinceCode pro
          ON      app.Locationarea = pro.ProvinceID
          INNER JOIN RegionalismCityCode city
          ON      city.CityID = app.LoanCity
          INNER JOIN dbo.tb_Menbermation AS men
          ON      app.Userid = men.Userid
          UNION
          SELECT  app.userid, app.Id AS aid, app.Type, app.Loanamount, app.LoanUse, app.Locationarea, app.Collateralname, app.LoanTitle,
                  app.LoanContent, app.Addtime, app.Processingtime, app.IsIndex, app.LoanCity, app.CountHit, app.CountLeave, pro.ProvinceName,
                  city.CityName, ent.Id AS eid, ent.TrueName, ent.HeadInphoto, ent.Headphoto
          FROM    dbo.tb_Apply AS app
          INNER JOIN RegionalismProvinceCode pro
          ON      app.Locationarea = pro.ProvinceID
          INNER JOIN RegionalismCityCode city
          ON      city.CityID = app.LoanCity
          INNER JOIN dbo.tb_MenberEnterprise AS ent
          ON      app.Userid = ent.Userid
        ) AS sp
    ) AS asp WHERE row_id BETWEEN 1 AND 10

    2012年8月6日 5:14
  • 如果是SQL Server 2012可以用Order By 子句的offset和fetch next叙述来做分页。

    http://www.dotblogs.com.tw/terrychuang/archive/2012/06/25/73036.aspx


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/

    2012年8月6日 5:56
  • SQL2012真的强大

    给我写信: QQ我:点击这里给我发消息

    2012年8月6日 14:30
  • 分页是个郁闷的问题,如果不考虑性能的话,看起来SQL Server确实提供了强大的功能

    如果考虑性能的话,那就可以折腾死你

    2012年8月7日 2:44
  • 任何数据库都需要考虑性能的,不单只sqlserver

    给我写信: QQ我:点击这里给我发消息

    2012年8月7日 5:24
  • 先嵌套,再分页即可。
    2012年8月7日 8:39
    版主