sorry: select * from tab union all select * from tab where qty=3 union all select * from tab where qty=3
select * from tab union all select * from tab where qty=3 union all select * from tab where qty=3 就这样实现了,还可以
对不起,我没有说清楚,假如我的QTY=1000,怎么办,要写999个UINIO ALL吗?有没有不写999个UINIO ALL 的办法?
你在结果集中每行取QTY行中的次数不就行了.
select a.* from test a,(select qty from test) b where a.qty >= b.qty; 看行不行吧?
select a.* from test a,test b where a.qty >= b.qty and a.memid <> b.memid order by a.memid; 还是这样好些!
可以这样: 构造一个表t_2: qtt 构造数据:在其中插入数据:qtt=3插入3条记录,qqt=2插入2条记录,qqt=1插入1条记录: 然后 select a.memid from t_1 a,t_2 b where a.qtt=b.qtt; 虽然麻烦点,但确实可行 要做的灵活一点可以设置以参数表存发每一个qqt的值查询几次 让后写一pl/sql语句,自动根据参数在t_2表种重复插入相应的数据 再查询也ok
只能用过程实现. CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; END pkg_test; /create or replace procedure get(p_rc out pkg_test.myrctype) as cursor t_sor is select MEMID,QTY from TAB; str varchar2(50); begin for v_sor in t_sor loop for i in 1..to_number(v_sor.QTY) loop str:=str||'select MEMID from TAB where QTY=v_sor.QTY union '; end loop; end loop; str:=substr(str,1,length(str)-7); open p_rc for str; end; /declare v_rc pkg_test.myrctype; v_MEMID TAB.MEMID%type; begin get(v_rc); loop fetch v_rc into v_MEMID; exit when v_rc%notfound; dbms_output.put_line(v_MEMID); end loop; end; /
select * from tab
union all
select * from tab where qty=3
union all
select * from tab where qty=3
union all
select * from tab where qty=3
union all
select * from tab where qty=3
就这样实现了,还可以
where a.qty >= b.qty;
看行不行吧?
where a.qty >= b.qty and a.memid <> b.memid
order by a.memid;
还是这样好些!
构造一个表t_2:
qtt
构造数据:在其中插入数据:qtt=3插入3条记录,qqt=2插入2条记录,qqt=1插入1条记录:
然后
select a.memid
from t_1 a,t_2 b
where a.qtt=b.qtt;
虽然麻烦点,但确实可行
要做的灵活一点可以设置以参数表存发每一个qqt的值查询几次
让后写一pl/sql语句,自动根据参数在t_2表种重复插入相应的数据
再查询也ok
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/create or replace procedure get(p_rc out pkg_test.myrctype)
as
cursor t_sor is
select MEMID,QTY from TAB;
str varchar2(50);
begin
for v_sor in t_sor loop
for i in 1..to_number(v_sor.QTY) loop
str:=str||'select MEMID from TAB where QTY=v_sor.QTY union ';
end loop;
end loop;
str:=substr(str,1,length(str)-7);
open p_rc for str;
end;
/declare
v_rc pkg_test.myrctype;
v_MEMID TAB.MEMID%type;
begin
get(v_rc);
loop
fetch v_rc into v_MEMID;
exit when v_rc%notfound;
dbms_output.put_line(v_MEMID);
end loop;
end;
/