select count(*) as ge,VirusName='其他病毒' from Table_Virus where VirusName not in(select top 5 VirusName from Table_Virus group by VirusName order by count(1) desc)
union
select top 5 count(VirusName) as ge , VirusName from Table_Virus group by VirusName order by ge desc 在我们的项目中遇到一个问题
这两条select 单独运行都好用 可是用union 结合就错了 (如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。)不胜感激!!
union
select top 5 count(VirusName) as ge , VirusName from Table_Virus group by VirusName order by ge desc 在我们的项目中遇到一个问题
这两条select 单独运行都好用 可是用union 结合就错了 (如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。)不胜感激!!
select count(*) as ge,VirusName='其他病毒' from Table_Virus where VirusName not in(select top 5 VirusName from Table_Virus group by VirusName order by ge desc)
union
select top 5 count(VirusName) as ge , VirusName from Table_Virus group by VirusName order by ge desc
into #temp
from Table_Virus where VirusName not in(select top 5 VirusName from Table_Virus group by VirusName order by count(1) desc) insert into #temp
select top 5 count(VirusName) as ge , VirusName from Table_Virus group by VirusName order by ge desc
select *from #tempdorp table temp
from Table_Virus a
right join (select top 5 VirusName,count(1) as r
from Table_Virus
group by VirusName
order by count(1) desc) b
on a.VirusName <> b.VirusName
union
select top 5 count(VirusName) as ge , VirusName
from Table_Virus
group by VirusName
order by ge desc
------------------
什麼樣的錯誤信息?
select top 5 count(VirusName) as ge , VirusName
from Table_Virus
group by VirusName
order by ge desc
) tmp
UNION
select count(*)-5 as ge ,VirusName='其他病毒'
from Table_Virus
-->>>
这句可以简化成
select count(*)-5 as ge ,VirusName='其他病毒'
from Table_Virus
楼上用临时表的方法正解,可以支持大数据。
create table Table_Virus(virusname varchar(10))
insert into Table_Virus select 'a'
insert into Table_Virus select 'b'
insert into Table_Virus select 'b'
insert into Table_Virus select 'g'
insert into Table_Virus select 'd'
insert into Table_Virus select 'd'
insert into Table_Virus select 'e'
insert into Table_Virus select 'e'
insert into Table_Virus select 'f'
insert into Table_Virus select 'f'
insert into Table_Virus select 'f'
insert into Table_Virus select 'n'
insert into Table_Virus select 'db'
insert into Table_Virus select 'cb'
goselect count(1) as ge,VirusName='其他病毒'
from Table_Virus b
where not exists(select 1 from
(select top 5 VirusName ,count(1) as r
from Table_Virus
group by VirusName
order by count(1) desc) a where a.VirusName = b.VirusName)union
select top 5 count(VirusName) as ge , VirusName
from Table_Virus
group by VirusName
order by ge desc
drop table Table_Virusset nocount off/*
ge VirusName
----------- ----------
4 其他病毒
2 b
2 d
1 a
1 cb
1 db*/
如果VirusName的 总个数小 5 ?
实在不行可以把union后面的改一下:...
union
select * from (select top 5 count(VirusName) as ge , VirusName from Table_Virus group by VirusName order by ge desc) t
union
select * from (
select top 5 count(VirusName) as ge , VirusName from Table_Virus group by VirusName order by ge desc
) as t
的确有问题,修正如下:set nocount on
create table Table_Virus(virusname varchar(10))
insert into Table_Virus select 'a'
insert into Table_Virus select 'b'
insert into Table_Virus select 'b'
insert into Table_Virus select 'g'
insert into Table_Virus select 'd'
insert into Table_Virus select 'd'
insert into Table_Virus select 'e'
insert into Table_Virus select 'e'
insert into Table_Virus select 'f'
insert into Table_Virus select 'f'
insert into Table_Virus select 'f'
insert into Table_Virus select 'n'
insert into Table_Virus select 'db'
insert into Table_Virus select 'cb'
goselect count(*) as ge,VirusName='其他病毒' from Table_Virus where VirusName not in(select top 5 VirusName from Table_Virus group by VirusName order by count(1) desc) select top 5 count(VirusName) as ge , VirusName from Table_Virus group by VirusName order by ge desc select count(1) as ge,VirusName='其他病毒'
from Table_Virus b
where not exists(select 1 from
(select top 5 VirusName ,count(1) as r
from Table_Virus
group by VirusName
order by count(1) desc) a where a.VirusName = b.VirusName)union all
select * from (select top 5 count(VirusName) as ge , VirusName
from Table_Virus
group by VirusName
order by ge desc ) b
order by ge descdrop table Table_Virusset nocount off
/*
ge VirusName
----------- ---------
4 其他病毒ge VirusName
----------- ----------
3 f
2 b
2 d
2 e
1 dbge VirusName
----------- ----------
4 其他病毒
3 f
2 b
2 d
2 e
1 db*/