select isnull(a.purchaseitem,b.salesitem) item,a.purchaseprice,b.salesprice,isnull(a.time,b.time) time from ta a full join tb b on a.purchaseitem=b.salesitem
if object_id('[ta]') is not null drop table [ta] go create table [ta]([purchaseitem] varchar(10),[purchaseprice] int,[time] varchar(30)) insert [ta] select 'a',20,'05/04--05/12' union all select 'a',12,'05/12--05/23' union all select 'b',23,'05/01--05/25' union all select 'c',15,'05/03--05/28'if object_id('[tb]') is not null drop table [tb] go create table [tb]([salesitem] varchar(10),[salesprice] int,[time] varchar(30)) insert [tb] select 'a',24,'05/04--05/12' union all select 'c',26,'05/03--05/28' union all select 'd',25,'05/03--05/23'select isnull(a.purchaseitem,b.salesitem) item,a.purchaseprice,b.salesprice,isnull(a.time,b.time) time from ta a full join tb b on a.purchaseitem=b.salesitem /* item purchaseprice salesprice time ---------- ------------- ----------- ------------------------------ a 20 24 05/04--05/12 a 12 24 05/12--05/23 b 23 NULL 05/01--05/25 c 15 26 05/03--05/28 d NULL 25 05/03--05/23(5 行受影响) */
select isnull(ta.purchaseitem ,tb.salesitem ) as item ,ta.purchaseprice ,tb.salesprice ,isnull(ta.time ,tb.time ) as time from ta full join tb on ta.purchaseitem =tb.salesitem and ta.time =tb.time
测试:if object_id('[ta]') is not null drop table [ta] go create table [ta]([purchaseitem] varchar(10),[purchaseprice] int,[time] varchar(30)) insert [ta] select 'a',20,'05/04--05/12' union all select 'a',12,'05/12--05/23' union all select 'b',23,'05/01--05/25' union all select 'c',15,'05/03--05/28'if object_id('[tb]') is not null drop table [tb] go create table [tb]([salesitem] varchar(10),[salesprice] int,[time] varchar(30)) insert [tb] select 'a',24,'05/04--05/12' union all select 'c',26,'05/03--05/28' union all select 'd',25,'05/03--05/23'select isnull(ta.purchaseitem ,tb.salesitem ) as item ,ta.purchaseprice ,tb.salesprice ,isnull(ta.time ,tb.time ) as time from ta full join tb on ta.purchaseitem =tb.salesitem and ta.time =tb.time /* item purchaseprice salesprice time ---------- ------------- ----------- ------------------------------ a 20 24 05/04--05/12 a 12 NULL 05/12--05/23 b 23 NULL 05/01--05/25 c 15 26 05/03--05/28 d NULL 25 05/03--05/23(5 行受影响) */
不好意思,没看好,改正: if object_id('[ta]') is not null drop table [ta] go create table [ta]([purchaseitem] varchar(10),[purchaseprice] int,[time] varchar(30)) insert [ta] select 'a',20,'05/04--05/12' union all select 'a',12,'05/12--05/23' union all select 'b',23,'05/01--05/25' union all select 'c',15,'05/03--05/28'if object_id('[tb]') is not null drop table [tb] go create table [tb]([salesitem] varchar(10),[salesprice] int,[time] varchar(30)) insert [tb] select 'a',24,'05/04--05/12' union all select 'c',26,'05/03--05/28' union all select 'd',25,'05/03--05/23'select isnull(a.purchaseitem,b.salesitem) item,a.purchaseprice,b.salesprice,isnull(a.time,b.time) time from ta a full join tb b on a.purchaseitem=b.salesitem and a.time=b.time /* item purchaseprice salesprice time ---------- ------------- ----------- ------------------------------ a 20 24 05/04--05/12 a 12 NULL 05/12--05/23 b 23 NULL 05/01--05/25 c 15 26 05/03--05/28 d NULL 25 05/03--05/23(5 行受影响) */
如果后面在加上一个 td: item description a red b orange c red d black ................... ta: purchaseitem purchaseprice time a 20 05/04--05/12 a 12 05/12--05/23 b 23 05/01--05/25 c 15 05/03--05/28 ...................... tb: salesitem salesprice time a 24 05/04--05/12 c 26 05/03--05/28 d 25 05/03--05/23 ....................... 如何将三个表连接成如下的表: tf: item purchaseprice salesprice time description a 20 24 05/04--05/12 red a 12 null 05/12--05/23 red b 23 null 05/01--05/25 orange c 15 null 05/03--05/28 red d null 25 05/03--05/23 balck .......................
from ta a
full join tb b
on a.purchaseitem=b.salesitem
go
create table [ta]([purchaseitem] varchar(10),[purchaseprice] int,[time] varchar(30))
insert [ta] select 'a',20,'05/04--05/12'
union all select 'a',12,'05/12--05/23'
union all select 'b',23,'05/01--05/25'
union all select 'c',15,'05/03--05/28'if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([salesitem] varchar(10),[salesprice] int,[time] varchar(30))
insert [tb] select 'a',24,'05/04--05/12'
union all select 'c',26,'05/03--05/28'
union all select 'd',25,'05/03--05/23'select isnull(a.purchaseitem,b.salesitem) item,a.purchaseprice,b.salesprice,isnull(a.time,b.time) time
from ta a
full join tb b
on a.purchaseitem=b.salesitem
/*
item purchaseprice salesprice time
---------- ------------- ----------- ------------------------------
a 20 24 05/04--05/12
a 12 24 05/12--05/23
b 23 NULL 05/01--05/25
c 15 26 05/03--05/28
d NULL 25 05/03--05/23(5 行受影响)
*/
,ta.purchaseprice
,tb.salesprice
,isnull(ta.time ,tb.time ) as time
from ta full join tb on ta.purchaseitem =tb.salesitem and ta.time =tb.time
go
create table [ta]([purchaseitem] varchar(10),[purchaseprice] int,[time] varchar(30))
insert [ta] select 'a',20,'05/04--05/12'
union all select 'a',12,'05/12--05/23'
union all select 'b',23,'05/01--05/25'
union all select 'c',15,'05/03--05/28'if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([salesitem] varchar(10),[salesprice] int,[time] varchar(30))
insert [tb] select 'a',24,'05/04--05/12'
union all select 'c',26,'05/03--05/28'
union all select 'd',25,'05/03--05/23'select isnull(ta.purchaseitem ,tb.salesitem ) as item
,ta.purchaseprice
,tb.salesprice
,isnull(ta.time ,tb.time ) as time
from ta full join tb on ta.purchaseitem =tb.salesitem and ta.time =tb.time
/*
item purchaseprice salesprice time
---------- ------------- ----------- ------------------------------
a 20 24 05/04--05/12
a 12 NULL 05/12--05/23
b 23 NULL 05/01--05/25
c 15 26 05/03--05/28
d NULL 25 05/03--05/23(5 行受影响)
*/
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([purchaseitem] varchar(10),[purchaseprice] int,[time] varchar(30))
insert [ta] select 'a',20,'05/04--05/12'
union all select 'a',12,'05/12--05/23'
union all select 'b',23,'05/01--05/25'
union all select 'c',15,'05/03--05/28'if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([salesitem] varchar(10),[salesprice] int,[time] varchar(30))
insert [tb] select 'a',24,'05/04--05/12'
union all select 'c',26,'05/03--05/28'
union all select 'd',25,'05/03--05/23'select isnull(a.purchaseitem,b.salesitem) item,a.purchaseprice,b.salesprice,isnull(a.time,b.time) time
from ta a
full join tb b
on a.purchaseitem=b.salesitem and a.time=b.time
/*
item purchaseprice salesprice time
---------- ------------- ----------- ------------------------------
a 20 24 05/04--05/12
a 12 NULL 05/12--05/23
b 23 NULL 05/01--05/25
c 15 26 05/03--05/28
d NULL 25 05/03--05/23(5 行受影响)
*/
td:
item description
a red
b orange
c red
d black
................... ta:
purchaseitem purchaseprice time
a 20 05/04--05/12
a 12 05/12--05/23
b 23 05/01--05/25
c 15 05/03--05/28
...................... tb:
salesitem salesprice time
a 24 05/04--05/12
c 26 05/03--05/28
d 25 05/03--05/23
....................... 如何将三个表连接成如下的表:
tf:
item purchaseprice salesprice time description
a 20 24 05/04--05/12 red
a 12 null 05/12--05/23 red
b 23 null 05/01--05/25 orange
c 15 null 05/03--05/28 red
d null 25 05/03--05/23 balck
.......................