derby一些很难得的sql语句

最新推荐文章于 2023-03-24 16:27:24 发布

hnlixm

最新推荐文章于 2023-03-24 16:27:24 发布

阅读量279

点赞数

CC 4.0 BY-SA版权

文章标签： Derby SQL

本文链接：https://blog.youkuaiyun.com/hnlixm/article/details/83808358

本文介绍了一种使用SQL进行分页查询的方法，并展示了如何通过插入操作实现数据库表的拷贝。具体包括利用ROW_NUMBER()进行行编号以便分页查询，以及通过联表查询获取两表间的差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

分页查询：
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)