两个表table1 table2
table1 | table2
zccode plucode price |plucode price
001 01 1 |01 3
002 02 2 |获得结果
zccode plucode price
001 01 3
002 02 2不能使用update更改表数据,如何组合两个表,求教
table1 | table2
zccode plucode price |plucode price
001 01 1 |01 3
002 02 2 |获得结果
zccode plucode price
001 01 3
002 02 2不能使用update更改表数据,如何组合两个表,求教
table1
zccode plucode price
001 01 1
002 02 2 table2
plucode price
01 3
left join table2 on table1.plucode =table2.plucode
两个表table1 table2
table1 | table2
zccode plucode price |plucode price
001 01 1 |01 3
002 02 2 |获得结果
zccode plucode price
001 01 3
002 02 2不能使用update更改表数据,如何组合两个表,求教
*/
go
if OBJECT_ID('table1')is not null
drop table table1
go
create table table1(
zccode varchar(3),
plucode varchar(2),
price numeric(5,2)
)
go
insert table1
select '001','01',1 union all
select '002','02',2
go
if OBJECT_ID('table2')is not null
drop table table2
go
create table table2(
plucode varchar(2),
price numeric(5,2)
)
go
insert table2
select '01',3--查询结果实现:
select a.zccode,a.plucode,isnull(b.price,a.price) as price
from table1 a
left join table2 b
on a.plucode=b.plucode/*
zccode plucode price
001 01 3.00
002 02 2.00
*/--修改了一下
from table1 left join table2
on table1.plucode =table2.plucode
insert into table1 values('001', '01', 1)
insert into table1 values('002', '02', 2)
create table table2(plucode varchar(10),price int)
insert into table2 values('01', 3)
goselect m.zccode,
isnull(m.plucode,n.plucode) plucode,
isnull(n.price , m.price) price
from table1 m full join table2 n
on m.plucode = n.plucode/*
zccode plucode price
---------- ---------- -----------
001 01 3
002 02 2(所影响的行数为 2 行)
*/drop table table1 , table2