27,579
社区成员
发帖
与我相关
我的任务
分享
---------------------------------
-- ================================================
/*
作者:北方男生(天南地北天涯浪子浪跡天涯,秋去冬來秋水伊人望穿秋水)
时间:2009-01-22
地点:广东東莞
*/
-- =================================================
---------------------------------
if object_id('tb')is not null drop table tb
go
create table tb(id int, dm varchar(5))
insert tb select 1, 'a'
insert tb select 2, 'a'
insert tb select 3, 'a'
insert tb select 4, 'a'
insert tb select 5, 'a'
insert tb select 6, 'b'
insert tb select 7, 'b'
insert tb select 8, 'c'
insert tb select 9, 'c'
select *,succeess=dm+convert(varchar,(select count(1) from tb where dm=a.dm and id<=a.id)) from tb a
drop table tb
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,dm VARCHAR(1))
INSERT INTO @T
SELECT 1,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'a' UNION ALL
SELECT 4,'a' UNION ALL
SELECT 5,'a' UNION ALL
SELECT 6,'b' UNION ALL
SELECT 7,'b' UNION ALL
SELECT 8,'c' UNION ALL
SELECT 9,'c'
--SQL查询如下:
SELECT
id,
dm,
dmtj=dm+CAST(ROW_NUMBER() OVER(PARTITION BY dm ORDER BY id) AS VARCHAR)
FROM (
SELECT
id,
dm=CASE WHEN (SELECT COUNT(*)
FROM @T
WHERE A.dm=dm
AND id<=A.id)<=4
THEN dm
ELSE (SELECT TOP 1 dm
FROM @T
WHERE dm<>A.dm
AND id>A.id
ORDER BY id) END
FROM @T AS A
) AS A
/*
id dm dmtj
----------- ---- -------------------------------
1 a a1
2 a a2
3 a a3
4 a a4
5 b b1
6 b b2
7 b b3
8 c c1
9 c c2
(9 行受影响)
*/
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[dm] nvarchar(1))
Insert tb
select 1,N'a' union all
select 2,N'a' union all
select 3,N'a' union all
select 4,N'a' union all
select 5,N'a' union all
select 6,N'b' union all
select 7,N'b' union all
select 8,N'c' union all
select 9,N'c'
Go
select *,
new=dm+convert(varchar,(select count(1) from tb where dm=a.dm and id<=a.id))
from tb a
/*
id dm new
----------- ---- -------------------------------
1 a a1
2 a a2
3 a a3
4 a a4
5 a a5
6 b b1
7 b b2
8 c c1
9 c c2
*/
if object_id('tb')is not null drop table tb
go
create table tb(id int, dm varchar(5))
insert tb select 1, 'a'
insert tb select 2, 'a'
insert tb select 3, 'a'
insert tb select 4, 'a'
insert tb select 5, 'a'
insert tb select 6, 'b'
insert tb select 7, 'b'
insert tb select 8, 'c'
insert tb select 9, 'c'
select *,dmtj=dm+ltrim((select count(*) from tb where dm=t.dm and id<=t.id)) from tb t
/*id dm dmtj
----------- ----- -----------------
1 a a1
2 a a2
3 a a3
4 a a4
5 a a5
6 b b1
7 b b2
8 c c1
9 c c2
*/