SELECT BUS_STOP_COUNT,BUS_NAME INTO i_busstop_count,i_name FROM tb_result1_temp WHERE rowid= (select min(rowid) from (select min(BUS_STOP_COUNT) from tb_result1_temp)) ------------------------ | | | 相逢何必曾相识 | | | ------------------------
to jiezhi: 我用了你的方法,但是却出现错误:rowid不能用于group by建立的视图。 请再帮忙呀!!
SELECT BUS_STOP_COUNT, BUS_NAME, INTO i_busstop_count, i_name, FROM (select * from tb_result1_temp order by BUS_STOP_COUNT desc ,BUS_NAME desc) WHERE rownum <2
不好意思,上面的语句不要DESC SELECT BUS_STOP_COUNT, BUS_NAME, INTO i_busstop_count, i_name, FROM (select * from tb_result1_temp order by BUS_STOP_COUNT ,BUS_NAME ) WHERE rownum <2
典型的错误,select语句返回多个值,too_many_rows,用游标解决:DECLARE i_busstop_count tb_resultl_temp.bus_stop_count%type; i_name tb_resultl_temp.bus_name%type; cursor cur1 is SELECT BUS_STOP_COUNT,BUS_NAME, FROM tb_result1_temp WHERE BUS_STOP_COUNT=(select min(BUS_STOP_COUNT) from tb_result1_temp) GROUP by BUS_STOP_COUNT,BUS_NAME; BEGIN open cur1; loop exit when cur1%notfound; fetch cur1 into i_busstop_count ,i_name; dbms_output.put_line(v_empno||v_ename); end loop; close cur1; END;
用一个唯一标识它们 SELECT BUS_STOP_COUNT, BUS_NAME, INTO i_busstop_count, i_name, FROM tb_result1_temp a where exists(select min(BUS_STOP_COUNT) from tb_result1_temp where id=a.id group by BUS_STOP_COUNT,BUS_NAME); 速度上比较可取.
to beckhambobo: 谢谢,但问题是在这个表中的id列不是唯一的,该表的主键是(id,bus_name)的组合键,我该怎么办呀!请帮忙呀!
SELECT BUS_STOP_COUNT,BUS_NAME INTO i_busstop_count,i_name FROM tb_result1_temp WHERE BUS_STOP_COUNT=( select count from (select min(BUS_STOP_COUNT) count from tb_result1_temp) where rownum<2 ) GROUP by BUS_STOP_COUNT,BUS_NAME;
SELECT BUS_STOP_COUNT, BUS_NAME, INTO i_busstop_count, i_name, FROM tb_result1_temp WHERE BUS_STOP_COUNT=(select min(BUS_STOP_COUNT) from tb_result1_temp) and rownum=1 GROUP by BUS_STOP_COUNT, BUS_NAME;
to beckhambobo: 对不起,其实是主键是(id,order)的组合键,order列是后来加上去的,该列表示了乘车的顺序,只有两个值:p和n,这样做是为了防止出现单向站点的情况,即有的站点在正向的时候经过而在逆向的时候没有被经过,所以说id列并不是主键。 那么在这种情况下,怎样修改您的程序呢?请指教呀!!谢谢
先试试: select distinct tab.nm1,tab.nm2 from (select bus_stop_count nm1,bus_name nm2 from tb_result1_temp where bus_stop_count=(select min(bus_stop_count) from tb_result1_temp ) group by bus_stop_count) tab into i_busstop_count,i_name;若不行,建议再建立一个临时表,把group by 的若干条满足条件的记录存入,再从此临时表中用distinct语句取出第一条记录送入i_busstop_count,i_name两变量中。
select BUS_STOP_COUNT,BUS_NAME INTO i_busstop_count,i_name from (SELECT BUS_STOP_COUNT, BUS_NAME FROM lsb WHERE ( BUS_STOP_COUNT)=(select min(BUS_STOP_COUNT) from lsb) and rownum=1 GROUP by BUS_STOP_COUNT, BUS_NAME )
SELECT BUS_STOP_COUNT, BUS_NAME, INTO i_busstop_count, i_name, FROM tb_result1_temp a where exists(select min(BUS_STOP_COUNT) from tb_result1_temp where id=a.id and order=a.order group by BUS_STOP_COUNT,BUS_NAME);
还可以改造你的sql------------------------
| |
| 相逢何必曾相识 |
| |
------------------------
FROM tb_result1_temp
WHERE rowid=
(select min(rowid) from (select min(BUS_STOP_COUNT) from tb_result1_temp))
------------------------
| |
| 相逢何必曾相识 |
| |
------------------------
我用了你的方法,但是却出现错误:rowid不能用于group by建立的视图。
请再帮忙呀!!
BUS_STOP_COUNT,
BUS_NAME,
INTO
i_busstop_count,
i_name,
FROM (select * from tb_result1_temp order by BUS_STOP_COUNT desc ,BUS_NAME desc)
WHERE
rownum <2
SELECT
BUS_STOP_COUNT,
BUS_NAME,
INTO
i_busstop_count,
i_name,
FROM (select * from tb_result1_temp order by BUS_STOP_COUNT ,BUS_NAME )
WHERE
rownum <2
i_busstop_count tb_resultl_temp.bus_stop_count%type;
i_name tb_resultl_temp.bus_name%type;
cursor cur1 is
SELECT BUS_STOP_COUNT,BUS_NAME,
FROM tb_result1_temp
WHERE BUS_STOP_COUNT=(select min(BUS_STOP_COUNT)
from tb_result1_temp)
GROUP by BUS_STOP_COUNT,BUS_NAME;
BEGIN
open cur1;
loop
exit when cur1%notfound;
fetch cur1 into i_busstop_count ,i_name;
dbms_output.put_line(v_empno||v_ename);
end loop;
close cur1;
END;
SELECT
BUS_STOP_COUNT,
BUS_NAME,
INTO
i_busstop_count,
i_name,
FROM tb_result1_temp a
where exists(select min(BUS_STOP_COUNT) from tb_result1_temp where id=a.id group by BUS_STOP_COUNT,BUS_NAME);
速度上比较可取.
谢谢,但问题是在这个表中的id列不是唯一的,该表的主键是(id,bus_name)的组合键,我该怎么办呀!请帮忙呀!
FROM tb_result1_temp
WHERE BUS_STOP_COUNT=( select count from
(select min(BUS_STOP_COUNT) count from tb_result1_temp) where rownum<2 )
GROUP by BUS_STOP_COUNT,BUS_NAME;
BUS_STOP_COUNT,
BUS_NAME,
INTO
i_busstop_count,
i_name,
FROM tb_result1_temp
WHERE BUS_STOP_COUNT=(select min(BUS_STOP_COUNT) from tb_result1_temp) and rownum=1
GROUP by
BUS_STOP_COUNT,
BUS_NAME;
id,bus_name联合主键不会产生什么作用!!
对不起,其实是主键是(id,order)的组合键,order列是后来加上去的,该列表示了乘车的顺序,只有两个值:p和n,这样做是为了防止出现单向站点的情况,即有的站点在正向的时候经过而在逆向的时候没有被经过,所以说id列并不是主键。
那么在这种情况下,怎样修改您的程序呢?请指教呀!!谢谢
select distinct tab.nm1,tab.nm2 from
(select bus_stop_count nm1,bus_name nm2 from tb_result1_temp where bus_stop_count=(select min(bus_stop_count) from tb_result1_temp ) group by bus_stop_count) tab
into i_busstop_count,i_name;若不行,建议再建立一个临时表,把group by 的若干条满足条件的记录存入,再从此临时表中用distinct语句取出第一条记录送入i_busstop_count,i_name两变量中。
INTO i_busstop_count,i_name from
(SELECT
BUS_STOP_COUNT,
BUS_NAME
FROM lsb
WHERE ( BUS_STOP_COUNT)=(select min(BUS_STOP_COUNT) from lsb) and rownum=1
GROUP by
BUS_STOP_COUNT,
BUS_NAME )
BUS_STOP_COUNT,
BUS_NAME,
INTO
i_busstop_count,
i_name,
FROM tb_result1_temp a
where exists(select min(BUS_STOP_COUNT) from tb_result1_temp where id=a.id and order=a.order group by BUS_STOP_COUNT,BUS_NAME);