1. sql語句問題
（select T.* ,row rn from (strSqlbase.tostring())是這條句話的結果集的別名吧
2. SQL將上一行的值賦值給下一行
rownum是表中列名，還是oracle的偽列？如果是列名，則如下
update t set
name=(select name from t s where s.rn<t.rn and s.name is not null),
f2=(select f2 from t s where s.rn<t.rn and and s.f2 is not null)
3. 用SQL語句排名次
selectt.classid,score,row_number()over(orderbyscoredesc)rn
from
(selectclassid,avg(score)scorefromscores)t
sqlserver下寫法
4. sql資料庫
你這個東西沒有標准答案，因為你的商業規則沒有表述得特別清楚。
我按自己的通常理解，寫了一個，希望能幫得到你：
廢話說說，上代碼：
--create table
drop table proct purge;
create table proct (
cProctCode varchar2(50)
,mPrice number
);
drop table proct_new purge;
create table proct_new (
cProctCode varchar2(50)
,mPrice_new number
);
--create sequence(for demo)
drop sequence seq_prd;
create sequence seq_prd;
--generate some procts in randome, satisfied:
--1) price between 1.00 and 95.00 RMB
--2) avg price between 50/1.05 and 50
--for common, here we generate 1000 procts
declare
v_try number;
v_avgprice number;
begin
v_try:=0;
loop
v_try:=v_try+1;
if(v_try>10)then
dbms_output.put_line('too many times to try,bye bye!');
exit;
end if;
delete from proct;
for idx in 1..1000
loop
insert into proct values('prd'||substr(10000000000+seq_prd.nextval,2),round(dbms_random.value(1,95),2));
end loop;
commit;
select avg(mprice) into v_avgprice from proct;
if(v_avgprice>(50/1.05) and v_avgprice<50)then
dbms_output.put_line('try ok,avg_price='||v_avgprice);
exit;
end if;
end loop;
end;
/
--begin price promote process:
--we always promote the most expensive proct, untill the avg price infinite approximation the red line: 50 RMB
declare
v_avgprice number;
begin
for idx in 1..1000 loop
select avg(d.mprice_new) into v_avgprice from(
select
case
when c.rn<=idx then c.mprice*1.05
else c.mprice
end as mprice_new
,c.* from (
select rownum as rn,b.* from (
select a.* from proct a order by a.mprice desc
) b
) c
) d;
if(v_avgprice>50)then
insert into proct_new
select
c.cproctcode
,case
when c.rn<idx then c.mprice*1.05
else c.mprice
end as mprice_new
from(
select rownum as rn,b.* from (
select a.* from proct a order by a.mprice desc
) b
) c
;
dbms_output.put_line('promote price done');
dbms_output.put_line('last promoted proct''s idx(order by price desc) is:'||(idx-1));
select avg(mprice) into v_avgprice from proct;
dbms_output.put_line('avg price before promote is:'||v_avgprice);
select avg(mprice_new) into v_avgprice from proct_new;
dbms_output.put_line('avg price after promote is:'||v_avgprice);
exit;
end if;
end loop;
commit;
end;
/
5. where r.status=0、 where t.rn = 1什麼意思這里的t、r、rn指什麼
t 表示子查詢表 r表示 tblmo2macbt 表 r n 表示row_number() over(partition by r.sn order by r.mdate desc,r.mtime desc) 欄位 都是別名
6. oracle查詢取數據 如圖，我要取每個id 對應的rn='2'和rn最大的兩條數據，sql語句怎麼寫
selectt.*
from表名tjoin
(selectID,Max(RN)from表名groupbyID)t1
ont.ID=t1.IDandt.RN=t1.RN
union
select*from表名whereRN=2