derby一些很难得的sql语句

bjmike

分页查询：
select p.* from(
select ROW_NUMBER() OVER() AS rownum ,t.* from (
select * from t_amountIn where day='20110107' order by t5percent desc
) t )p
where p.rownum<31



select a.*,b.price as curprice,b.price-a.price as dif  from(
select p.* from(
select ROW_NUMBER() OVER() AS rownum ,t.* from (
select * from t_amountIn where day='20110106' order by t5percent desc
) t )p
where p.rownum<31
) a,
(
select * from t_amountIn where day='20110107'
)b
where a.code=b.code


数据库表拷贝：类似select into功能
insert into test(day,code,name)
select day,code,name from t_amountIn







insert into t_amountIn_dif
(select a.*,b.price as curprice,b.price-a.price as dif  from(
select p.* from(
select ROW_NUMBER() OVER() AS rownum ,t.* from (
select * from t_amountIn where day='20110106' order by t5percent desc
) t )p
) a,
(
select * from t_amountIn where day='20110107'
)b
where a.code=b.code)