数据库进行SQL分组查询后再过滤

Question

数据库表中有以下几个字段：
city_id
gathertime
ws_total
ws_succ
ws_other
para ,
ws_stat
type

先要根据city_id进行分组，分组完了之后根据type类型的不同，分别统计出type类型相同的sum(ws_succ)*100.00/sum(ws_stat)和sum(ws_stat) 的值。
之前自己写了 select (sum(ws_succ)*100.00/sum(ws_stat)) as yvalue,city_id as xvalue,sum(ws_stat) as ws_total from tab_open_success_ratio where ws_stat>0 group by city_id，type
明显不大对，也不知道怎么搞定
有大侠援助么

Answer 1

你的表述我没看得太清， “根据type类型的不同” 统计是否忽略了city_id
最好 举个例子说明一下期望的算法

我根据你的表述，猜测你这里包含两层分组业务，
第一，根据city_id进行分组， 后用分组结果进行比较再次统计
如果你是10g或以上，可以试试以下语句
with grp1 as ( select sum(ws_succ)ssucc, sum(ws_stat) sstat ,city_id ,type
from tab_open_success_ratio
where ws_stat>0
group by city_id ,type)
select sum(ssucc)*100.00/sum(sstat),sum(sstat),type
from grp1
group by type

Answer 2

这句sql除city_id后的逗号要用ascii的逗号外其它的没问题，它的意思就是按city_id与type组，然后每组中sum(ws_succ)*100.00/sum(ws_stat)和sum(ws_stat) 的值，这个含义不对？

Answer 3

row_number() over() 这个函数我用的不多，不过我相信它应该可以解决你遇到的问题，至于效率嘛，我就不保证了，呵呵……

SELECT T.S_ws_succ*100.00/T.S_ws_stat as yvalue,T.city_id as xvalue,T.S_ws_stat as ws_total
(SELECT ROW_NUMBER() OVER(PARTITION BY city_id ORDER BY type) AS R_city_id,city_id,
ROW_NUMBER() OVER(PARTITION BY city_id，type ORDER BY ws_stat) AS R_type,type,
SUM(ws_stat) OVER(PARTITION BY city_id，type) AS S_ws_stat,ws_stat,
SUM(ws_succ) OVER(PARTITION BY city_id，type) AS S_ws_succ,ws_succ
from tab_open_success_ratio
where ws_stat>0) T
WHERE T.R_type=1

有问题欢迎提问，满意请采纳！

Answer 4

select city_id,type,(sum(ws_succ)*100.00/sum(ws_stat)) as yvalue,sum(ws_stat) as ws_total
from tab_open_success_ratio where ws_stat>0 group by city_id,type 把无关的字段从select中剔除，不然无法group 求和