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