我有這個資料：
| 應用程式 | 屁股 | mod_asscs |
|---|---|---|
| 一種 | 56 | CB-56 |
| 一種 | 67 | CB-67 |
| b | 38 | CB-38 |
| 一種 | 12 | 12 |
我想按列'app'分組并計算'mod_asscs'值等于concat（'cb-'，asscs）的情況。我還想在單獨的列“mod_asscs_array”中輸出陣列，以便輸出如下：
| 應用程式 | mod_asscs_array | scs_count |
|---|---|---|
| 一種 | CB-56、CB-67 | 2 |
| b | CB-38 | 1 |
到目前為止，這就是我所擁有的：
SELECT DISTINCT
app,
( CASE WHEN concat('cb-', asscs) = mod_asscs THEN mod_asscs || ',') AS mod_asscs_array,
COUNT( CASE WHEN concat('cb-', asscs) = mod_asscs THEN mod_asscs || ',') AS scs_count
FROM data_table
GROUP BY
app
uj5u.com熱心網友回復：
看起來像聚合。
樣本資料：
SQL> with test (app, asscs, mod_asscs) as
2 (select 'a', 56, 'cb-56' from dual union all
3 select 'a', 67, 'cb-67' from dual union all
4 select 'b', 38, 'cb-38' from dual union all
5 select 'a', 12, '12' from dual
6 )
詢問：
7 select app,
8 listagg(mod_asscs, ', ') within group (order by mod_asscs) array,
9 count(*) cnt
10 from test
11 where mod_asscs = 'cb-'|| asscs
12 group by app;
A ARRAY CNT
- -------------------- ----------
a cb-56, cb-67 2
b cb-38 1
SQL>
uj5u.com熱心網友回復：
如果您需要顯示所有“應用程式”，即使是“計數”為零的應用程式，那么您需要條件聚合，如下所示：
with
test (app, asscs, mod_asscs) as (
select 'a', 56, 'cb-56' from dual union all
select 'a', 67, 'cb-67' from dual union all
select 'b', 38, 'cb-38' from dual union all
select 'a', 12, '12' from dual union all
select 'c', 33, 'cb-23' from dual
)
select app,
listagg(case when mod_asscs = 'cb-' || asscs
then mod_asscs end, ', ')
within group (order by asscs) as mod_asscs_array,
count(case when mod_asscs = 'cb-' || asscs
then mod_asscs end) as scs_count
from test
group by app
order by app -- if needed
;
APP MOD_ASSCS_ARRAY SCS_COUNT
--- -------------------- ----------
a cb-56, cb-67 2
b cb-38 1
c 0
uj5u.com熱心網友回復：
如果陣列中有重復項，我應該這樣做
listagg(distinct..嗎count(distinct case when ...？
不能使用LISTAGG(DISTINCT ...，因為LISTAGG函式目前不支持DISTINCT關鍵字；相反，您需要DISTINCT先在子查詢中使用，然后使用LISTAGG：
SELECT app,
LISTAGG(mod_asscs, ',') WITHIN GROUP (ORDER BY mod_asscs)
AS mod_asscs_array,
COUNT(*) AS scs_count
FROM (
SELECT DISTINCT
app,
mod_asscs
FROM data_table
WHERE 'cb-' || asscs = mod_asscs
)
GROUP BY app
其中，對于樣本資料：
CREATE TABLE data_table (app, asscs, mod_asscs) AS
SELECT 'a', 56, 'cb-56' FROM DUAL UNION ALL
SELECT 'a', 56, 'cb-56' FROM DUAL UNION ALL
SELECT 'a', 67, 'cb-67' FROM DUAL UNION ALL
SELECT 'b', 38, 'cb-38' FROM DUAL UNION ALL
SELECT 'a', 12, '12' FROM DUAL;
輸出：
應用程式 MOD_ASSCS_ARRAY SCS_COUNT 一種 CB-56,CB-67 2 b CB-38 1
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432939.html
標籤：甲骨文