请用oracle语句写出第一个不是顺序排列的数
看到 monta_ellis 在http://www.dewen.org/q/12209/提的mysql语句的问题:
"在一个叫team的表有个int的字段（字段叫t_id）分别是1、2、4、5...200，最后一个数是200，一共100个数，请用mysql语句写出第一个不是顺序排列的数（ 在这里就是4）？", 我试着用Oracle语句来实现,
select t_id from
(select t_id from
(select t_id,rownum rn from(select t_id from team order by t_id)),
(select min(t_id) as min_t_id from team)
where t_id <> (rn+min_t_id-1))
where rownum=1;
给出数据 1,2,4,5,8, 输出4. 给出数据 3,4,5,8,10, 输出8. 功能上应该没问题..
大家有什么更好的方法么
老哈Und
10 years, 11 months ago
Answers
CREATE TABLE TEMP_T
(
T_ID NUMBER
);
方法一:
SELECT *
FROM (SELECT t_id, ROWNUM + (SELECT MIN (t_id) FROM temp_t) - 1 AS new_id
FROM ( SELECT *
FROM TEMP_T
ORDER BY 1))
WHERE new_id <> t_id AND ROWNUM = 1;
方法二:
SELECT *
FROM (SELECT t_id, LAG (t_id ) OVER (ORDER BY t_id)+1 AS t_id2
FROM ( SELECT t_id
FROM TEMP_T
ORDER BY 1) a)
WHERE t_id2 IS NOT NULL AND t_id > t_id2 AND ROWNUM = 1;
方法三:
SELECT *
FROM (SELECT t_id, ROWNUM rn
FROM ( SELECT t_id
FROM TEMP_T
ORDER BY 1)) a,
(SELECT t_id2, ROWNUM rn2
FROM ( SELECT t_id + 1 AS t_id2
FROM TEMP_T
ORDER BY 1)) b
WHERE A.rn = b.rn2 + 1 AND t_id > t_id2 AND ROWNUM = 1;
长着羽翼的赛姬
answered 10 years, 11 months ago