table中有3个字段 name1(字符串) name2(字符串) value(浮点),
name1 name2 value
aa bb 1.2
aa cc 1.5
dd aa 1.3
bb cc 1.6
cc dd 1.8
问如何写一sql语句查询name1 name2中出现的字符串的value值的和
例如:aa 1.2+1.5+1.3=
bb ...
cc ...
dd ...
name1 name2 value
aa bb 1.2
aa cc 1.5
dd aa 1.3
bb cc 1.6
cc dd 1.8
问如何写一sql语句查询name1 name2中出现的字符串的value值的和
例如:aa 1.2+1.5+1.3=
bb ...
cc ...
dd ...
from
(select name1,value from tb
union all select name2 value tb
)
修正一下select name1,replace(wmsys.wm_concat(value),',','+') as value
from
(select name1,value from table01
union all
select name2,value from table01
)
group by name1
getValueCREATE OR REPLACE function getValue(p_name IN VARCHAR2) RETURN VARCHAR2 AS
qq1 DBMS_UTILITY.uncl_array;
l_tablen NUMBER;
l_tab VARCHAR2 (1000);
BEGIN
SELECT value
BULK COLLECT INTO qq1
FROM (select name1,value from table01
union all
select name2,value from table01)
WHERE name1 = p_name; DBMS_UTILITY.table_to_comma (qq1, l_tablen, l_tab);
RETURN REPLACE(l_tab,',','+');
END;查询:column value format a100select name1, getvalue(name1) value
from(
select name1 from table01
union
select name2 from table01);
( select name1,value from tb
union all
select name2,value from tb where name1<>name2
)
group by name1 order by sum(value)desc多谢各位