我有一個看起來像這樣的 oracle sql 表
"STUDENT_ID","FULL_NAME","SEMESTER_ID","STIP_ID"
"1","Liam Bottrill","1","1"
"1","Liam Bottrill","2","3"
"1","Liam Bottrill","3","2"
"1","Liam Bottrill","4","5"
"2","Maurits Smitham","1","6"
"2","Maurits Smitham","2",""
"2","Maurits Smitham","3","2"
"2","Maurits Smitham","4","6"
"43","Jackie Cotton","1",""
"43","Jackie Cotton","2",""
"43","Jackie Cotton","3",""
"43","Jackie Cotton","4",""
我想按“STUDENT_ID”對該表進行分組，并從結果中排除任何“STIP_ID”行為空的學生
我的目標是這樣的結果：
"STUDENT_ID","FULL_NAME"
"1","Liam Bottrill"
Liam Bottrill 應該被顯示，而 Maurits Smitham 和 Jackie Cotton 應該被排除在結果之外
你能幫我處理這樣的聚合函式嗎？
uj5u.com熱心網友回復：
這是使用聚合的一種方法：
SELECT *
FROM yourTable
WHERE STUDENT_ID IN (
SELECT STUDENT_ID
FROM yourTable
GROUP BY STUDENT_ID
HAVING COUNT(CASE WHEN STIP_ID IS NULL THEN 1 END) = 0
);
另一種方式，使用存在邏輯：
SELECT t1.*
FROM yourTable t1
WHERE NOT EXISTS (
SELECT 1
FROM yourTable t2
WHERE t2.STUDENT_ID = t1.STUDENT_ID AND
t2.STIP_ID IS NULL
);
uj5u.com熱心網友回復：
您可以通過該識別符號組，然后使用條件聚集找到學生，在當計數STIP_ID為NULL（在Oracle中，是一樣的一個空字串）：
SELECT student_id,
MAX(full_name) AS full_name
FROM table_name
GROUP BY student_id
HAVING COUNT(CASE WHEN stip_id IS NULL THEN 1 END) = 0;
其中，對于您的示例資料：
CREATE TABLE table_name (STUDENT_ID, FULL_NAME, SEMESTER_ID, STIP_ID) AS
SELECT 1, 'Liam Bottrill', 1, 1 FROM DUAL UNION ALL
SELECT 1, 'Liam Bottrill', 2, 3 FROM DUAL UNION ALL
SELECT 1, 'Liam Bottrill', 3, 2 FROM DUAL UNION ALL
SELECT 1, 'Liam Bottrill', 4, 5 FROM DUAL UNION ALL
SELECT 2, 'Maurits Smitham', 1, 6 FROM DUAL UNION ALL
SELECT 2, 'Maurits Smitham', 2, NULL FROM DUAL UNION ALL
SELECT 2, 'Maurits Smitham', 3, 2 FROM DUAL UNION ALL
SELECT 2, 'Maurits Smitham', 4, 6 FROM DUAL UNION ALL
SELECT 43, 'Jackie Cotton', 1, NULL FROM DUAL UNION ALL
SELECT 43, 'Jackie Cotton', 2, NULL FROM DUAL UNION ALL
SELECT 43, 'Jackie Cotton', 3, NULL FROM DUAL UNION ALL
SELECT 43, 'Jackie Cotton', 4, NULL FROM DUAL;
輸出：
學生卡 全名 1 利亞姆·博特里爾
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/376668.html
下一篇：如何將視頻插入Oracle資料庫