mysql中组内排序编号的一种实现方法。

编程环境：win7+navicat自带的存储过程编辑平台

即根据commodity_id和bs_flag为优先级来进行排序、。之后统计每组的持仓总量、组内名次和组内成员数、客户编码的连接。

组内成员数用count+group by实现
组内持仓总量用sum+group by实现
组内客户号码连接用group concat + group by实现
组内排序的名次用了用户变量来实现，这也是从网络上学习来的。
具体而言要用这个。后一半实现表格的每一行和上一行对比的功能，个人觉得相当巧妙。

上午想试着用下游标的。。然而并没有使用成功，还需要多加练习、
被注释掉的都是游标的内容了、。

CREATE TEMPORARY TABLE result SELECT *,IF(@bsflag=f.bs_flag and @commodityid = f.commodity_id,@rank:=@rank + 1,@rank:=1) as 组内名次 ,@bsflag:=f.bs_flag,@commodityid := f.commodity_id FROM f;#添加排名名次结果

BEGIN
    #Routine body goes here...
    #Routine body goes here...
/*
DECLARE CurDone INT DEFAULT 0; # 定义是否到行末
DECLARE Cur_firm_id INT DEFAULT 0;
DECLARE Cur_customer_id INT DEFAULT 0;
DECLARE Cur_commodity_id INT DEFAULT 0;
DECLARE Cur_Bs_flag INT DEFAULT 0;
DECLARE Cur_Balance INT DEFAULT 0;
DECLARE Cur_bse INT  DEFAULT 0;
DECLARE Cur_come INT DEFAULT 0;
DECLARE 当前组内成员 INT DEFAULT 0;
DECLARE 当前持仓总量 INT DEFAULT 0;
DECLARE 当前组内名次 INT DEFAULT 0;

DECLARE CurDone1 INT DEFAULT 0; # 定义是否到行末
DECLARE Cur_firm_id1 INT DEFAULT 0;
DECLARE Cur_customer_id1 INT DEFAULT 0;
DECLARE Cur_commodity_id1 INT DEFAULT 0;
DECLARE Cur_Bs_flag1 INT DEFAULT 0;
DECLARE Cur_Balance1 INT DEFAULT 0;
DECLARE Cur_bse1 INT  DEFAULT 0;
DECLARE Cur_come1 INT DEFAULT 0;
DECLARE 当前组内成员1 INT DEFAULT 0;
DECLARE 当前持仓总量1 INT DEFAULT 0;
DECLARE 当前组内名次1 INT DEFAULT 0;

DECLARE rent INT DEFAULT 1;

#DECLARE linerow CURSOR FOR SELECT * FROM f; #定义游标

#DECLARE CONTINUE HANDLER FOR NOT FOUND SET CurDone=1;#判断结束标记
*/
DROP TABLE IF EXISTS d; #删除临时表d ，之前应该加入判断语句判断d是否存在。
DROP TABLE IF EXISTS e;
DROP TABLE IF EXISTS f;
DROP TABLE IF EXISTS result;

CREATE TEMPORARY TABLE d SELECT firm_id,customer_id,commodity_id,bs_flag,balance FROM temp ORDER BY commodity_id ,bs_flag ,balance DESC;  #创建临时表d

CREATE TEMPORARY TABLE e SELECT commodity_id AS come,bs_flag AS bse,COUNT(*) AS 组内成员数,SUM(balance) AS 组内持仓总量 ,GROUP_CONCAT(customer_id) AS 组内客户号码的连接 FROM d GROUP BY commodity_id,bs_flag;

CREATE TEMPORARY TABLE f SELECT * FROM d LEFT JOIN e ON commodity_id = come AND bs_flag = bse;

SET @rank=1;SET @bsflag=NULL;SET @commodityid=NULL;

CREATE TEMPORARY TABLE result SELECT *,IF(@bsflag=f.bs_flag and @commodityid = f.commodity_id,@rank:=@rank + 1,@rank:=1) as 组内名次 ,@bsflag:=f.bs_flag,@commodityid := f.commodity_id FROM f;#添加排名名次结果

/*
OPEN linerow;
FETCH linerow INTO Cur_firm_id1,Cur_customer_id1,Cur_commodity_id1,Cur_Bs_flag1,Cur_balance1,Cur_come1,Cur_bse1,当前组内成员1,当前持仓总量1,当前组内名次1;
WHILE  CurDone=0 DO
FETCH linerow INTO Cur_firm_id,Cur_customer_id,Cur_commodity_id,Cur_Bs_flag,Cur_balance,Cur_come,Cur_bse ,当前组内成员,当前持仓总量,当前组内名次;##应当考虑是不是空表，
#   IF Cur_commodity_id<>Next_commodity_id OR Cur_Bs_flag<>Next_Bs_flag SET num_in_team=1;
        IF Cur_commodity_id<>Cur_commodity_id1 OR Cur_Bs_flag<>Cur_Bs_flag1 
            THEN
            SET rent=1;
    IF Cur_commodity_id=Cur_commodity_id1 AND Cur_Bs_flag<>Cur_Bs_flag1 
            THEN
            SET rent=rent+1;
        UPDATE f SET bse=rent WHERE Cur_commodity_id=commodity_id AND Cur_Bs_flag=bs_flag;
END WHILE;
*/
#CLOSE linerow;

SELECT firm_id , customer_id , commodity_id , bs_flag , 组内成员数 , 组内持仓总量 , 组内名次 as 组内持仓数量排序的名次 , 组内客户号码的连接 FROM result;

END