再问一下
数据结构
编号 编号1 数量
0901 12
0901 23
0901 11
0902 19
0903 12
0903 12
0903 12
0903 12
0903 12
0903 12
0903 12
根据编号的值和个数得到编号1的值,得到的结果应该是下面的:
编号 编号1 数量
0901 1 12
0901 1 23
0901 1 11
0902 2 19
0903 3 12
0903 3 12
0903 3 12
0903 3 12
0903 3 12
0903 3 12
0903 3 12
如果是这样用sql语句应该怎么写呢
数据结构
编号 编号1 数量
0901 12
0901 23
0901 11
0902 19
0903 12
0903 12
0903 12
0903 12
0903 12
0903 12
0903 12
根据编号的值和个数得到编号1的值,得到的结果应该是下面的:
编号 编号1 数量
0901 1 12
0901 1 23
0901 1 11
0902 2 19
0903 3 12
0903 3 12
0903 3 12
0903 3 12
0903 3 12
0903 3 12
0903 3 12
如果是这样用sql语句应该怎么写呢
INSERT @TB
SELECT '0901', 12 UNION ALL
SELECT '0901', 23 UNION ALL
SELECT '0901', 11 UNION ALL
SELECT '0902', 19 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 1SELECT *,编号1=(select count(DISTINCT 编号) from @tb where 编号<=T.编号)
FROM @TB T/*
编号 编号1 编号1
---- ----------- -----------
0901 12 1
0901 23 1
0901 11 1
0902 19 2
0903 12 3
0903 12 3
0903 12 3
0903 12 3
0903 12 3
0903 12 3
0903 1 3
*/
--TRY
ALTER TABLE TB
ADD ID INT IDENTITY
GO
SELECT 编号,编号1=(SELECT COUNT(DISTINCT 编号) FROM TB WHERE 编号=T.编号 AND ID<=T.ID) FROM TB T
GO
ALTER TABLE TB
DROP COLUMN ID
Create Table 表(编号 varchar(10),数量 varchar(10))
--插入数据
insert into 表
select '0901','12' union all
select '0901','23' union all
select '0901','11' union all
select '0902','19' union all
select '0903','12' union all
select '0903','12' union all
select '0903','12' union all
select '0903','12' union all
select '0903','12' union all
select '0903','12' union all
select '0903','12'--测试语句
select a.*,编号1 from 表 a,(select distinct 编号,编号1=(select count(distinct 编号)+1 from 表 where 编号<t.编号) from 表 t) b
where a.编号=b.编号
--删除测试环境
Drop Table 表/*
0901 12 1
0901 23 1
0901 11 1
0902 19 2
0903 12 3
0903 12 3
0903 12 3
0903 12 3
0903 12 3
0903 12 3
0903 12 3
*/
INSERT @TB
SELECT '0901', 12 UNION ALL
SELECT '0901', 23 UNION ALL
SELECT '0901', 11 UNION ALL
SELECT '0902', 19 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 1SELECT *,编号1=DENSE_RANK() OVER (ORDER BY 编号)
FROM @TB T/*
编号 数量 编号1
---- ----------- --------------------
0901 12 1
0901 23 1
0901 11 1
0902 19 2
0903 12 3
0903 12 3
0903 12 3
0903 12 3
0903 12 3
0903 12 3
0903 1 3
*/
DECLARE @TB TABLE([BH] VARCHAR(4), [SL] INT)
INSERT @TB
SELECT '0901', 12 UNION ALL
SELECT '0901', 23 UNION ALL
SELECT '0901', 11 UNION ALL
SELECT '0902', 19 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 1
SELECT [BH],BH1=DENSE_RANK()OVER(ORDER BY BH),SL FROM @TB
/*BH BH1 SL
---- -------------------- -----------
0901 1 12
0901 1 23
0901 1 11
0902 2 19
0903 3 12
0903 3 12
0903 3 12
0903 3 12
0903 3 12
0903 3 12
0903 3 1*/
dense_Rank()还没有用过呢。DECLARE @TB TABLE([编号] VARCHAR(4), [编号1] INT)
INSERT @TB
SELECT '0901', 12 UNION ALL
SELECT '0901', 23 UNION ALL
SELECT '0901', 11 UNION ALL
SELECT '0902', 19 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 12 UNION ALL
SELECT '0903', 1--方法一
SELECT *,编号1=(select count(DISTINCT 编号) from @tb where 编号<=T.编号)
FROM @TB T
--方法二
SELECT *,dense_Rank() over(order by 编号) as 编号1
from @TB t