DECLARE @t TABLE([id] INT,[v] NVARCHAR(1))
INSERT @t SELECT 1,N'A'
UNION ALL SELECT 2,N'B'
UNION ALL SELECT 3,N'B'
UNION ALL SELECT 4,N'A'
/************/
/*Test Data*/
/***fcuandy**/
/*2008-12-26*/
/************/
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) id,b.v FROM @t a
CROSS JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY v) idx,v FROM @t GROUP BY v) b
ORDER BY a.id,b.idx
/*
1 A
2 B
3 A
4 B
5 A
6 B
7 A
8 B
*/
INSERT @t SELECT 1,N'A'
UNION ALL SELECT 2,N'B'
UNION ALL SELECT 3,N'B'
UNION ALL SELECT 4,N'A'
/************/
/*Test Data*/
/***fcuandy**/
/*2008-12-26*/
/************/
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) id,b.v FROM @t a
CROSS JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY v) idx,v FROM @t GROUP BY v) b
ORDER BY a.id,b.idx
/*
1 A
2 B
3 A
4 B
5 A
6 B
7 A
8 B
*/
insert @t
select 1,'A' union
select 2,'B' union
select 3,'B' union
select 4,'A'
select * from @t
union
select fid+(select max(fid) from @t),col from @t/*
fid col
----------- ----------
1 A
2 B
3 B
4 A
5 A
6 B
7 B
8 A
*/
A
B
A
B
A
B
A
B
这样显示啊