样表如下
id f1
1 a
2 b
3 c
4 a
5 b
6 a
7 b
8 d
9 aSELECT f1, count(*) AS num GROUP BY f1 ORDER BY num DESC
f1 num
a 4
b 3
c 1
d 1现在要显示为
f1 num
a 4
b 3
other 2该怎么弄?
id f1
1 a
2 b
3 c
4 a
5 b
6 a
7 b
8 d
9 aSELECT f1, count(*) AS num GROUP BY f1 ORDER BY num DESC
f1 num
a 4
b 3
c 1
d 1现在要显示为
f1 num
a 4
b 3
other 2该怎么弄?
解决方案 »
- 怎样把MySQL数据库搬到msSQL?连结构带数据
- varchar_ignorecase和varchar的区别
- A表记录的多个字段和B表的多记录如何实现关联
- MYSQl多表查询
- foreign key 问题
- 如何对时间型字段进行求和?
- mysql5.0.0--alpha的一个问题。。。。
- 新手安装Mysql的问题!! 请大家多帮忙!!
- 高分求助:有3中以frm,myd,myi后缀的数据库文件各是什么意思,我怎样将他们挂在MYSQL数据库上
- 高手帮我解释下这sql --。。。。。-
- 各位大哥请帮帮忙!一个关于MYSQL数据库方面的问题,我是新手,请帮忙!谢谢
- 如何用一句MYSQL语句完成以下任务?
from (
select case when count(*)=1 then 'other' else f1 end as f1, count(*) as num
from 表名
group by f1 ) T
group by f1
order by num desc
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'a' union all
select 5,'b' union all
select 6,'a' union all
select 7,'b' union all
select 8,'d' union all
select 9,'a' select f1,sum(num) as num
from (
select case when count(*)=1 then 'other' else f1 end as f1, count(*) as num
from 表名
group by f1 ) T
group by f1
order by num descdrop table 表名
from (
select case when count(*)=1 then 'other' else f1 end as f1, count(*) as num
from 表名
group by f1 ) T
group by f1
order by num desc
楼上高手呀,又学到新东西了.
不过可能没说清楚,不是因为c,d的count为1才丢到other里的是我希望取前两项,然后把剩下的丢进other里应该用LIMIT搞定吧期待更好的答案,我自己也想想吧
from 表名
group by f1另,执行时无法用f1来GROUP BY
因为 'other'不属于f1
select (case when count(*)=1 then 'other' else f1 end) as f1,count(*) as num
from group_test
group by f1) T group by f1
order by num desc;
见楼上回复,此问题我已重复说明了
你的方法和wangtiecheng是一样的,都不行
SELECT distinct(case when f1='a' then 'a' when f1='b' then 'b' when f1!='a' or f1!='b' then 'other' end ) as 'other', (case when f1='a' or f1='b' then count(*) when f1!='a' or f1!='b' then (select count(*) from table1 where f1!='a' and f1!='b') end ) as num from table1 GROUP BY 'other' ORDER BY num DESC;
有问题,因为不是因为f1等于a或b才把他们独立,而把cd归为一组的
To yifuzhiming
那两条SQL的作用是什么?
select distinct(case when f1='c' or f1='d' then 'other' else f1 end ) as 'f1' ,(case when f1='c' or f1='d' then (select count(*) from table1 where f1='c' and f1='d') else count(*) end ) as num from table1 GROUP BY 'f1' ORDER BY num DESC;
你再试试.
select distinct(case when f1='c' or f1='d' then 'other' else f1 end ) as 'f1' ,(case when f1='c' or f1='d' then (select count(*) from table1 where f1='c' or f1='d') else count(*) end ) as num from table1 GROUP BY 'f1' ORDER BY num DESC;
select f1, sum(num) as num from
(select case when f1 not in (select top 2 f1
from 表名
group by f1
order by count(*) desc) then 'other' else f1 end as f1,count(*) as num
from 表名
group by f1) t
group by f1
order by num desc
from other as a left join
(
select f1,count(*) as num
from other
group by f1
order by num desc
limit 0,2
)as b
on(a.f1=b.f1)
group by f1
`id` int(11) NOT NULL auto_increment,
`f1` char(1) NOT NULL default 'a',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
=================select f1,sum(num) as num from (
select (case when count(*)=1 then 'other' else f1 end) as f1,count(*) as num
from group_test
group by f1) T group by f1
order by num desc;==========================
query result(3 records)
f1 num
a 4
b 3
other 2
怎么就不一样了。
create table #a(id int,f1 varchar(10))insert into #a
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'a' union all
select 5,'b' union all
select 6,'a' union all
select 7,'b' union all
select 8,'d' union all
select 9,'a' SELECT f1, count(*) AS num from #a
GROUP BY f1
having count(*) <> 1
union all
SELECT '其他' , sum(num) from
(
SELECT f1, count(*) AS num from #a
GROUP BY f1
having count(*) = 1
)a
你的那个改后的语句本身有些问题,这样写更简化些吧:
select distinct(case when f1='c' or f1='d' then 'other' else f1 end ) as 'f1' , count(*) as num from table1 GROUP BY 'f1' ORDER BY num DESC;
可能是数据库版本问题吧!我的MySQL 4.3版,MyISAM数据库,不是innoDB。
而且我说了,不是按COUNT排序的,实际上是取前2个,其余的归为一类,所以不符合要求TO abcd_(玉莲)
按你的做法不可行,因为还有cdefghijk........................
不知道为什么,几乎所有人都死揪count=1这个。。
我给的只是样表,顶楼没说清楚,不过后面补充过了,貌似看了的人不多TO Debug_mq() ydage(Ljr)
这两个方案我试着改改吧
谢谢