基本上查詢的當前條件是
WHERE data_payload_uri BETWEEN
'/organization/team/folder/2021'
AND
'/organization/team/folder/2022'
這將獲取 2021 年的所有資料。
data_payload_uri 資料的示例如下所示：
/organization/team/folder/20210101/orig
/organization/team/folder/20210102/orig
/organization/team/folder/20210102/orig_v1
/organization/team/folder/20210103/orig
/organization/team/folder/20210104/orig
/organization/team/folder/20210105/orig
/organization/team/folder/20210105/orig_v1
/organization/team/folder/20210105/orig_v2
我想做的只是查詢直到最后一個正斜杠的行，該行不是唯一的。
這意味著，我不想查詢只有一個原點的行
/organization/team/folder/20210101/orig
/organization/team/folder/20210103/orig
/organization/team/folder/20210104/orig
但我確實想查詢所有其他行
/organization/team/folder/20210105/orig
/organization/team/folder/20210105/orig_v1
/organization/team/folder/20210105/orig_v2
/organization/team/folder/20210102/orig
/organization/team/folder/20210102/orig_v1
做這個的最好方式是什么？如果有任何不清楚的地方，請告訴我，感謝您的幫助
uj5u.com熱心網友回復：
您可以使用分析COUNT功能：
SELECT *
FROM (
SELECT t.*,
COUNT(DISTINCT data_payload_uri) OVER (
PARTITION BY SUBSTR(data_payload_uri, 1, INSTR(data_payload_uri, '/', -1))
) AS cnt
FROM table_name t
WHERE data_payload_uri >= '/organization/team/folder/2021'
AND data_payload_uri < '/organization/team/folder/2022'
)
WHERE cnt > 1
其中，對于樣本資料：
CREATE TABLE table_name (id, data_payload_uri) AS
SELECT 1, '/organization/team/folder/20210101/orig' FROM DUAL UNION ALL
SELECT 2, '/organization/team/folder/20210102/orig' FROM DUAL UNION ALL
SELECT 3, '/organization/team/folder/20210102/orig_v1' FROM DUAL UNION ALL
SELECT 4, '/organization/team/folder/20210103/orig' FROM DUAL UNION ALL
SELECT 5, '/organization/team/folder/20210104/orig' FROM DUAL UNION ALL
SELECT 6, '/organization/team/folder/20210105/orig' FROM DUAL UNION ALL
SELECT 7, '/organization/team/folder/20210105/orig_v1' FROM DUAL UNION ALL
SELECT 8, '/organization/team/folder/20210105/orig_v2' FROM DUAL;
輸出：
ID DATA_PAYLOAD_URI 碳納米管 2 /組織/團隊/檔案夾/20210102/orig 2 3 /組織/團隊/檔案夾/20210102/orig_v1 2 6 /組織/團隊/檔案夾/20210105/orig 3 7 /組織/團隊/檔案夾/20210105/orig_v1 3 8 /組織/團隊/檔案夾/20210105/orig_v2 3
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/425013.html
上一篇：如何在SQL中獲取不匹配的記錄