求高手写个sql啊,有一个表:
t(id number, score1 number, score2 number);
试写一个sql语句:查找一个id(如有多个id取最小id值),使得给定的数值80距离score1或score2列最近。如
1 70 90
2 60 85
则85最近,id=2再如
1 50 60
2 90 95
则90最近,id为2;再如
1 60 70
2 90 96
则id 1最小,id为1;分不多了,求助了!
t(id number, score1 number, score2 number);
试写一个sql语句:查找一个id(如有多个id取最小id值),使得给定的数值80距离score1或score2列最近。如
1 70 90
2 60 85
则85最近,id=2再如
1 50 60
2 90 95
则90最近,id为2;再如
1 60 70
2 90 96
则id 1最小,id为1;分不多了,求助了!
where temp =(select min(case when abs(t.score1-80)>abs(t.score2-80) then abs(t.score2-80) else abs(t.score1-80) end) from t)
where temp =(select min(case when abs(t.score1-80)>abs(t.score2-80) then abs(t.score2-80) else abs(t.score1-80) end) from t)
修改后的,取最小ID
[SYS@orcl] SQL>WITH T AS
2 (SELECT 1 ID, 70 SCORE1, 90 SCORE2 FROM DUAL
3 UNION ALL
4 SELECT 2 ID, 60 SCORE1, 85 SCORE2 FROM DUAL)
5 SELECT MIN(ID) KEEP(DENSE_RANK FIRST ORDER BY SCORE)
6 FROM (SELECT ID, ABS(SCORE1 - 80) SCORE FROM T
7 UNION
8 SELECT ID, ABS(SCORE2 - 80) SCORE FROM T);MIN(ID)KEEP(DENSE_RANKFIRSTORDERBYSCORE)
----------------------------------------
2[SYS@orcl] SQL>
[SYS@orcl] SQL>WITH T AS
2 (SELECT 1 ID, 50 SCORE1, 60 SCORE2 FROM DUAL
3 UNION ALL
4 SELECT 2 ID, 90 SCORE1, 95 SCORE2 FROM DUAL)
5 SELECT MIN(ID) KEEP(DENSE_RANK FIRST ORDER BY SCORE)
6 FROM (SELECT ID, ABS(SCORE1 - 80) SCORE FROM T
7 UNION
8 SELECT ID, ABS(SCORE2 - 80) SCORE FROM T);MIN(ID)KEEP(DENSE_RANKFIRSTORDERBYSCORE)
----------------------------------------
2[SYS@orcl] SQL>
[SYS@orcl] SQL>WITH T AS
2 (SELECT 1 ID, 60 SCORE1, 70 SCORE2 FROM DUAL
3 UNION ALL
4 SELECT 2 ID, 90 SCORE1, 96 SCORE2 FROM DUAL)
5 SELECT MIN(ID) KEEP(DENSE_RANK FIRST ORDER BY SCORE)
6 FROM (SELECT ID, ABS(SCORE1 - 80) SCORE FROM T
7 UNION
8 SELECT ID, ABS(SCORE2 - 80) SCORE FROM T);MIN(ID)KEEP(DENSE_RANKFIRSTORDERBYSCORE)
----------------------------------------
1
FROM T
WHERE LEAST(ABS(SCORE1 - 80), ABS(SCORE1 - 80)) =
(SELECT LEAST(MIN(ABS(SCORE1 - 80)), MIN(ABS(SCORE1 - 80))) FROM T)
LEAST多个最小