根据另一列值递增列索引 [英] Incrementing column index based on another column value

问题描述

假设我有一个包含2列的表：一个具有不同的日期（DATE），另一个具有浮点值（PRICE），包括空值。我想要实现的是添加一个新列（NON_ZERO_ID），该列基本上具有一个值，并且仅在PRICE不为空时才递增。这是所需的输出：

日期|价格| NON_ZERO_ID

1/2/16 | 0.12 | 1

1/3/16 | 45.00 | 2

1/4/16 | null | null

1/5/16 | 40.00 | 3 ...



我的尝试：



我尝试使用row_number（）over（order by ..）语法对行进行编号，但在所有情况下，NON_ZERO_ID都会自动递增，即使有是PRICE的空值。有什么建议？基本上，我希望能够使用预先填充的字符串来制作NON_ZERO_ID，即ID1，ID2等。

解决方案

首先，这听起来像是在尝试为没有间隙的某些行创建一系列序数。如果这是真的，应该注意，如果删除行，您将有间隙。如果价格更新为null或反之亦然会发生什么？



话虽如此，你可以使用select语句进行插入。类似

  INSERT   INTO  TableName 
（ DATE ，PRICE，NON_ZERO_ID）
  SELECT ：datevariable 
： pricevariable 
  CASE  
  WHEN ：pricevariable  IS   NULL  那么  NULL  
  ELSE （ SELECT  MAX（NON_ZERO_ID）+  1  
  FROM  TableName）
  END  
  FROM  dual; 

目前我无法检查语法，所以希望它没有包含mista KES ...

Lets say I have a table with 2 columns: one having distinct dates (DATE) and another having floating point values (PRICE), including nulls. What I want to achieve is add a new column (NON_ZERO_ID) which essentially has a value and is incremented only if PRICE is not null. Here's the desired output:
DATE | PRICE | NON_ZERO_ID
1/2/16| 0.12| 1
1/3/16|45.00| 2
1/4/16|null| null
1/5/16|40.00|3...

What I have tried:

I have tried using row_number() over (order by..) syntax for numbering the rows, but in all cases, the NON_ZERO_ID is getting auto-incremented even if there is a null value for PRICE. Any suggestions? Essentially, I want to be able to make the NON_ZERO_ID's with a string pre-pended as well, i.e., ID1, ID2, and so on.

解决方案

First of all, this sounds like you're trying to create a sequence of ordinals for certain rows without gaps. If that is true, it should be noted that you will have gaps if the rows are deleted. Also what happens if the price is updated to null or vice versa?

Having that said, you could do the insert using a select statement. Something like

INSERT INTO TableName
(DATE, PRICE, NON_ZERO_ID)
SELECT :datevariable
       :pricevariable
       CASE 
          WHEN :pricevariable IS NULL THEN NULL
          ELSE (SELECT MAX(NON_ZERO_ID) + 1
                FROM   TableName)
       END
FROM dual;

At the moment I don't have the possibility to check syntax so hopefully it doesn't contain mistakes...

这篇关于根据另一列值递增列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！