有一个表TEST,有三列,内容如下:
C1 C2 C3
1 2 A S
1 3 E D F
1 1 F
2 2 D R
3 1 W
3 4 W D F G
……
……
意思是:C2表示C3有几个字母。C3是具体的字母
要求查出如下的结果:
C1 C2 C3
1 6 A S E D F F
2 2 D R
3 5 W W D F G
意思是,以C1为准,把C2的数字加起来,把C3的字母也合并起来要求用SQL语句解决。不要写过程啊什么的。
C1 C2 C3
1 2 A S
1 3 E D F
1 1 F
2 2 D R
3 1 W
3 4 W D F G
……
……
意思是:C2表示C3有几个字母。C3是具体的字母
要求查出如下的结果:
C1 C2 C3
1 6 A S E D F F
2 2 D R
3 5 W W D F G
意思是,以C1为准,把C2的数字加起来,把C3的字母也合并起来要求用SQL语句解决。不要写过程啊什么的。
select c1,sum(c2) c2,replace(WMSYS.WM_CONCAT(c3),',',' ')
from test
group by c1
from ( select C1,C2,C3, rownum rnum,
row_number() over(partition by C1 order by C1) rn1 from a )
start with rn1=1
connect by rnum-1=prior rnum
group by C1
2) c3
FROM (SELECT a.*, ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c3) rn
FROM TEST)
GROUP BY c1
START WITH rn = 1
CONNECT BY rn - 1 = PRIOR rn AND c1 = PRIOR c110G 以下,用这个,我再去测试下两句还有什么错误
from TEST
group by C1 试试
/* Formatted on 2008/06/06 18:09 (Formatter Plus v4.8.8) */
WITH TEST AS
(SELECT 1 c1, 2 c2, 'A S' c3
FROM DUAL
UNION ALL
SELECT 1, 3, 'E D F'
FROM DUAL
UNION ALL
SELECT 1, 1, 'F'
FROM DUAL
UNION ALL
SELECT 2, 2, 'D R'
FROM DUAL
UNION ALL
SELECT 3, 1, 'W'
FROM DUAL
UNION ALL
SELECT 3, 4, 'W D F G'
FROM DUAL)
SELECT c1, SUM (c2) c2, REPLACE (wmsys.wm_concat (c3), ',', ' ') c3
FROM TEST
GROUP BY c1结果
Row# C1 C2 C31 1 6 A S E D F F
2 2 2 D R
3 3 5 W W D F G
WITH TEST AS
(SELECT 1 c1, 2 c2, 'A S' c3
FROM DUAL
UNION ALL
SELECT 1, 3, 'E D F'
FROM DUAL
UNION ALL
SELECT 1, 1, 'F'
FROM DUAL
UNION ALL
SELECT 2, 2, 'D R'
FROM DUAL
UNION ALL
SELECT 3, 1, 'W'
FROM DUAL
UNION ALL
SELECT 3, 4, 'W D F G'
FROM DUAL)
SELECT c1, SUM (c2) c2,
REPLACE (SUBSTR (MAX (SYS_CONNECT_BY_PATH (c3, ',')), 2),
',',
' '
) c3
FROM (SELECT a.*, ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c3) rn
FROM TEST a)
GROUP BY c1
START WITH rn = 1
CONNECT BY rn - 1 = PRIOR rn AND c1 = PRIOR c1结果Row# C1 C2 C31 1 6 A S E D F F
2 2 2 D R
3 3 5 W W D F G