select Camer as X ,count(*) as Y from PassengersVIEWGroup by Camer order by Y desc我需要显示出Group by求和后的前10条,和后面的其它数据的SUM(Y)
如:
X, Y
Group1,91
Group2,87
....
Group10,20
其它合计,112
如:
X, Y
Group1,91
Group2,87
....
Group10,20
其它合计,112
union
select [X]='其它合计',count(*) from PassengersVIEW where Camer not in(select distinct top 10 Camer from PassengersVIEW order by Camer desc)
@sql varchar(2000)as
CREATE TABLE [dbo].[#TEMP] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[x] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[y] [numeric](18, 0) NULL
) ON [PRIMARY]declare @sqlstr varchar(2000)set @sqlstr='insert into #TEMP(X,Y) select Camer as X ,count(*) as Y from PassengersVIEW where 1=1'+@sql+' Group by Camer order by Y desc '
exec(@sqlstr)select x,y from #TEMP where id<=10
union
select '其它' as x,SUM(y) AS Y FROM #TEMP where id>10
GO
@sql varchar(500)
as
declare @sqlstr varchar(2000)
set @sqlstr='select id=identity(int,1,1),Camer as X ,count(*) as Y into # from PassengersVIEW where 1=1'+@sql+' Group by Camer order by id'
exec(@sqlstr)
select x,y from # where id<=10
union
select '其它' as x,SUM(y) AS Y FROM # where id>10
直接select id=identity(int,1,1),Camer as X ,count(*) as Y into # from PassengersVIEW
Group by Camer order by Y desc -------------------------------------------
然后后面用
select x,y from #TEMP where id<=10
union
select '其它' as x,SUM(y) AS Y FROM #TEMP where id>10就行
from PassengersVIEW
Group by Camer order by Y descselect Camer as X,sum(*) as Y
from PassengersVIEW
Group by Camer order by y desc
where not exists(select top 10 from PassengersVIEW)
select sum(Y) from
(
select X,Y,rownum r from
(
select Camer X ,count(*) Y from PassengersVIEW
Group by Camer order by Y desc
)tab
where r>=10
)