表名: USERTABLE,字段NAME(名称),MODEL(型号),TYPE(类型),CNT(用户数)
表数据大概如下:
NAME MODEL TYPE CNT
name1 model1 A 1000
name2 model2 B 2000
name3 model3 C 3000
name1 model1 B 4000
name1 model1 B 4000
name1 model1 C 8000想统计出哪种名称和型号的用户数排名前十的数据,结果如:
NAME MODEL TYPEA(类型为A的用户数) TYPEB(类型为B的用户数) TYPEC(类型为C的用户数) 总数有无办法一次性写出来,高手指教
表数据大概如下:
NAME MODEL TYPE CNT
name1 model1 A 1000
name2 model2 B 2000
name3 model3 C 3000
name1 model1 B 4000
name1 model1 B 4000
name1 model1 C 8000想统计出哪种名称和型号的用户数排名前十的数据,结果如:
NAME MODEL TYPEA(类型为A的用户数) TYPEB(类型为B的用户数) TYPEC(类型为C的用户数) 总数有无办法一次性写出来,高手指教
解决方案 »
- B/S模式java客户端打包
- if,else为啥if执行单个语句也要后面加大括号
- struts2 页面list传给action的问题
- 求助:如何在resin下使用jndi配置proxool的连接池啊
- Java性能问题(菜鸟送分来了)
- fileupload上传问题
- 如何获取访客的浏览轨迹(本姑娘万分感谢)
- 如何在JSP中不通过ODBC直接连MS SQL Server
- 很奇怪的JSP关于数据库的问题?(谢谢我很着急)
- 最近JSP中大家讨论聊天室的建立方法、中文解决方案的较多,不如大家来作个综合讨论
- 实现一个下载链接,这个链接必须可以用flashget进行多线程下载,而且下载的东西是经过修改的源文件(只能存在内存中,不能真正更改硬盘中的源文件)
- js冲突的问题
select t.name,t.model,
(select sum(cnt) from usertable where name = t.name and type = 'A') A,
(select sum(cnt) from usertable where name = t.name and type = 'B') B,
(select sum(cnt) from usertable where name = t.name and type = 'C') C
from usertable t
group by t.name,t.model 如果你的类型只有A、B、C那么这样木有问题!!!
select * from (select t.name,t.model,
(select sum(cnt) from usertable where name = t.name and model=t.model and type = 'A') A,
(select sum(cnt) from usertable where name = t.name and model=t.model and type = 'B') B,
(select sum(cnt) from usertable where name = t.name and model=t.model and type = 'C') C,
sum(t.cnt)sums
from usertable t
group by t.name,t.model)t where rownum <= 10 order by t.sums desc;如果你的数据库是Oracle这样应该OK了!!!
(
select sum (cnt) from usertable t2 where t.name=t2.name and t2.type='A'
) typeA ,
(
select sum (cnt) from usertable t2 where t.name=t2.name and t2.type='B'
) typeB,
(
select sum (cnt) from usertable t2 where t.name=t2.name and t2.type='C'
) typeC ,
(
select sum(cnt) from usertable t2 where t.name=t2.name
) sums
from usertable t group by name,model order by sums desc
(select sum(USER_CNT) from AT_TERM_USER where TERM_FACTORY = t.TERM_FACTORY and USER_BRAND = 'A') A,
(select sum(USER_CNT) from AT_TERM_USER where TERM_FACTORY = t.TERM_FACTORY and USER_BRAND = 'B') B,
(select sum(USER_CNT) from AT_TERM_USER where TERM_FACTORY = t.TERM_FACTORY and USER_BRAND = 'C') C,
(select sum(USER_CNT) from AT_TERM_USER where TERM_FACTORY = t.TERM_FACTORY and USER_BRAND = 'D') D,
(select sum(USER_CNT) from AT_TERM_USER where TERM_FACTORY = t.TERM_FACTORY and USER_BRAND = 'Z') Z,
(select sum(USER_CNT) from AT_TERM_USER where TERM_FACTORY = t.TERM_FACTORY and USER_BRAND = 'Y') Y
from AT_TERM_USER t
group by t.TERM_FACTORY,t.term_model order by COUNT desc) where rownum <=10
但我发现COUNT值和A,B,C,D,Z,Y的和不相等
(
select sum (cnt) from usertable t2 where t.name=t2.name and t2.type='A'
) typeA ,
(
select sum (cnt) from usertable t2 where t.name=t2.name and t2.type='B'
) typeB,
(
select sum (cnt) from usertable t2 where t.name=t2.name and t2.type='C'
) typeC ,
(
select sum(cnt) from usertable t2 where t.name=t2.name
) sums
from usertable where rownum <10 t group by name,model order by sums desc
这个是oracle的写法,其他数据库,就是去前十位的方法rownum <10 这个地方不同,其他地方应该没有问题,楼主可以试试,是否有效
你的型号and model=t.model 这个加上木有???
要把这个条件加上啊!
中 sum(user_cnt) as count 这总量不能这么计算
(select sum(USER_CNT) from AT_TERM_USER where TERM_FACTORY = t.TERM_FACTORY ) as count这样计算出的才是真正的总数,楼主可以单独就算总数,看看,应该就是这么写