34,591
社区成员
发帖
与我相关
我的任务
分享
创建表格的语句:
CREATE TABLE [dbo].[Table_AA](
[AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[B1] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[B2] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[C1] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[C2] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Table_AA(AA,B1,B2,C1,C2)VALUES('A','1','1','2','2')
INSERT INTO Table_AA(AA,B1,B2,C1,C2)VALUES('B','3','3','4','4')
INSERT INTO Table_AA(AA,B1,B2,C1,C2)VALUES('C','5','5','6','6')
INSERT INTO Table_AA(AA,B1,B2,C1,C2)VALUES('D','7','7','8','8')
查出的结果是这样的:
A 1 1
A 2 2
B 3 3
B 4 4
C 5 5
C 6 6
D 7 7
D 8 8
---------------------
有数据量比较大 所以 请考虑一下效率问题 ,union也可以 这个我会用 看看有没有其他的办法 高手们,sql2005版本的。
DECLARE @a nchar(10),
@b1 nchar(10),
@b2 nchar(10),
@c1 nchar(10),
@c2 nchar(10)
DECLARE a_cursor CURSOR READ_ONLY FOR
SELECT aa,b1,b2,c1,c2
FROM table_aa
ORDER BY aa
OPEN a_cursor
FETCH NEXT FROM a_cursor
INTO @a,@b1,@b2,@c1,@c2
WHILE @@FETCH_STATUS = 0
BEGIN
select @a 'a',@b1 'd1',@b2 'd2'
union all
select @a 'a',@c1 'd1',@c2 'd2'
--print @a+''+@b1+''+@b2
--print @a+''+@c1+''+@c2
FETCH NEXT FROM a_cursor
INTO @a,@b1,@b2,@c1,@c2
end
CLOSE a_cursor
DEALLOCATE a_cursor
select aa,b1,b2 from Table_AA
union all
select aa,c1,c2 from Table_aa
order by aa,b1
CREATE TABLE [dbo].[Table_AA](
[AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[B1] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[B2] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[C1] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[C2] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Table_AA(AA,B1,B2,C1,C2)VALUES('A','1','1','2','2')
INSERT INTO Table_AA(AA,B1,B2,C1,C2)VALUES('B','3','3','4','4')
INSERT INTO Table_AA(AA,B1,B2,C1,C2)VALUES('C','5','5','6','6')
INSERT INTO Table_AA(AA,B1,B2,C1,C2)VALUES('D','7','7','8','8')
select AA,D1,D2
from
(select AA,B1 'D1',B2 'D2' from Table_AA
union all
select AA,C1 'D1',C2 'D2' from Table_AA) t
order by AA,D1,D2
/*
AA D1 D2
---------- ---------- ----------
A 1 1
A 2 2
B 3 3
B 4 4
C 5 5
C 6 6
D 7 7
D 8 8
(8 row(s) affected)
*/