把所有表合并成一个表(SQL 合并问题)
表a
id tag
1 aa
2 bb
表b
id tag
1 cc
2 dd
表c
id tag
1 dd
2 ff
我想把所有数据合并到表D,并且是每次从每个表读一条数据
表d
id tag table
1 aa a
2 cc b
3 dd c
4 bb a
5 dd b
6 ff c
怎么写?
------------------------------------------------
如果是下面这样写就是一个一个表读了
INSERT INTO d
(tag,type)
SELECT tag, 'a ' AS type
FROM a
INSERT INTO d
(tag,type)
SELECT tag, 'b ' AS type
FROM b
INSERT INTO d
(tag,type)
SELECT tag, 'c ' AS type
FROM c
------------------------------------------------
这样写也是没按顺序一个表一个表读
INSERT INTO d
(tag,type)
(
SELECT tag, 'a ' AS type
FROM a
UNION
SELECT tag, 'b ' AS type
FROM b
UNION
SELECT tag, 'c ' AS type
FROM c
)
[解决办法]
INSERT INTO d
(tag,type)
select tag,type
from (
SELECT tag, 'a ' AS type,id*100+1 as ordernum
FROM a
UNION all
SELECT tag, 'b ' AS type,id*100+2 as ordernum
FROM b
UNION all
SELECT tag, 'c ' AS type,id*100+3 as ordernum
FROM c
) as t
order by ordernum
[解决办法]
create table A(id int, tag varchar(10))
insert A select 1, 'aa '
union all select 2, 'bb '
create table B(id int, tag varchar(10))
insert B select 1, 'cc '
union all select 2, 'dd '
create table C(id int, tag varchar(10))
insert C select 1, 'dd '
union all select 2, 'ff '
select id=identity(int, 1, 1), tmp.tag, tmp.[table] into D
from
(
select top 100 percent tmp.tag, tmp.[table] from
(
select id, tag, [table]= 'A ' from A
union all
select id, tag, [table]= 'B ' from B
union all
select id, tag, [table]= 'C ' from C
)tmp order by id, tag
)tmp
select * from D order by id
--result
id tag table
----------- ---------- -----
1 aa A
2 cc B
3 dd C
4 bb A
5 dd B
6 ff C
(6 row(s) affected)
