56,687
社区成员
发帖
与我相关
我的任务
分享
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'
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