初学者求一段SQL统计语句

2013-11-21

菜鸟求一段SQL统计语句表 table1 有以下数据：STL_IDSTL_NAMESTL_INVOICESTL_COUNT1NAME1INVOICE1101NAME1I

菜鸟求一段SQL统计语句
表 table1 有以下数据：
STL_IDSTL_NAMESTL_INVOICESTL_COUNT
1NAME1          INVOICE110
1NAME1          INVOICE220
1NAME1          INVOICE330
2NAME2          INVOICE410
2NAME2          INVOICE530

希望最后的统计结果如下：

STL_IDSTL_NAMESTL_INVOICE              STL_COUNT
1NAME1        INVOICE1,INVOICE2,INVOICE360
2NAME2        INVOICE4,INVOICE5        40

请问SQL语句要怎么写？

[解决办法]


SELECT STL_ID,STL_NAME,
       WM_CONCAT(STL_INVOICE) STL_INVOICE,
       SUM(STL_COUNT) STL_COUNT
FROM TABLE1
GROUP BY STL_ID,STL_NAME
ORDER BY STL_ID,STL_NAME

[解决办法]
还可以用listagg
[解决办法]
10g的话用分组函数wm_concat()
select
stl_id,stl_name,wm_concat(stl_invoice) stl_invoice ,sum(stl_count) stl_count
group by stl_id,stl_name
order by stl_id,stl_name;
[解决办法]
3楼的最简洁
10G上的版本wm_concat
11g listagg

[解决办法]
wm_concat在12c已经废弃，建议用listagg
[解决办法]

引用:

3楼的最简洁
10G上的版本wm_concat
11g listagg

热点排行

oracle

初学者求一段SQL统计语句