准备sql

CREATE TABLE products (prod_id INT NOT NULL AUTO_INCREMENT, prod_name VARCHAR(20) NOT NULL, prod_cost FLOAT NOT NULL DEFAULT 0.0, prod_price FLOAT NOT NULL DEFAULT 0.0, PRIMARY KEY(prod_id));

INSERT INTO products (prod_name, prod_cost, prod_price) VALUES ('Basic Widget',5.95,8.35),('Micro Widget',0.95,1.35),('Mega Widget',99.95,140.00);
CREATE TABLE sales_order (
     id INT NOT NULL AUTO_INCREMENT, 
      prod_id INT NOT NULL ,customer_id INT NOT NULL, prod_price FLOAT NOT NULL DEFAULT 0.0, PRIMARY KEY(id));

insert into sales_order(prod_id, customer_id, prod_price) values (1, 1, 10.0), (1, 2, 10.5), (1, 3, 10.5), (2, 1, 1.5), (2, 2, 4.4), (2, 3, 2.5)

增加一个顺序列

select prod_id, prod_name, prod_cost, (@caseRowIdx := @caseRowIdx +1) as caseRowIdx
from 
(select *  from products) as p,
(select @caseRowIdx :=0) as b

增加一个顺序列并按照一个字段重置

select prod_id, customer_id, prod_price, 
			(@customerRowIdx := (case when @customerId = customer_id then @customerRowIdx +1
               else 1
               end)) as customerRowIdx,
               (@customerId := customer_id) as tmpCustomer                
from               
(select prod_id, customer_id, prod_price from sales_order order by customer_id) as a, 
(select @customerRowIdx :=0, @customerId := '') as b