+------------+------+-----------+---------------------+---------------------+
| m_id | s_id | soft_name | soft_uninstall_path | time |
+------------+------+-----------+---------------------+---------------------+
| 1234567892 | 1 | test.ext | aaa | 2007-08-09 00:00:00 |
| 1234567892 | 1 | test.exe | aabn | 2007-08-10 00:00:00 |
| 1234567892 | 1 | test.exe | aabn | 2007-08-10 00:00:00 |
| 1234567893 | 1 | test.exe | aabn | 2007-08-10 00:00:00 |
| 1234567893 | 1 | test.exe | aabn | 2007-08-10 00:00:00 |
| 1234567890 | 1 | test1.exe | aabn | 2007-08-10 00:00:00 |
| 1234567891 | 1 | test1.exe | aabn | 2007-08-10 00:00:00 |
| 1234567893 | 1 | ttt.exe | dasf | 2008-02-01 00:00:00 |
| 1234567892 | 1 | dee | eee | 2008-03-01 00:00:00 |
+------------+------+-----------+---------------------+---------------------+
想统计该表中soft_name在不同m_id上安装的个数的top 3,怎样写?
| m_id | s_id | soft_name | soft_uninstall_path | time |
+------------+------+-----------+---------------------+---------------------+
| 1234567892 | 1 | test.ext | aaa | 2007-08-09 00:00:00 |
| 1234567892 | 1 | test.exe | aabn | 2007-08-10 00:00:00 |
| 1234567892 | 1 | test.exe | aabn | 2007-08-10 00:00:00 |
| 1234567893 | 1 | test.exe | aabn | 2007-08-10 00:00:00 |
| 1234567893 | 1 | test.exe | aabn | 2007-08-10 00:00:00 |
| 1234567890 | 1 | test1.exe | aabn | 2007-08-10 00:00:00 |
| 1234567891 | 1 | test1.exe | aabn | 2007-08-10 00:00:00 |
| 1234567893 | 1 | ttt.exe | dasf | 2008-02-01 00:00:00 |
| 1234567892 | 1 | dee | eee | 2008-03-01 00:00:00 |
+------------+------+-----------+---------------------+---------------------+
想统计该表中soft_name在不同m_id上安装的个数的top 3,怎样写?
解决方案 »
- 为何[存档终点]是USE_DB_RECOVERY_FILE_DEST,而不是目录?
- 请问游标取数据的顺序?
- 无法初始化ocr
- 请问PL/SQL两个不同服务器的数据库的所有procedures怎么同步?
- 请问如何导入导出一个存储过程,我用的是sqlplus,请问用什么命令?
- 关于sqlloader的问题,为什么如果某个字段的值有空的整条记录就导不进去
- 请教:为了提高i/o,为什么将表分区分配到不同磁盘驱动器?谢谢!!
- 加急电报(巨难)!
- 怎么访问oracle?
- 生成脚本往mysql导入数据报错
- 碰到郁闷问题,装有oracle9,10,11的兄弟姐妹们,帮忙测测啊
- 求助达人,redhat 9 下oracle91,如何创建数据库实例(50分
(select t.soft_name,m_id,count(*) c from ttt
group by t.soft_name,m_id
order by c desc)
where c<=3
SELECT SOFT_NAME,CNUM FROM (
SELECT soft_name,COUNT(DISTINCT MID) CNUM FROM TBNAME GROUP BY SOFT_NAME
ORDER BY COUNT(DISTINCT MID) DESC
) WHERE ROWNUM<4;
SELECT M_ID, SOFT_NAME, TOTAL
FROM (
SELECT M_ID,SOFT_NAME, TOTAL,
ROW_NUMBER() OVER (PARTITION BY M_ID, SOFT_NAME ORDER BY TOTAL DESC) AS SEQ
FROM (
SELECT M_ID,SOFT_NAME, COUNT(SOFT_NAME) AS TOTAL
FROM soft_name
GROUP BY M_ID,SOFT_NAME
)
)
test.exe 2
test1.exe 2
test.ext 1
ttt.exe 1
dee 1test.exe虽然出现4次,但只属于两个不同m_id,所以是2,test1.exe出现两次,又分属两个不同m_id,所以也
是2,以此类推
在线等,得到结果马上给分
谢谢
where rownum < 4
select b.soft_name,count(*) as cnt from (select distinct m_id,soft_name from mss_software) b group by b.soft_name order by cnt desc 谢谢大家