现在 有个 表 字段为 A1,A2,A3,A4,A5我定义了几个变量 例如: v_B1 NUMBER;
v_B2 NUMBER;
v_B3 NUMBER;我要查出一个结果集;
这个结果集合要求:
select SUM(A1)/COUNT(DISTINCT A2) into v_B1 FROM t_daylog_agentoprinfo;
select SUM(A3)/SUM(A4) into v_B2 FROM t_daylog_agentoprinfo where a5='2222'
select SUM(A3) into v_B3 FROM t_daylog_agentoprinfo WHERE a5='234';这3个变量分别通过这个表的不同计算方式赋值!!!! 如何将这3个变量 拼接起来 并且查询出一个结果集合。。这样的语法怎么写??????
请各位帮下。
v_B2 NUMBER;
v_B3 NUMBER;我要查出一个结果集;
这个结果集合要求:
select SUM(A1)/COUNT(DISTINCT A2) into v_B1 FROM t_daylog_agentoprinfo;
select SUM(A3)/SUM(A4) into v_B2 FROM t_daylog_agentoprinfo where a5='2222'
select SUM(A3) into v_B3 FROM t_daylog_agentoprinfo WHERE a5='234';这3个变量分别通过这个表的不同计算方式赋值!!!! 如何将这3个变量 拼接起来 并且查询出一个结果集合。。这样的语法怎么写??????
请各位帮下。
(select SUM(A1)/COUNT(DISTINCT A2) FROM t_daylog_agentoprinfo) v_B1,
(select SUM(A3)/SUM(A4) FROM t_daylog_agentoprinfo where a5='2222') v_B2,
(select SUM(A3) FROM t_daylog_agentoprinfo WHERE a5='234') vB3
from dual;
(select SUM(A1)/COUNT(DISTINCT A2) FROM t_daylog_agentoprinfo) v_B1,
(select SUM(A3)/SUM(A4) FROM t_daylog_agentoprinfo where a5='2222') v_B2,
(select SUM(A3) FROM t_daylog_agentoprinfo WHERE a5='234') vB3
from dual;
|| ''''||v_TmpStr || ''' ShortName, '
|| ''''||TO_CHAR(v_BeginDate,'YYYYMMDDHH24MISS') ||''' BeginDate, '
|| ''''||TO_CHAR(v_EndDate,'YYYYMMDDHH24MISS') ||''' EndDate, '
|| ' ''000'' UserType, ''000'' SubUserType, '
|| ' a.NetType, '
|| ' a.InCallNum '
|| ' FROM ICD.t_H1V7_CallBy10011CG a, ICDMAIN.t_H1_City b '
|| ' WHERE a.CityID = b.ID ';
能不能 用这样的 形式去做呢 ??????????
declare
cursor c1 is select
(select SUM(A1)/COUNT(DISTINCT A2) FROM t_daylog_agentoprinfo) v_B1,
(select SUM(A3)/SUM(A4) FROM t_daylog_agentoprinfo where a5='2222') v_B2,
(select SUM(A3) FROM t_daylog_agentoprinfo WHERE a5='234') vB3
from dual;
type rec_type is record (b1 number,b2 number,b3 number)
type curs_type_array is table of curs_type index by binary_integer;
curs_type_rec rec_type;
curs_rec_array curs_type_array;
i number;
begin
for c1_rec in c1 loop
curs_type_rec.b1:=c1_rec.v_B1;
curs_type_rec.b2:=c1_rec.v_B2;
curs_type_rec.b3:=c1_rec.v_B3;
for i in 1..c1%rowcount loop
curs_rec_array(i):=curs_type_rec;
dbms_output.put_line('v_B1:'||curs_rec_array(i).b1||' '||'v_B2:'||curs_rec_array(i).b2||' '||'v_B3:'||curs_rec_array(i).b3);
end loop;
end loop;
end;
如果子查询复杂的话,可以考虑用with
例如
with A as (select sysdate from dual)
,B as (select sysdate-1 from dual)
,C as (select sysdate-2 from dual)
select * from A,B,C
是干什么的 ??? 能解释下吗 我语法很差的
SUM(DECODE(a5,'2222',A1,0))/COUNT(DISTINCT DECODE(a5,'2222',A2,0)) as v_B2,
SUM(DECODE(a5,'234',A3,0)) as v_B3
FROM t_daylog_agentoprinfo;
with A as (select sysdate from dual)
,B as (select sysdate-1 from dual)
,C as (select sysdate-2 from dual)
select * from A,B,C它将后面的每个名称(A,B,C)当作一个临时表!
此临时表都是一些虚表,即不占任何空间的!