oracle的常用sql語句

Ⅰ oracle sql 語句

1. select top 1 *from (select count(BId) as cc from brrow where getdate() between T_time and B_time group by stuID) order by cc desc
2.select a.stuname,b.title,b.T_time,b.B_time from student a join
(select borrow.stuID,borrow.T_time,borrow.B_time,book.title from borrow join book on borrow.BID=book.bid) b on a.stuID =b.stuID
3.select *from student where stuID not in (select stuid from borrow where gedate() >B_time)
4.
go
create proc login
@stuid number(10),
@password varchar2(20),
@re varchar(20) output
as
declare @st number
select @st = stuid from student where stuid=@st
if isnull(@st,0)=0 return '-1'
else if @password = (select password from student where stuid =@stuid) and @password is not null rturn '0'
else return select stuname from student where stuid =@stuid
go
5.go
create function dd()
@bid number(10)
as
if ((select bid from borrow where bid =@bid) is not null )
begin
print '必須先刪除借書信息記錄表中所有對應於該圖書的所有數據'
return
end
delete from book where bid =@bid
go

Ⅱ oracle 查詢數據只要排序後的第一條記錄（不用rownum=1），sql語句怎麼寫

1、創建測試表，

create table test_order(id number, value varchar2(50));

Ⅲ ORACLE SQL語句

創建表插入數據：

createtabletest
(wy1int,
wy2int,
wy3int,
wy4int);

insertintotestvalues(1,11,4,5);
insertintotestvalues(2,22,4,6);
insertintotestvalues(3,33,4,5);
insertintotestvalues(4,444,null,null);
insertintotestvalues(5,555,null,null);
insertintotestvalues(6,666,null,null);
commit;

執行：

selectt1.wy1,t1.wy2,t2.wy2wy3,t3.wy2wy4
fromtestt1
leftjointestt2
ont2.wy1=t1.wy3
leftjointestt3
ont3.wy1=t1.wy4
orderbywy1;

結果：