使用Union联接两种表后再进行分页，多谢-MSSQL教程-爱易网页

使用Union联接两种表后再进行分页，多谢

日期：2014-05-18　浏览次数：20517 次

使用Union联接两种表后再进行分页，谢谢！
使用Union联接两种表后，这两种表都有ROW_NUMBER()对其进行了排序,查询出来结果如图,所以再想用ROW_NUMBER() 对这个查询出来的表进行排序分页.有木有方法啊！

SQL code


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

------解决方案--------------------

SQL code

;WITH  t AS (
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
)
SELECT *
FROM   t
WHERE  row_id BETWEEN 1 AND 10

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

使用Union联接两种表后再进行分页，多谢

相关资料更多>

推荐阅读更多>