何隆昌 mysql自动生成序号,分组生成序号

mysql自动生成普通序号

SELECT (@i:= @i+1) AS '序号', account FROM base_user,
	(SELECT @i:=0) AS i

连表的(SELECT @i:=0) AS i要放在where的前面，

SELECT
(@i:= @i+1) AS '序号',
 t1.id id1,
 t1.WayName name1,
 t2.id id2,
 t2.WayName name2,
 t3.id id3,
 t3.WayName name3,
 t4.id id4,
 t4.WayName name4
FROM
 all_tree t4
 LEFT JOIN all_tree t3 ON t4.WayPid = t3.id
LEFT JOIN all_tree t2 ON t3.WayPid = t2.id
LEFT JOIN all_tree t1 ON t2.WayPid = t1.id ,(SELECT @i:=0) AS i 
WHERE t4.type='4' 

mysql按规律自动生成序号

怎么按照品种分组，自动生成no这个字段

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `time` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

insert  into `test`(`id`,`name`,`time`) values 
(2,'毛笔','2014-05-03'),
(3,'毛笔','2014-12-08'),
(4,'毛笔','2014-07-26'),
(5,'钢笔','2014-08-12'),
(6,'钢笔','2014-12-23'),
(7,'钢笔','2015-02-09'),
(8,'钢笔','2014-10-30'),
(9,'钢笔','2014-11-08'),
(10,'铅笔','2014-04-13'),
(11,'铅笔','2014-05-18'),
(12,'铅笔','2014-08-26'),
(13,'铅笔','2014-10-18'),
(14,'铅笔','2014-12-08');

sql

SELECT t.*,
       CASE WHEN @partition_by = t.name THEN @rownum := @rownum + 1 
            ELSE @rownum :=1
	   END AS NO,
       @partition_by := NAME AS partition_by
FROM test t
INNER JOIN (SELECT @rownum :=0) r
        ON 1=1
INNER JOIN (SELECT @partition_by :='') p
        ON 1=1
ORDER BY t.name,t.time

结果

最后把多余的字段去掉

SELECT NAME,TIME,NO
FROM 
(
///
SELECT t.*,
       CASE WHEN @partition_by = t.name THEN @rownum := @rownum + 1 
            ELSE @rownum :=1
	   END AS NO,
       @partition_by := NAME AS partition_by
FROM test t
INNER JOIN (SELECT @rownum :=0) r
        ON 1=1
INNER JOIN (SELECT @partition_by :='') p
        ON 1=1
ORDER BY t.name,t.time

/
)tb

第二种：没测试过（不推荐）

select pz, jzyf, rank
from (
select tt_tmp.pz, tt_tmp.jzyf, @rownum:=@rownum+1,if(@pjzyf=tt_tmp.jzyf, @rank:=@rank+1, @rank:=1) as rank, @pjzyf:= tt_tmp.pjzyf
from 
(select pz, jzyf from pz, jzyf
from tt
ORDER BY pz asc,jzyf desc) tt_tmp,
(select @rownum := 0, @pjzyf := null, @rank:=0) a ) result;

复杂的树形数据分组生成序号（有注释的地方要改，其它不用动）

SELECT t.*,
       CASE WHEN @partition_by = t.name1 THEN @rownum := @rownum + 1 //name1要分组生成序号的字段
            ELSE @rownum :=1
	   END AS NO,
       @partition_by := name1 AS partition_by   //name1要分组生成序号的字段
FROM 
//中间这个就是生成树形数据的sql,放进来就行了，根据哪个字段分组，上面就写哪个字段
(SELECT
 t1.id id1,
 t1.WayName name1,
 t2.id id2,
 t2.WayName name2,
 t3.id id3,
 t3.WayName name3,
 t4.id id4,
 t4.WayName name4
FROM
 all_tree t4
 LEFT JOIN all_tree t3 ON t4.WayPid = t3.id
LEFT JOIN all_tree t2 ON t3.WayPid = t2.id
LEFT JOIN all_tree t1 ON t2.WayPid = t1.id
WHERE t4.type='4') t

下面的不用动
INNER JOIN (SELECT @rownum :=0) r
        ON 1=1
INNER JOIN (SELECT @partition_by :='') p
        ON 1=1