CREATE TABLE tb(pz NVARCHAR(10),jzyf DATE)INSERT INTO tb SELECT '毛笔','2014-05-03' UNION ALL SELECT '毛笔','2014-12-08' UNION ALL SELECT '毛笔','2014-07-26' UNION ALLSELECT '钢笔','2014-08-12' UNION ALL SELECT '钢笔','2014-12-23' UNION ALL SELECT '钢笔','2015-02-09' UNION ALL SELECT '钢笔','2014-10-30' UNION ALL SELECT '钢笔','2014-11-08' UNION ALLSELECT '铅笔','2014-04-13' UNION ALL SELECT '铅笔','2014-05-18' UNION ALL SELECT '铅笔','2014-08-26' UNION ALL SELECT '铅笔','2014-10-18' UNION ALL SELECT '铅笔','2014-12-08' SELECT pz,jzyf,NO FROM ( SELECT t.*, CASE WHEN @partition_by = t.pz THEN @rownum := @rownum + 1 ELSE @rownum :=1 END AS NO, @partition_by := pz AS partition_by FROM tb t INNER JOIN (SELECT @rownum :=0) r ON 1=1 INNER JOIN (SELECT @partition_by :='') p ON 1=1 ORDER BY t.pz,t.jzyf )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 pz,jzfy, (select count(*) from 怎么按照品种分组 where pz=t.pz and jzfy<=t.jzfy) as no from 怎么按照品种分组 t
CREATE TABLE tb(pz NVARCHAR(10),jzyf DATE)INSERT INTO tb
SELECT '毛笔','2014-05-03' UNION ALL
SELECT '毛笔','2014-12-08' UNION ALL
SELECT '毛笔','2014-07-26' UNION ALLSELECT '钢笔','2014-08-12' UNION ALL
SELECT '钢笔','2014-12-23' UNION ALL
SELECT '钢笔','2015-02-09' UNION ALL
SELECT '钢笔','2014-10-30' UNION ALL
SELECT '钢笔','2014-11-08' UNION ALLSELECT '铅笔','2014-04-13' UNION ALL
SELECT '铅笔','2014-05-18' UNION ALL
SELECT '铅笔','2014-08-26' UNION ALL
SELECT '铅笔','2014-10-18' UNION ALL
SELECT '铅笔','2014-12-08'
SELECT pz,jzyf,NO
FROM
(
SELECT t.*,
CASE WHEN @partition_by = t.pz THEN @rownum := @rownum + 1
ELSE @rownum :=1
END AS NO,
@partition_by := pz AS partition_by
FROM tb t
INNER JOIN (SELECT @rownum :=0) r
ON 1=1
INNER JOIN (SELECT @partition_by :='') p
ON 1=1
ORDER BY t.pz,t.jzyf
)tb
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 count(*) from 怎么按照品种分组 where pz=t.pz and jzfy<=t.jzfy) as no
from 怎么按照品种分组 t