select id,sum(数量) from table group by id
解决方案 »
- Oracle库怎样设置能忽略中文逗号(刚发现是能忽略的,然后换库报错了,希望不要版本打回。。。)
- 数据库链、EXP/IMP性能问题?
- 怎样把这个字段的值导更新到DATE型字段中
- 各位大侠,请求一个oracle存储过程和临时表的问题!急急!
- 这条SQL怎么写?
- access 导入oracle ??
- 为什么在pl/sql的定义游标时,我传的参数不好用
- 关于在oacle中删除一具有DBA权限的用户,为何用system/manager@服务名进入sqlplus出错
- 有关oracle中关于连接查询的问题?(着急!!!)
- 谁给个下载oracle8.1.7 for linux 的详细下载地址?(要具体一点)
- 请看这SQL文,错在哪里?
- 远程连接数据库方法
CREATE OR REPLACE FUNCTION GET_NAME(ID IN VARCHAR2) RETURN VARCHAR2 IS
RE_NAME VARCHAR2(100);
IN_NAME VARCHAR2(100);
TYPE CUR_NANE IS REF CURSOR;
CR_PRONAME CUR_NANE;
BEGIN
OPEN CR_PRONAME FOR SELECT A.BUZITYPENAME FROM BUZI_TYPE A WHERE A.BUZITYPECODE = ID;
LOOP
FETCH CR_PRONAME INTO IN_NAME;
EXIT WHEN CR_PRONAME%NOTFOUND;
RE_NAME := RE_NAME || ',' || IN_NAME;
END LOOP;
CLOSE CR_PRONAME;
RETURN(RE_NAME);
EXCEPTION
WHEN OTHERS THEN
RE_NAME := '';
RETURN(RE_NAME);
END GET_NAME;调用:
select COUNT(T.BUZITYPECODE),GET_NAME(T.BUZITYPECODE) AS AA from buzi_type t
GROUP BY T.BUZITYPECODE
再写个函数
CREATE OR REPLACE Function get_shop(c_id in varchar2) return varchar2 is
CURSOR c_do IS select 商品 from table where id=c_id;
v_DO c_do%ROWTYPE;
v_name varchar2(200);
begin
OPEN c_do;
LOOP
FETCH c_do INTO v_DO;
EXIT WHEN c_do%NOTFOUND;
BEGIN
v_name:=v_name||v_do.商品||',';
end;
END LOOP;
CLOSE c_do;
return v_name;
end;
select id,sum(数量),substr(get_shop(id),2) "商品" from table group by id
CREATE OR REPLACE Function get_shop(c_id in varchar2) return varchar2 is
CURSOR c_do IS select 商品 from table where id=c_id;
v_DO c_do%ROWTYPE;
v_name varchar2(200);
begin
OPEN c_do;
LOOP
FETCH c_do INTO v_DO;
EXIT WHEN c_do%NOTFOUND;
BEGIN
v_name:=v_name||','||v_do.商品;
end;
END LOOP;
CLOSE c_do;
return v_name;
end;