sql server 查询多个不关联表且对结果编号

1.除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

解决方法：top 100 percent *

2.如何对查询结果编号？

解决方法：row_number() over (order by billdate desc) as rowid

3.如何查询多个不关联表？且各表的字段不一样怎么办？

解决方法：使用union all，具体方法如下面代码

示例代码：（执行成功，但代码有多余，为了提供实例说明）

select top 50 a.* from (

select top 100 percent *,row_number() over (order by billdate desc,a.lx asc,a.id desc) as rowid from
(Select tablename='wenzhang',title,id,BILLDATE,lx,zqs=0,qs=0 From wenzhang 
union all 
Select tablename='jchd',title,id,BILLDATE,lx=7,zqs=0,qs=0  From jchd 
union all
Select tablename='jianbao',title,id,BILLDATE,lx=8,ZQS,QS From jianbao ) as a
where 1=1 
order by billdate desc,a.lx asc,a.id desc
) a where a.rowid not in (select top 25 a.rowid from (

select top 100 percent *,row_number() over (order by billdate desc,a.lx asc,a.id desc) as rowid from
(Select tablename='wenzhang',title,id,BILLDATE,lx,zqs=0,qs=0 From wenzhang 
union all 
Select tablename='jchd',title,id,BILLDATE,lx=7,zqs=0,qs=0  From jchd 
union all
Select tablename='jianbao',title,id,BILLDATE,lx=8,ZQS,QS From jianbao ) as a
where 1=1 
order by billdate desc,a.lx asc,a.id desc
) 
a order by a.billdate desc,a.lx asc,a.id desc) order by a.billdate desc,a.lx asc,a.id desc

简化后代码：（执行成功）

select top 50 a.* from (

select *,row_number() over (order by billdate desc,a.lx asc,a.id desc) as rowid from
(Select tablename='wenzhang',title,id,BILLDATE,lx,zqs=0,qs=0 From wenzhang 
union all 
Select tablename='jchd',title,id,BILLDATE,lx=7,zqs=0,qs=0  From jchd 
union all
Select tablename='jianbao',title,id,BILLDATE,lx=8,ZQS,QS From jianbao ) as a
where 1=1 

) a where a.rowid not in (select top 25 a.rowid from (

select *,row_number() over (order by billdate desc,a.lx asc,a.id desc) as rowid from
(Select tablename='wenzhang',title,id,BILLDATE,lx,zqs=0,qs=0 From wenzhang 
union all 
Select tablename='jchd',title,id,BILLDATE,lx=7,zqs=0,qs=0  From jchd 
union all
Select tablename='jianbao',title,id,BILLDATE,lx=8,ZQS,QS From jianbao ) as a
where 1=1 

) 
a order by a.billdate desc,a.lx asc,a.id desc) order by a.billdate desc,a.lx asc,a.id desc

 

转载于:https://www.cnblogs.com/ding2011/p/3270212.html