请教下,我有一个查询,如下:
select agent_id,sum(t.charge_amt)
from tdaily_charge t
where (t.agent_id=i or t.agent_p_id=i or t.agent_pp_id=i)
and t.dt between
to_date('2009-4-1','yyyy-mm-dd')
and to_date('2009-4-30','yyyy-mm-dd')其中i的值在(21,2192,42,146,223,340,3800,439,590,750,1771,2236,1828,1955,2216,2290,2292,2348,2460,2735,2936,3363,3369,3378,4317)这些数字中取,请问如果用PL/SQL写的话,应该如何来写,在线等高手解决!
谢谢!
select agent_id,sum(t.charge_amt)
from tdaily_charge t
where (t.agent_id=i or t.agent_p_id=i or t.agent_pp_id=i)
and t.dt between
to_date('2009-4-1','yyyy-mm-dd')
and to_date('2009-4-30','yyyy-mm-dd')其中i的值在(21,2192,42,146,223,340,3800,439,590,750,1771,2236,1828,1955,2216,2290,2292,2348,2460,2735,2936,3363,3369,3378,4317)这些数字中取,请问如果用PL/SQL写的话,应该如何来写,在线等高手解决!
谢谢!
解决方案 »
- 初学oracle
- 自动扩展回滚表空间
- 在ORACLE中只知道SYSTEM的用户和密码能否将这个库中的几个用户下的对象全部备份下来急!急!
- 求oracle中, 如何把表中一条记录中多行字符串合并成一行字符串----在线等
- oracle数据导入导出问题
- 怎样屏蔽查询DB_LINKS
- 为什么我在网上下载了一个oracle不能安装?我制作成虚拟光驱还是不能安装。共637M
- oracle里用sql语句select查询出现“无法在本地管理的表空间创建临时段”是什么原因?
- 一个问题困扰了我几个星期了,到了几个论坛都没解决
- 请问,在ORACLE 数据库中执行简单的SQL语句,是不是不支持左取字符--在线等候!
- 几个ORACLE分布数据库合并到一个ORACLE数据库
- 一个比较复杂的sql,重复记录的标记问题!
能给个例子吗,动态SQL不会啊...
from tdaily_charge t
where (t.agent_id in (...) or t.agent_p_id in (...) or t.agent_pp_id in (...))
and t.dt between
to_date('2009-4-1','yyyy-mm-dd')
and to_date('2009-4-30','yyyy-mm-dd')
ssql varchar2(200);
begin
ssql := 'select * from table '|| 'where id in '|| '(21,2192,42,146,223,340,3800,439,590,750,1771,2236,1828,1955,2216,2290,2292,2348,2460,2735,2936,3363,3369,3378,4317)';
execute ssql;
end;
CREATE OR REPLACE FUNCTION fnc_charge
(
in_agent_id IN NUMBER,
in_beg_date IN DATE,
in_end_date IN DATE
)
RETURN NUMBER
IS
v_sql VARCHAR2(4000);
n_charge_amt NUMBER;
BEGIN
v_sql := 'select agent_id,sum(t.charge_amt)
from tdaily_charge t
where (t.agent_id=:i or t.agent_p_id=:i or t.agent_pp_id=:i)
and t.dt between to_date('':beg_date'',''yyyy-mm-dd'')
and to_date('':end_date'',''yyyy-mm-dd'')';
EXECUTE IMMEDIATE v_sql INTO n_charge_amt USING in_agent_id,in_beg_date,in_end_date;
RETURN n_charge_amt;
END;
/
from tdaily_charge t
where (t.agent_id=i or t.agent_p_id=i or t.agent_pp_id=i)
and t.dt between
to_date('2009-4-1','yyyy-mm-dd')
and to_date('2009-4-30','yyyy-mm-dd')
group by agent_id这个查询所用到的表结构如下:
ID NUMBER(10) 系统编码
DT DATE 日期
AGENT_ID NUMBER(10) 代理商ID
CITY_NO CHAR(5) 所属城市编码
DISTRICT_NO CHAR(3) 所属地区编码
AGENT_P_ID NUMBER(10) Y 上级代理商ID
AGENT_PP_ID NUMBER(10) Y 上上级代理商ID
CHARGE_QTY NUMBER(10) Y 充值笔数
CHARGE_AMT NUMBER(18,2) Y 充值金额因为是要统计大代理的销量(包含下级和下下级),所以在查询语句中使用or条件,4楼的查询方法是不可用的,会把下级的充值量也统计出来。这个查询是应该每次查询后替换i的值进行下一次查询,最后输出每次查询的结果。谢谢各位帮忙!
select agent_id,sum(t.charge_amt)
from tdaily_charge t
where (t.agent_id=:i or t.agent_p_id=:i or t.agent_pp_id=:i)
and t.dt between to_date('':beg_date'',''yyyy-mm-dd'')
and to_date('':end_date'',''yyyy-mm-dd'')
group by agent_id
修改如下:
with id_list as (select column_value id from table(sys.odcivarchar2list(21,2192,42,146,223,340,3800,439,590,750,1771,2236,1828,1955,2216,2290,2292,2348,2460,2735,2936,3363,3369,3378,4317)))
select agent_id,sum(t.charge_amt)
from tdaily_charge t1,id_list t2
where (t1.agent_id=t2.id or t1.agent_p_id=t2.id or t1.agent_pp_id=t2.id)
and t1.dt between to_date('2009-4-1','yyyy-mm-dd')
and to_date('2009-4-30','yyyy-mm-dd')
group by agent_id
from tdaily_charge t
where (t.agent_id in (21,2192,42,146,223,340,3800,439,590,750,1771,2236,1828,1955,2216,2290,2292,2348,2460,2735,2936,3363,3369,3378,4317)
and t.dt between
to_date('2009-4-1','yyyy-mm-dd')
and to_date('2009-4-30','yyyy-mm-dd')
CREATE OR REPLACE TYPE my_ODCIVarchar2List AS VARRAY(32767) OF VARCHAR2(4000);
9i要是不支持with子句的话直接替换掉
with id_list as (select column_value id from table(my_ODCIVarchar2List(21,2192,42,146,223,340,3800,439,590,750,1771,2236,1828,1955,2216,2290,2292,2348,2460,2735,2936,3363,3369,3378,4317)))
select agent_id,sum(t.charge_amt)
from tdaily_charge t1,id_list t2
where (t1.agent_id=t2.id or t1.agent_p_id=t2.id or t1.agent_pp_id=t2.id)
and t1.dt between to_date('2009-4-1','yyyy-mm-dd')
and to_date('2009-4-30','yyyy-mm-dd')
group by agent_id