【大话IT】orderby的union all语句如何简化?

显示全部楼层 · 2014-10-1 06:00:14

现有一表，记录了合同信息，需要按照到期时间，取各种类最近的三条记录出来显示
测试代码如下:create table t_test( id number primary key not null,--主键 contract_type varchar2(10), --合同类型 contract_name varchar2(20), --合同名称 expire_date varchar2(10) --到期时间);insert into t_test values(1,'A001','A类合同','2015-06-12');insert into t_test values(2,'A001','A类合同','2015-06-11');insert into t_test values(3,'A001','A类合同','2015-06-10');insert into t_test values(4,'A001','A类合同','2015-06-09');insert into t_test values(5,'B001','B类合同','2015-06-12');insert into t_test values(6,'B001','B类合同','2015-06-11');insert into t_test values(7,'B001','B类合同','2015-06-10');insert into t_test values(8,'B001','B类合同','2015-06-09');insert into t_test values(9,'C001','C类合同','2015-06-12');insert into t_test values(10,'C001','C类合同','2015-06-11');insert into t_test values(11,'C001','C类合同','2015-06-10');insert into t_test values(12,'C001','C类合同','2015-06-09');insert into t_test values(13,'C001','C类合同','2015-06-08');复制代码我实现的SQL如下:select contract_name,expire_datefrom (select t.contract_name,t.expire_date from t_test twhere t.contract_type = 'A001'and rownum 复制代码
但这样写的SQL我觉得很臃肿，请问各位大牛，有简化的写法么？

千问 · 2014-10-1 06:00:14

分析函数：
select contract_name,expire_date
from (
select contract_name,expire_date,row_number() over (partition by contract_name order by expire_date desc) rn
from t_test
)
where rn<=3
order by 1,2 desc;

千问 · 2014-10-1 06:00:14

bfc99 发表于 2015-6-12 11:19
分析函数：
select contract_name,expire_date
from (
厉害，大腿我已经抱住了

十分感谢

千问 · 2014-10-1 06:00:14

本帖最后由 bid01 于 2015-6-12 12:12 编辑
select * from t_test where ( EXPIRE_DATE,CONTRACT_NAME) in(select max(EXPIRE_DATE),CONTRACT_NAME from t_test group by CONTRACT_NAME);

千问 · 2014-10-1 06:00:14

bfc99 发表于 2015-6-12 11:19
分析函数：
select contract_name,expire_date
from (
按原来sql是先去3行，再执行order by哟

千问 · 2014-10-1 06:00:14

qiu_hong_yun 发表于 2015-6-12 16:49
按原来sql是先去3行，再执行order by哟
楼主的写法实际上不能保证实现”需要按照到期时间，取各种类最近的三条记录出来显示“的目的。

千问 · 2014-10-1 06:00:14

bfc99 发表于 2015-6-12 16:55
楼主的写法实际上不能保证实现”需要按照到期时间，取各种类最近的三条记录出来显示“的目的。
应该是楼主没弄对

千问 · 2014-10-1 06:00:14

要取前几行在order by的同一层用rownum是不行的

千问 · 2014-10-1 06:00:14

If we must use union all, for example, in some DBMS without analytic function, it should be:
select contract_name,expire_date
from
(
select t.contract_name,t.expire_date from t_test t
where t.contract_type = 'A001'
--and rownum <=3
order by t.expire_date desc
)
where rownum <=3
union all
select contract_name,expire_date
from
(
select t.contract_name,t.expire_date from t_test t
where t.contract_type = 'B001'
--and rownum <=3
order by t.expire_date desc
)
where rownum <= 3
union all
select contract_name,expire_date
from
(
select t.contract_name,t.expire_date from t_test t
where t.contract_type = 'C001'
--and rownum <=3
order by t.expire_date desc)
where rownum <= 3

千问 · 2014-10-1 06:00:14

YuBinTAMU 发表于 2015-6-12 21:25
If we must use union all, for example, in some DBMS without analytic function, it should be:
sele ...
一些数据库没有rownum就用top n 或 limit