我有一張類似于下面的表格
| 保險號 | 由...制作 | 關閉者 |
|---|---|---|
| 1 | 用戶 A | 用戶 A |
| 2 | 用戶 A | 用戶 C |
| 3 | 用戶 B | 用戶 C |
| 4 | 用戶 B | 用戶 C |
| 5 | 用戶 B | 用戶 C |
從這張表中，我正在嘗試創建一個如下所示的視圖
| 用戶名 | 總創建 | 總關閉 |
|---|---|---|
| 用戶 A | 2 | 1 |
| 用戶 B | 3 | 0 |
| 用戶 C | 0 | 4 |
我無法弄清楚如何對表格進行分組以實作此視圖。任何幫助將不勝感激
uj5u.com熱心網友回復：
這是一個選項：
樣本資料（你已經有了，所以你不用輸入）：
SQL> with test (insurance_id, created_by, closed_by) as
2 (select 1, 'user a', 'user a' from dual union all
3 select 2, 'user a', 'user c' from dual union all
4 select 3, 'user b', 'user c' from dual union all
5 select 4, 'user b', 'user c' from dual union all
6 select 5, 'user b', 'user c' from dual
7 ),
查詢從這里開始：
8 all_users as
9 (select created_by username from test
10 union
11 select closed_by from test
12 )
13 select u.username,
14 sum(case when t.created_by = u.username then 1 else 0 end) total_created,
15 sum(case when t.closed_by = u.username then 1 else 0 end) total_closed
16 from all_users u cross join test t
17 group by u.username
18 order by u.username;
USERNA TOTAL_CREATED TOTAL_CLOSED
------ ------------- ------------
user a 2 1
user b 3 0
user c 0 4
SQL>
uj5u.com熱心網友回復：
我傾向于有一個單獨的用戶表，并且主表中只有一個整數 UserId。CROSS APPLY 應該避免兩次讀取同一個表。
SELECT X.UserName
,SUM(CASE WHEN X.Activity = 'Created' THEN 1 ELSE 0 END) AS TotalCreated
,SUM(CASE WHEN X.Activity = 'Closed' THEN 1 ELSE 0 END) AS TotalClosed
FROM YourTable T
CROSS APPLY
(
VALUES (T.CreatedBy, 'Created')
,(T.ClosedBy, 'Closed')
) X (UserName, Activity)
GROUP BY X.UserName
ORDER BY UserName;
uj5u.com熱心網友回復：
create table sometable (user_id, created_by,closed_by)
as
select 1, 'user a', 'user a' from dual union all
select 2, 'user a', 'user c' from dual union all
select 3, 'user b', 'user c' from dual union all
select 4, 'user b', 'user c' from dual union all
select 5, 'user b', 'user c' from dual;
SELECT *
FROM sometable
UNPIVOT ( username
FOR col IN ( created_by
, closed_by
)
)
PIVOT ( COUNT (user_id)
FOR col IN ( 'CREATED_BY' AS total_created
, 'CLOSED_BY' AS total_closed
)
)
ORDER BY username
;
USERNAME TOTAL_CREATED TOTAL_CLOSED
user a 2 1
user b 3 0
user c 0 4
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/454350.html
下一篇：在Python中從字串中獲取數字