--測試begin tran Test_insert --事務啟動--建表A
if exists(select * from sysobjects where name = 'A')
drop table A
go create table A(
name nvarchar(20),
id int,
status nvarchar(6),
time datetime,
data1 float,
data2 float
)
goinsert into A(name,id,time,data1,data2) values('X1', 1, '2004-08-25', 2, 4)
insert into A(name,id,time,data1,data2) values('X2', 2, '2004-08-25', 2, 4)
insert into A(name,id,time,data1,data2) values('X3', 4, '2004-08-25', 2, 4)
insert into A(name,id,time,data1,data2) values('X4', 5, '2004-08-25', 2, 4)
go
--建表B
if exists(select * from sysobjects where name = 'B')
drop table B
go create table B(
name nvarchar(20),
id int,
status nvarchar(6),
time datetime,
data3 float,
data4 float
)
goinsert into B(name,id,time,data3,data4) values('X1', 1, '2004-08-25', 2, 4)
insert into B(name,id,time,data3,data4) values('X2', 2, '2004-08-25', 2, 4)
insert into B(name,id,time,data3,data4) values('X7', 4, '2004-08-25', 2, 4)
insert into B(name,id,time,data3,data4) values('X8', 5, '2004-08-25', 2, 4)
go --建表C
if exists(select * from sysobjects where name = 'C')
drop table C
go create table C(
name nvarchar(20),
id int,
status nvarchar(6),
time datetime,
data1 float,
data2 float,
data3 float,
data4 float
)
go--插入B數據到表C
insert into C(name,id,status,time, data3,data4)
select * from B
go
--更新A表name,id和B表name,id重複的數據到c
update C set c.data1 = a.data1 , c.data2 = a.data2
from c
left join A on C.name = a.Name and c.ID = a.ID--插入表A不存在于表B的數據到表C
insert into C(name,id,status,time, data1,data2)
select A.name , A.id, A.status, A.Time, A.data1 , A.data2
from A
where not (A.name + '&&' + convert(nvarchar(4),A.ID) ) in
(select name + '&&' + convert(nvarchar(4),ID) from C)
goselect * from A
select * from B
select * from C
if 1 = 0
rollback tran test_insert --如果不希望真正插入插入數據在這裡退出commit tran test_insert --事務執行
if exists(select * from sysobjects where name = 'A')
drop table A
go create table A(
name nvarchar(20),
id int,
status nvarchar(6),
time datetime,
data1 float,
data2 float
)
goinsert into A(name,id,time,data1,data2) values('X1', 1, '2004-08-25', 2, 4)
insert into A(name,id,time,data1,data2) values('X2', 2, '2004-08-25', 2, 4)
insert into A(name,id,time,data1,data2) values('X3', 4, '2004-08-25', 2, 4)
insert into A(name,id,time,data1,data2) values('X4', 5, '2004-08-25', 2, 4)
go
--建表B
if exists(select * from sysobjects where name = 'B')
drop table B
go create table B(
name nvarchar(20),
id int,
status nvarchar(6),
time datetime,
data3 float,
data4 float
)
goinsert into B(name,id,time,data3,data4) values('X1', 1, '2004-08-25', 2, 4)
insert into B(name,id,time,data3,data4) values('X2', 2, '2004-08-25', 2, 4)
insert into B(name,id,time,data3,data4) values('X7', 4, '2004-08-25', 2, 4)
insert into B(name,id,time,data3,data4) values('X8', 5, '2004-08-25', 2, 4)
go --建表C
if exists(select * from sysobjects where name = 'C')
drop table C
go create table C(
name nvarchar(20),
id int,
status nvarchar(6),
time datetime,
data1 float,
data2 float,
data3 float,
data4 float
)
go--插入B數據到表C
insert into C(name,id,status,time, data3,data4)
select * from B
go
--更新A表name,id和B表name,id重複的數據到c
update C set c.data1 = a.data1 , c.data2 = a.data2
from c
left join A on C.name = a.Name and c.ID = a.ID--插入表A不存在于表B的數據到表C
insert into C(name,id,status,time, data1,data2)
select A.name , A.id, A.status, A.Time, A.data1 , A.data2
from A
where not (A.name + '&&' + convert(nvarchar(4),A.ID) ) in
(select name + '&&' + convert(nvarchar(4),ID) from C)
goselect * from A
select * from B
select * from C
if 1 = 0
rollback tran test_insert --如果不希望真正插入插入數據在這裡退出commit tran test_insert --事務執行
樓主可以不可以連帖子弄到database版去;呵呵;
Union
select name2 as name,id2 as id,status2 as status,time2 as time, Null as data1,Null as data12 ,data2 as data3,data21 as data4,data22 as data5 from b
你的 sql 不太对,出来的结果相同的name和id的记录不能有两个的。应该怎么改?
to ann790914,
我不是需要建新表,A,B表已经存在了,我现在就是想用 sql query 出C表的结果。
WHERE EXISTS
(SELECT 'A'
FROM B
WHERE A.NAME1<>B.NAME2 AND A.ID1 <> B.ID2)
Union
select name2 as name,id2 as id,status2 as status,time2 as time, Null as data1,Null as data12 ,data2 as data3,data21 as data4,data22 as data5 from b
帮帮忙吧。
DATA1 AS DATA1, DATA12 AS DATA12, NULL AS DATA3, NULL AS DATA4, NULL
AS DATA5
FROM A
WHERE (NOT EXISTS
(SELECT 'A'
FROM B
WHERE A.NAME1 = B.NAME2 AND A.ID1 = B.ID2))
UNION
SELECT name2 AS name, id2 AS id, status2 AS status, time2 AS time, NULL
AS data1, NULL AS data12, data2 AS data3, data121 AS data4, data22 AS data5
FROM B
select b.name2 as name,b.id2 as id,b.time2 as time,
b.status2 as status,nvl(a.data1,'') as data1,
nvl(a.data12,'') as data12,b.data2 as data3,
b.data21 as data4,b.data22 as data5
from b, a
where b.name2 = a.name1(+)
and b.id2 = a.id1(+) union allselect a.name1 as name,a.id1 as id,a.time1 as time,
a.status1 as status,a.data1 as data1,
a.data12 as data12,'' as data3,
'' as data4,'' as data5
from a, b
where a.name1 = b.name2(+)
and a.id1 = b.id2(+)
and b.name2 is null
DATA1 AS DATA1, DATA12 AS DATA12, NULL AS DATA3, NULL AS DATA4, NULL
AS DATA5
FROM A
WHERE (NOT EXISTS
(SELECT 'A'
FROM B
WHERE A.NAME1 = B.NAME2 AND A.ID1 = B.ID2))
UNION
SELECT name2 AS name, id2 AS id, status2 AS status, time2 AS time, NULL
AS data1, NULL AS data12, data2 AS data3, data121 AS data4, data22 AS data5
FROM B
WHERE (NOT EXISTS
(SELECT *
FROM A
WHERE (B.NAME2 = A.NAME1 AND B.ID2 = A.ID1)))
UNION
SELECT B.NAME2 AS NAME, B.ID2 AS ID, B.STATUS2 AS STATUS, B.TIME2 AS TIME,
A.DATA1 AS DATA1, A.DATA12 AS DATA12, B.DATA2 AS DATA3, B.DATA121 AS DATA4,
B.DATA22 AS DATA5
FROM A, B
WHERE A.NAME1 = B.NAME2 AND A.ID1 = B.ID2
SELECT NAME1 AS NAME, ID1 AS ID, STATUS1 AS STATUS, TIME1 AS TIME,
DATA1 AS DATA1, DATA12 AS DATA12, NULL AS DATA3, NULL AS DATA4, NULL
AS DATA5
FROM A
WHERE (NOT EXISTS
(SELECT 'A'
FROM B
WHERE A.NAME1 = B.NAME2 AND A.ID1 = B.ID2))
UNION
SELECT name2 AS name, id2 AS id, status2 AS status, time2 AS time, NULL
AS data1, NULL AS data12, data2 AS data3, data121 AS data4, data22 AS data5
FROM B
WHERE (NOT EXISTS
(SELECT *
FROM A
WHERE (B.NAME2 = A.NAME1 AND B.ID2 = A.ID1)))
UNION
SELECT B.NAME2 AS NAME, B.ID2 AS ID, B.STATUS2 AS STATUS, B.TIME2 AS TIME,
A.DATA1 AS DATA1, A.DATA12 AS DATA12, B.DATA2 AS DATA3, B.DATA121 AS DATA4,
B.DATA22 AS DATA5
FROM A, B
WHERE A.NAME1 = B.NAME2 AND A.ID1 = B.ID2
多谢你们,两个都可以,待会儿就给分,但是用哪一个比较好?哪一个执行效率比较高,系统loading较低呢?
to ann790914,
我不是需要建新表,A,B表已经存在了,我现在就是想用 sql query 出C表的结果。
-----------------------------------
我幾乎沒語言了;
樓主大哥你太猛了;替一樓的兄弟委屈;