表A
CODE QTY CODE1
A 1 A1
A 2 A1
A 3 A2
B 4 B1
怎么用一个SQL语句 变化这样
CODE1 SUM(QTY)
A1|A2 6
B1 4
就是要按照CODE分组,QTY算总和,CODE1算不同的编码相加 用|分割
CODE QTY CODE1
A 1 A1
A 2 A1
A 3 A2
B 4 B1
怎么用一个SQL语句 变化这样
CODE1 SUM(QTY)
A1|A2 6
B1 4
就是要按照CODE分组,QTY算总和,CODE1算不同的编码相加 用|分割
----- ---------- -----
A 1 A1
A 2 A1
A 3 A2
B 4 B1已用时间: 00: 00: 00.01
13:53:29 tina@PRACTICE> select replace(wm_concat(distinct code1),',','|') code1,sum(qty) from A group by code;CODE1 SUM(QTY)
----- ----------
A1|A2 6
B1 4已用时间: 00: 00: 00.01
(SELECT 'A' AS code, 1 AS qty, 'A1' AS code1
FROM dual
UNION ALL
SELECT 'A' AS code, 2 AS qty, 'A1' AS code1
FROM dual
UNION ALL
SELECT 'A' AS code, 3 AS qty, 'A2' AS code1
FROM dual
UNION ALL
SELECT 'A' AS code, 3 AS qty, 'A3' AS code1
FROM dual
UNION ALL
SELECT 'B' AS code, 4 AS qty, 'B1' AS code1
FROM dual
UNION ALL
SELECT 'B' AS code, 4 AS qty, 'B3' AS code1 FROM dual)
SELECT MAX (sys_connect_by_path(a.code1, '|')), a.qty
FROM(
SELECT t.code, t.code1, SUM(t.qty) over(PARTITION BY t.code) qty, row_number() over(PARTITION BY t.code
ORDER BY t.code) rn
FROM t) a
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1 AND a.code = PRIOR a.code
GROUP BY a.qty
CURSOR c_mrp_plan_dispatch(ci_dispatch_id mrp_dispatch_list.dispatch_id%TYPE) IS
SELECT plant,
operator_no,
item_code,
year_month,
req_date,
EDITION_ID,
TECHNIC_STATUS,
replace(wm_concat( VEHICLE_CODE),',','\') VEHICLE_CODE ,
sum(material_qty) material_qty
FROM mrp_dispatch_spread
WHERE dispatch_id = ci_dispatch_id
group by plant, operator_no, item_code, req_date, year_month, EDITION_ID,TECHNIC_STATUS
ORDER BY plant, operator_no, item_code, req_date,TECHNIC_STATUS;说DISTINCT 在此函数中禁用?为什么啊
CURSOR c_mrp_plan_dispatch(ci_dispatch_id mrp_dispatch_list.dispatch_id%TYPE) IS
SELECT plant,
operator_no,
item_code,
year_month,
req_date,
EDITION_ID,
TECHNIC_STATUS,
replace(wm_concat(DISTINCT VEHICLE_CODE),',','\') VEHICLE_CODE ,
sum(material_qty) material_qty
FROM mrp_dispatch_spread
WHERE dispatch_id = ci_dispatch_id
group by plant, operator_no, item_code, req_date, year_month, EDITION_ID,TECHNIC_STATUS
ORDER BY plant, operator_no, item_code, req_date,TECHNIC_STATUS; 说DISTINCT 在此函数中禁用?为什么啊
单就这句话应该没什么问题
楼主oracle什么版本
group by code;
create table ta(id int,name varchar2(10));
insert into ta select 1,'a1' from dual;
insert into ta select 1,'a2' from dual;
insert into ta select 1,'a3' from dual;
insert into ta select 1,'a4' from dual;
insert into ta select 2,'a1' from dual;
insert into ta select 2,'a2' from dual;
insert into ta select 3,'a3' from dual;
insert into ta select 3,'a4' from dual;
commit;
select id,getGroupContent(id) as GroupContent from ta
group by id;
ID GROUPCONTENT
1 a1,a2,a3,a4,a4
2 a1,a2,a2
3 a3,a4,a4select id,wmsys.wm_concat(name) from ta
group by id
group by code
如果是10g以上版本 用wm_concat吧 很强大
我们现在用的是11G了
group by code;
select 'A' code,1 qty,'A1' code1 from dual
union all
select 'A' code,2 qty,'A1' code1 from dual
union all
select 'A' code,3 qty,'A2' code1 from dual
union all
select 'B' code,4 qty,'B1' code1 from dual
)
select replace(wm_concat(distinct code1),',','|') code1,sum(qty) from A group by code;