zhang@zhang>select * from wdtbl;ID STATUS ---------- -------------------- 3 1 3 1 4 1 4 1 4 1 4 1 4 1 5 1 5 1 5 1 6 2ID STATUS ---------- -------------------- 6 2 6 213 rows selected.zhang@zhang>select id, count(id)/(select count(*) from wdtbl where status=1) 2 from wdtbl 3 where status=1 4 group by id;ID COUNT(ID)/(SELECTCOUNT(*)FROMWDTBLWHERESTATUS=1) ---------- ------------------------------------------------ 3 .2 4 .5 5 .3zhang@zhang>select id from wdtbl 2 where status=1 3 group by id 4 having count(id)/(select count(*) from wdtbl where status=1)>0.4;ID ---------- 4
zhang@zhang>select t1.id from 2 (select id, count(id) id1 from wdtbl where status=1 group by id) t1, 3 (select id, count(id) id2 from wdtbl group by id) t2 4 where t1.id=t2.id and id1/id2>0.4;ID ---------- 3效率不会太高
假设原表名:old_tablecreate table a_table as select id,status,count(status) as a_count from old_table group by id,status order by id;create table b_table as select id,count(status) as b_count from old_table group by id order by id;select * from a_table a,b_table b where a.id=b.id and a.status=1 and a.count/b.count>0.4;附:才学SQL一个星期,连基础都还有点没学完~``,只能写出这么长的代码了。不好意思~`` 再就是不知道是我理解错了还是什么,题意应该是个多对多的关系吧,不然就做不了了~`` 就是:一个ID可以对应多个status的值,一个status也可以对应多个ID吧~``
希望大家帮我哈~``
谢谢捧场的两位兄弟,问题已解决! 结贴 改为如下速度还可以 select id from wdtbl group by id having (sum(decode(status,1,1,0))/count(*))>0.4;
where status=1
group by id
having count(id)/select count(*) from wdtbl where status=1>0.4
你那个语句是不是还有问题啊,
ERROR 位于第 4 行:
ORA-00936: 缺少表达式
select id from wdtbl
where status=1
group by id
having count(id)/(select count(*) from wdtbl where status=1)>0.4
---------- --------------------
3 1
3 1
4 1
4 1
4 1
4 1
4 1
5 1
5 1
5 1
6 2ID STATUS
---------- --------------------
6 2
6 213 rows selected.zhang@zhang>select id, count(id)/(select count(*) from wdtbl where status=1)
2 from wdtbl
3 where status=1
4 group by id;ID COUNT(ID)/(SELECTCOUNT(*)FROMWDTBLWHERESTATUS=1)
---------- ------------------------------------------------
3 .2
4 .5
5 .3zhang@zhang>select id from wdtbl
2 where status=1
3 group by id
4 having count(id)/(select count(*) from wdtbl where status=1)>0.4;ID
----------
4
ID STATUS
---------- --------------------
3 1
3 1
4 1
4 1
4 2
4 3
4 4
5 1
5 2
5 3ID Status=1的百分比
3 1
4 .4
5 .33333所以符合条件的ID只有3
2 (select id, count(id) id1 from wdtbl where status=1 group by id) t1,
3 (select id, count(id) id2 from wdtbl group by id) t2
4 where t1.id=t2.id and id1/id2>0.4;ID
----------
3效率不会太高
select id,status,count(status) as a_count
from old_table
group by id,status
order by id;create table b_table as
select id,count(status) as b_count
from old_table
group by id
order by id;select *
from a_table a,b_table b
where a.id=b.id
and a.status=1
and a.count/b.count>0.4;附:才学SQL一个星期,连基础都还有点没学完~``,只能写出这么长的代码了。不好意思~``
再就是不知道是我理解错了还是什么,题意应该是个多对多的关系吧,不然就做不了了~``
就是:一个ID可以对应多个status的值,一个status也可以对应多个ID吧~``
希望大家帮我哈~``
结贴
改为如下速度还可以
select id from wdtbl
group by id
having (sum(decode(status,1,1,0))/count(*))>0.4;