如何根据 SQL 中另一列的值更新列?

Question

基本上我有这样的产品表：

date       price
---------  -----
02-SEP-14     50  
03-SEP-14     60  
04-SEP-14     60  
05-SEP-14     60  
07-SEP-14     71  
08-SEP-14     45  
09-SEP-14     45  
10-SEP-14     24  
11-SEP-14     60

我需要更新此表单
中的表格

date       price   id
---------  -----   --
02-SEP-14     50    1
03-SEP-14     60    2
04-SEP-14     60    2
05-SEP-14     60    2
07-SEP-14     71    3
08-SEP-14     45    4
09-SEP-14     45    4
10-SEP-14     24    5
11-SEP-14     60    6

我尝试过的：

CREATE SEQUENCE user_id_seq
  START WITH 1
  INCREMENT BY 1
  CACHE 20;
ALTER TABLE Product
  ADD (ID number);
UPDATE Product SET ID = user_id_seq.nextval;

这是以通常的方式更新ID，如1,2,3,4,5。

我不知道如何使用基本的SQL命令来做到这一点。请建议我该怎么做。提前谢谢你。

Answer 1

以下是从基础数据创建视图的一种方法。我假设您有多个产品(由产品 ID 标识(，并且价格日期不一定是连续的。对于每个产品 ID，序列是分开的。 (此外，product应该是不同表的名称 - 其中产品 ID 是主键，并且您还有其他信息，例如产品名称、类别等。您帖子中的表更恰当地称为 price_history .(

alter session set nls_date_format='dd-MON-rr';
create table product ( prod_id number, dt date, price number );
insert into product ( prod_id, dt, price )
  select 101, '02-SEP-14', 50 from dual union all 
  select 101, '03-SEP-14', 60 from dual union all  
  select 101, '04-SEP-14', 60 from dual union all 
  select 101, '05-SEP-14', 60 from dual union all 
  select 101, '07-SEP-14', 71 from dual union all  
  select 101, '08-SEP-14', 45 from dual union all 
  select 101, '09-SEP-14', 45 from dual union all 
  select 101, '10-SEP-14', 24 from dual union all 
  select 101, '11-SEP-14', 60 from dual union all
  select 102, '02-SEP-14', 45 from dual union all
  select 102, '04-SEP-14', 45 from dual union all
  select 102, '05-SEP-14', 60 from dual union all
  select 102, '06-SEP-14', 50 from dual union all
  select 102, '09-SEP-14', 60 from dual
;
commit;
create view product_vw ( prod_id, dt, price, seq ) as
  select prod_id, dt, price, 
         count(flag) over (partition by prod_id order by dt)
  from   ( select prod_id, dt, price,
                  case when price = lag(price) over (partition by prod_id order by dt)
                       then null else 1 end as flag
           from   product
         )
;

现在检查视图的外观：

select * from product_vw;
PROD_ID DT                       PRICE        SEQ
------- ------------------- ---------- ----------
    101 02/09/0014 00:00:00         50          1
    101 03/09/0014 00:00:00         60          2
    101 04/09/0014 00:00:00         60          2
    101 05/09/0014 00:00:00         60          2
    101 07/09/0014 00:00:00         71          3
    101 08/09/0014 00:00:00         45          4
    101 09/09/0014 00:00:00         45          4
    101 10/09/0014 00:00:00         24          5
    101 11/09/0014 00:00:00         60          6
    102 02/09/0014 00:00:00         45          1
    102 04/09/0014 00:00:00         45          1
    102 05/09/0014 00:00:00         60          2
    102 06/09/0014 00:00:00         50          3
    102 09/09/0014 00:00:00         60          4

Answer 2

注意：这回答了最初提出的问题。 OP 更改了数据。

如果数据不是太大，则可以使用相关的子查询：

update product p
    set id = (select count(distinct p2.price)
              from product p2
              where p2.date <= p.date
             );

如果数据较大，则merge更合适。

Answer 3

WITH cts AS  
(
SELECT row_number() over (partition by price order by price ) as id
,date
,price  
FROM Product 
)
UPDATE p
set p.id = cts.id 
from product p join cts on cts.id = p.id

Answer 4

这是您尝试的最好方法。 没有另一种简单的方法可以使用简单的语句来做到这一点