基本方法就是自连接
create table #A(id int, col int)
insert #A select 1,1
union all select 1,5
union all select 1,7
union all select 2,5select *, (select count(1) from #A t2 where t1.id=t2.id and t1.col>=t2.col) [index] from #A t1
/*
id col index
------------------------
1 1 1
1 5 2
1 7 3
2 5 1
*/
drop table #A
上面的是一个简单的例子。下面我写了代码,但是没有测试过。
select *, right(COL_2,6) + right('0000' +
-----------------重1开始------------------------
(select count(1) from
(
SELECT CardNo,LabelNo, CONVERT(VARCHAR(10), OpTime, 120) AS COL_1,
RIGHT(CONVERT(VARCHAR(8), OpTime, 112),6) +
(SELECT RIGHT(10000 + COUNT(DISTINCT CardNo) + 1, 4)
FROM #GoodsQB WHERE CardNo < T.CardNo) AS COL_2
FROM #GoodsQB T
ORDER BY CardNo
) t2 where t1.COL_1 = t2.COL_1 and t1.COL_2 >=t2.COL_2
)
-------------------------------------------
, 4) [你想要的答案]
from
(
SELECT CardNo,LabelNo, CONVERT(VARCHAR(10), OpTime, 120) AS COL_1,
RIGHT(CONVERT(VARCHAR(8), OpTime, 112),6) +
(SELECT RIGHT(10000 + COUNT(DISTINCT CardNo) + 1, 4)
FROM #GoodsQB WHERE CardNo < T.CardNo) AS COL_2
FROM #GoodsQB T
ORDER BY CardNo) t1
create table #A(id int, col int)
insert #A select 1,1
union all select 1,5
union all select 1,7
union all select 2,5select *, (select count(1) from #A t2 where t1.id=t2.id and t1.col>=t2.col) [index] from #A t1
/*
id col index
------------------------
1 1 1
1 5 2
1 7 3
2 5 1
*/
drop table #A
上面的是一个简单的例子。下面我写了代码,但是没有测试过。
select *, right(COL_2,6) + right('0000' +
-----------------重1开始------------------------
(select count(1) from
(
SELECT CardNo,LabelNo, CONVERT(VARCHAR(10), OpTime, 120) AS COL_1,
RIGHT(CONVERT(VARCHAR(8), OpTime, 112),6) +
(SELECT RIGHT(10000 + COUNT(DISTINCT CardNo) + 1, 4)
FROM #GoodsQB WHERE CardNo < T.CardNo) AS COL_2
FROM #GoodsQB T
ORDER BY CardNo
) t2 where t1.COL_1 = t2.COL_1 and t1.COL_2 >=t2.COL_2
)
-------------------------------------------
, 4) [你想要的答案]
from
(
SELECT CardNo,LabelNo, CONVERT(VARCHAR(10), OpTime, 120) AS COL_1,
RIGHT(CONVERT(VARCHAR(8), OpTime, 112),6) +
(SELECT RIGHT(10000 + COUNT(DISTINCT CardNo) + 1, 4)
FROM #GoodsQB WHERE CardNo < T.CardNo) AS COL_2
FROM #GoodsQB T
ORDER BY CardNo) t1
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货