mysql - 是否有任何 mysql/Oracle 函数可以提供增量编号。基于另一列相似值的一列？

Question

我有兴趣知道是否有任何 mysql/Oracle 函数可以提供增量编号。基于另一列相似值的一列？

就像下面的代码一样，我有 order_primary 列，其中包含订单号。因此，基于此我们可以确定有多少产品属于特定订单。 count 还用于存储这些值，例如 1、2、3 等。但我面临着计数值不断增加的问题...

我的代码-:

$query_product = "SELECT name, id,qty_ordered,price,row_total,base_subtotal, 
base_shipping_amount,base_grand_total,order_primary,message
FROM  sales_order WHERE `prod_Flag`=0 ";

$result_query_product = mysql_query($query_product);
        $count = 0;
            while($row = mysql_fetch_array($result_query_product))
        {
            $count++;
            $name = ($row["name"]);
            $message1 = ($row["message"]);
                 $result_str_product .= "('". mysql_real_escape_string($name) . "',". "'" . $row["sku"] . "'," . "'" . $row["qty_ordered"] . "',". "'" . $row["price"] . "'," . "'" . $row["row_total"] . "'," . "'" . $row["base_subtotal"]. "'," . "'" . $row["base_shipping_amount"] . "'," . "'" . $row["base_grand_total"] ."',". $row["order_primary"].",". $count.",". "'".mysql_real_escape_string($message1)."'".", NOW()),";      
        }


        $query_prod_insert = "INSERT into sales_product(name, sku, qty_ordered, price, row_total, base_subtotal,    base_shipping_amount,base_grand_total,prod_foreign,count,message,product_creation_date) VALUES ".$result_str_product;
    $final_query = substr_replace($query_prod_insert,";",-1);
    $result_query_product_outbound = mysql_query($final_query);

所以我的运算符(operator)是-:

('shirt','st','2.0000','75','150','150','20','170',29,1,NOW()),
    ('tie'  ,'te','2.0000','50','100','100','10','110',29,2,NOW()),
    ('tie'  ,'te','2.0000','50','100','100','10','110',29,3,NOW()),                   
('socks','sk','5.0000','20','100','100','05','105',30,4,NOW()); 
    ('jackt','jt','3.0000','40','120','120','15','135',30,5,NOW()); 

但我想要这样的o/p-:

 ('shirt','st','2.0000','75','150','150','20','170',29,**1**,NOW()),
    ('tie'  ,'te','2.0000','50','100','100','10','110',29,**2**,NOW()),
    ('tie'  ,'te','2.0000','50','100','100','10','110',29,**3**,NOW()),

('socks','sk','5.0000','20','100','100','05','105',30,**1**,NOW()); 
    ('jackt','jt','3.0000','40','120','120','15','135',30,**2**,NOW());

那么有没有 mysql/Oracle 函数可以提供增量编号。基于相似值的另一列的一列，即在我的情况下，对于相同的订单号。值如 29，计数值应为 1,2,3 & 对于相同的订单号。 30，计数值应为1,2...

有什么功能或者如何做同样的事情吗？

Answer 1

对于 Oracle 来说，这非常简单:

SELECT order_no, 
       row_number() over (partition by order_no order by order_primary) as rn
FROM sales_product

注意:我猜测列名是因为它们隐藏在 PHP(?) 代码中的某个位置。请根据您的表结构进行调整。对于将来的帖子，您还应该在问题中包含相应的 CREATE TABLE 语句。