------- -----
| User | Rol |
------- -----
| user1 | r1 |
| user1 | r2 |
| user1 | r3 |
| user3 | r1 |
| user3 | r5 |
| user4 | r4 |
| user5 | r2 |
------- -----
A group by / count 將回傳：
select User, count(Rol) as 'RolCount'
from
table
group by User;
------- ----------
| User | RolCount |
------- ----------
| user1 | 3 |
| user3 | 2 |
| user4 | 1 |
| user5 | 1 |
------- ----------
是否可以having count(Rol) = 1在同一個 sql 陳述句中顯示記錄的角色名稱？像這樣：
------- ---------- -----
| User | RolCount | Rol |
------- ---------- -----
| user4 | 1 | r4 |
| user5 | 1 | r2 |
------- ---------- -----
uj5u.com熱心網友回復：
WITH CTE(USERR,ROL)AS
(
SELECT 'user1' , 'r1' UNION ALL
SELECT 'user1' ,'r2' UNION ALL
SELECT 'user1' ,'r3' UNION ALL
SELECT 'user3' ,'r1' UNION ALL
SELECT 'user3' ,'r5' UNION ALL
SELECT 'user4' ,'r4' UNION ALL
SELECT 'user5' ,'r2'
)
SELECT C.USERR,COUNT(C.USERR)CNTT,MAX(C.ROL)ROL
FROM CTE AS C
GROUP BY C.USERR
HAVING COUNT(C.USERR)=1
基于以上評論
uj5u.com熱心網友回復：
這是一種替代方法：
with aggt(usr, count_rol)as
(
select usr, count(rol) from t group by usr
)
select usr
, count_rol
, iif(count_rol=1,(select top(1) ROL from t where t.usr=aggt.usr),'')
from aggt
我包括了top(1)，但我沒有嘗試過，這也有效。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/346163.html
標籤：sql sql-server
上一篇：如何在oracle中根據組中的最小和最大日期獲取值？
下一篇：優化給定的sql查詢以提高速度