有表如下:
id name counts
1 a 34
2 b 56
3 c 24
4 d 2
5 e 1
6 f 3
由于d,e,f 的数据比较少,想把他们合在一起,得到以下数据表:
name counts
a 34
b 56
c 24
other 6
应该怎样写这个语句?
id name counts
1 a 34
2 b 56
3 c 24
4 d 2
5 e 1
6 f 3
由于d,e,f 的数据比较少,想把他们合在一起,得到以下数据表:
name counts
a 34
b 56
c 24
other 6
应该怎样写这个语句?
from tablename
group by case when name not in ('a','b','c') then 'others' else name end
就算小於10吧select [name],counts from T where counts>=10
union
select 'other',sum(counts) from T where counts<10
union
select 'other',sum(counts) from T where name not in (select top 3 name from T order by counts desc)
現在才明白你前三的意思,開始以為你是指上面那個示例數據的前三條,沒想到是說表中的前三條。:)Create Table TEST
(id Int,
name Varchar(10),
counts Int)
Insert TEST Select 1, 'a', 34
Union All Select 2, 'b', 56
Union All Select 3, 'c', 24
Union All Select 4, 'd', 2
Union All Select 5, 'e', 1
Union All Select 6, 'f', 3
GO
Select
name,
SUM(counts) As counts
From
(Select
(Case When counts In (Select TOP 3 counts From TEST Order By counts Desc) Then name Else 'Other' End) As name,
counts
From TEST) A
Group By name
GO
Drop Table TEST
--Result
/*
id counts
a 34
b 56
c 24
Other 6
*/
from tab where counts > 10
union
select other as name , sum(counts)
from tab where counts < 10
from tab where counts in (select top 3 counts from tab order by counts asc )
union
select other as name , sum(counts)
from tab where counts not in (select top 3 counts from tab order by counts asc )