SQL实现分组排序编号（rownumber+over的替代办法）

今天看到论坛有人提问：按规律自动生成序号

要实现的效果如下：

1、建表语句、插入数据：

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 ALL

SELECT '钢笔','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 ALL

SELECT '铅笔','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'

2、实现代码如下：

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,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,@partition_by :='') r
        ON 1=1
ORDER BY t.pz,t.jzyf
)tb