我有兩個 Oracle 表：
USER(ID*,NAME,SURNAME)
MATCH(ID*,START_DATE,END_DATE,MATCH_CODE,ID_USER**)
我需要一個查詢來為每個用戶獲取 END_DATE 和 START_DATE 之間的最大秒差以及 NAME 和 MATCH_CODE 欄位的匹配。
我的查詢：
SELECT A.ID,A.NAME,MAX(extract(second from (END_DATE-START_DATE))
extract(minute from (END_DATE-START_DATE)*60
extract(hour from (END_DATE-START_DATE)*60*60
extract(day from (END_DATE-START_DATE)*60*60*24) max_differance
FROM USER A JOIN MATCH B
ON A.ID = B.ID_USER
GROUP BY A.ID;
我正在考慮這個查詢，但顯然它給出了一個錯誤，因為在 GROUP BY 中選擇的所有欄位都去。另外我需要 MATCH_CODE 欄位，我該怎么辦？
uj5u.com熱心網友回復：
聚合name列并使用MAX ... KEEP以獲取match_code：
SELECT u.id,
MAX(u.name) AS name,
MAX(end_date - start_date)*24*60*60 AS max_difference,
MAX(match_code) KEEP (
DENSE_RANK LAST
ORDER BY end_date - start_date NULLS FIRST
) As match_code
FROM "USER" u
INNER JOIN match m
ON (u.id = m.id_user)
GROUP BY u.id
或者，使用決議函式：
SELECT id,
name,
max_difference,
match_code
FROM (
SELECT u.id,
u.name,
(end_date - start_date)*24*60*60 AS max_difference,
match_code,
ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY end_date - start_date DESC)
AS rn
FROM "USER" u
INNER JOIN match m
ON (u.id = m.id_user)
)
WHERE rn = 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/350101.html