主页 / dba / 问题 / 2013
Accepted
bernd_k
Asked: 2011-04-01 05:03:19 +0800 CST

如何使用外部表别名将 Top 1 子查询转换为 Oracle？

  • 772

我有以下 SQL Server 查询

select
    (select top 1 b2 from  BB b where b.b1 = a.a1 order by b2) calc,
    a1,
    a2
from AA a
where a2 = 2;

我可以使用分析函数重写

select
    (select b2 from 
    (select 
        row_number() over (order by b2) lfd, 
     b2 from  BB b where b.b1 = a.a1
    ) as t where lfd = 1
    ) calc,
    a1,
    a2
from AA a
where a2 = 2;

但是当我将其转换为 oracle

create table AA ( a1 NUMBER(10), a2 NUMBER(10) );
insert into AA values ( 1, 1);
insert into AA values ( 1, 2);
insert into AA values ( 1, 3);
insert into AA values ( 2, 2);

create table BB ( b1 NUMBER(10), b2 NUMBER(10) );
insert into BB values ( 1, 1);
insert into BB values ( 2, 4);
insert into BB values ( 2, 5);


select * from AA;
select * from BB;


select
    (select b2 from 
    (select 
        row_number() over (order by b2) lfd, 
     b2 from  BB b where b.b1 = a.a1
    )  where lfd = 1
    ) calc,
    a1,
    a2
from AA a
where a2 = 2;

我收到以下错误

Error at line 5
ORA-00904: "A"."A1": invalid column name
oracle subquery

3 个回答

  1. Best Answer

    您将首先在 Oracle 中执行连接：

    SELECT a1, a2, b2
  FROM (SELECT a1, a2, b2, 
               row_number() over(PARTITION BY a.a1 ORDER BY b.b2) lfd
           FROM AA a
           LEFT JOIN BB b ON b.b1 = a.a1
          WHERE a2 = 2)
 WHERE lfd = 1

    您的查询的问题是，目前 Oracle 中的子查询无法从父查询中访问超过两级以上的值。

    您还可以使用包含内部 SELECT 的 PL/SQL 函数。

    • 6

  2. 如果这些是您正在寻找的结果：

    CODE QTY  FRUIT_PRICE  FRUIT_NAME  DRINK_PRICE   DRINK_NAME  
---- ---- ------------ ----------- ------------- ------------
A    1    2.4          apple       5.4           aperol      
B    1    1.3          banana      4.3           bear        
C    1

    你可以用这个得到它：

    SELECT o.code, o.qty, f.fruit_price, f.fruit_name, d.drink_price, d.drink_name 
FROM want_to_eat o
LEFT JOIN (
   SELECT Row_Number() OVER (PARTITION BY Fruit_Code ORDER BY f.datetime desc) 
        FruitRow
      , fruit_price, fruit_name, fruit_code
   FROM Fruit f
   ) f ON f.fruit_code = o.code AND f.FruitRow = 1
LEFT JOIN (
   SELECT Row_Number() OVER (PARTITION BY Drink_Code ORDER BY d.datetime desc) 
        DrinkRow
      , Drink_price, Drink_name, Drink_code
   FROM Drink d
   ) d ON d.Drink_code = o.code AND d.DrinkRow = 1
WHERE qty = 1;
    • 2

  3. 在这里，我将展示我在使用 Vincent Malgrat 的方法时得到的一些结果。

    首先我了解到，当基于不同的表或顺序使用超过 1 个这样的 top 1 子查询时，我必须使用RANK()函数而不是ROW_NUMBER()函数。

    其次，在使用 rank() 时存在关系问题。SQL Server 的 top 1 任意选择 rank = 1 的行之一，而使用 rank() 可以返回多于 1 行。

    我认为在这些情况下使用 SQL Server top 1 是不好的设计。为了使设计正确，必须找到一些唯一约束（例如唯一索引），以防止这种歧义。

    如果您想自己尝试，这里有一个 SQL Server 示例。

    比较下面最后两个 select 语句的执行计划表明，Vincent Malgrat 的方法比 top 1 解决方案要好。

    SET NOCOUNT ON

begin try drop table fruit       end try begin catch end catch; 
begin try drop table want_to_eat end try begin catch end catch; 
begin try drop table drink       end try begin catch end catch; 

create table fruit (
 fruit_code char(1),
 fruit_name varchar(20),
 date   datetime,
 fruit_price  money
);
go

create table drink  (
 drink_code char(1),
 drink_name varchar(20),
 date   datetime,
 drink_price  money
);
go
create table want_to_eat (
 code char(1),
 qty  integer,
);
go
insert into want_to_eat values ( 'A', 1);
insert into want_to_eat values ( 'B', 2);
insert into want_to_eat values ( 'B', 1);
insert into want_to_eat values ( 'C', 1);


insert into fruit values ( 'A', 'apple',  '20100101', '2.20');
insert into fruit values ( 'A', 'apple',  '20110101', '2.40');
insert into fruit values ( 'B', 'banana', '20100101', '1.40');
insert into fruit values ( 'B', 'banana', '20110101', '1.30');
insert into fruit values ( 'B', 'banana', '20110101', '1.35');

insert into drink values ( 'A', 'aperol',  '20100101', '5.20');
insert into drink values ( 'A', 'aperol',  '20110101', '5.40');
insert into drink values ( 'B', 'bear',    '20100101', '4.40');
insert into drink values ( 'B', 'bear',    '20110101', '4.30');

create unique index iu_drink on drink(drink_code, date);
-- create unique index iu_fruit on fruit(fruit_code, date); -- Error


Select top 1 fruit_price from fruit where fruit_code = 'A' order by date desc;
Select top 1 fruit_price from fruit where fruit_code = 'B' order by date desc;
Select top 1 fruit_price from fruit where fruit_code = 'C' order by date desc;

SELECT
qty,
(Select top 1 fruit_price from fruit where fruit_code = code order by date desc) fruit_price,
(Select top 1 fruit_name  from fruit where fruit_code = code order by date desc) fruit_name,
(Select top 1 drink_price from drink where drink_code = code order by date desc) drink_price,
(Select top 1 drink_name  from drink where drink_code = code order by date desc) fruit_name
FROM want_to_eat
WHERE qty = 1;

SELECT qty, fruit_price, fruit_name , drink_price,drink_name from (
-- SELECT * FROM (
SELECT
RANK() OVER (PARTITION BY o.CODE ORDER BY f.date desc) f_lfd,
RANK() OVER (PARTITION BY o.CODE ORDER BY d.date desc) d_lfd,
f.date f_date,
code,
qty, 
fruit_price,
fruit_name,
drink_price,
drink_name
from want_to_eat o
left join fruit f on o.code = fruit_code
left join drink d on o.code = drink_code
WHERE qty = 1

) t 
where f_lfd = 1
and d_lfd = 1;

    对 Leigh Riffel 的回答：

    在以下 id 为 1 和 2 的行中，rank() 和 dense_rank 均为 1。

    create table rank_test (
id int,
grp  int,
val varchar(10)
);

insert into rank_test values (1, 1, 'a');
insert into rank_test values (2, 1, 'a');
insert into rank_test values (3, 1, 'b');
insert into rank_test values (4, 2, 'b');

select * from rank_test;

select r.*,
RANK() OVER (PARTITION BY grp ORDER BY val) rank,
DENSE_RANK() OVER (PARTITION BY grp ORDER BY val) d_rank
from rank_test r
order by id;

    结果：

    id          grp         val        rank                 d_rank
----------- ----------- ---------- -------------------- --------------------
1           1           a          1                    1
2           1           a          1                    1
3           1           b          3                    2
4           2           b          1                    1

    对 Leigh Riffel 的新回答：

    这是一个示例，我无法使用您的模式进行转换。我有一张带有文章价格的表格，还有一张带有价格变化历史的表格。当价格发生变化时，一些权威想知道旧价格。

    create table artikel (id int, price int);
create table price_history (id int,price int,v_date datetime);

insert into artikel values (1, 11), (2, 22);
insert into price_history values (1, 11, '20110101'), (2, 20,'20110101');
insert into price_history values (1, 11, '20110201'), (2, 20,'20110201'); -- the true table has more columns, which values might change while price stays the same
insert into price_history values (1, 11, '20110301'), (2, 22,'20110301');

Select id,
       (SELECT TOP 1 price_history.price FROM price_history WHERE price_history.id = artikel.id AND artikel.price <> price_history.price ORDER by v_date DESC ) priceOld
from artikel 
ORDER BY id;

Select artikel.id, y.price priceOld
from    artikel
LEFT JOIN (
   SELECT Row_Number() OVER (PARTITION BY id ORDER BY v_date desc) 
        yRow
      , id, price
   FROM price_history
   ) y ON y.id = artikel.id AND y.yRow = 1 and artikel.price <> y.price
ORDER BY id;

    我想得到

    id          priceOld
----------- -----------
1           NULL
2           20

    但第二个给出

    id          priceOld
----------- -----------
1           NULL
2           NULL

    文森特马尔格拉特的方法产生了正确的结果

    Select id, priceOld from (
Select 
    Row_Number() OVER (PARTITION BY a.id ORDER BY v_date desc) vRow, 
    a.id, h.price priceOld
from artikel a
LEFT JOIN
   price_history h ON a.id = h.id and a.price <> h.price
) t 
where vRow = 1   
ORDER BY id;
    • 1

相关问题