日期:2014-05-17 浏览次数:20523 次
创建表格的语句:
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版本的。
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&n