a表
AID State aValue
1 1 aaa
2 2 abc
3 4 abb
4 7 efdb表
BID State bValue
1 1 aav
2 3 bbe
3 5 eef
4 7 efdc表
CID State cValue
1 2 fef
2 4 fjd
3 6 iej
4 8 ijd想得到的表
State aValue bValue cValue
1 aaa aav null
2 abc null fef
3 null bbe null
4 abb null fjd
5 null eef null
6 null null iej
7 efd efd null
8 null null ijd就是这个样子,请问大侠们怎么做??
AID State aValue
1 1 aaa
2 2 abc
3 4 abb
4 7 efdb表
BID State bValue
1 1 aav
2 3 bbe
3 5 eef
4 7 efdc表
CID State cValue
1 2 fef
2 4 fjd
3 6 iej
4 8 ijd想得到的表
State aValue bValue cValue
1 aaa aav null
2 abc null fef
3 null bbe null
4 abb null fjd
5 null eef null
6 null null iej
7 efd efd null
8 null null ijd就是这个样子,请问大侠们怎么做??
解决方案 »
- ★★★如何为某个用户设置创建/删除索引的权限?
- 数据库批量插入多行信息的问题
- 如何获得两个特定字符中间的子字符串?
- 这样的错误是数据库冗余吗?
- 安装SQL2000的问题?急救。。。。
- 请教一个存储过程的编写以供学习!
- 数据库备份问题
- natual join
- 查询属性名不定的表?
- alter table b_userlist add FOREIGN KEY (用户组) REFERENCES b_uplist (用户组) ON DELETE CASCADE ON UPDATE CASCADE 哪里错
- 大哥我急!!!有一个急手的问题------如何把表1的记录查询结果变成表2 的效果
- 百万级数据更新问题
declare @A table(AID int,State int,aValue nvarchar(10))
insert into @A
select 1,1,'aaa'
union select 2,2,'abc'
union select 3,4,'abb'
union select 4,7,'efd'declare @B table(BID int,State int,aValue nvarchar(10))
insert into @B
select 1,1,'aav'
union select 2,3,'bbe'
union select 3,5,'eef'
union select 4,7,'efd'declare @C table(CID int,State int,aValue nvarchar(10))
insert into @C
select 1,2,'fef'
union select 2,4,'fjd'
union select 3,6,'iej'
union select 4,8,'ijd'select a.state,b.aValue,c.aValue ,d.aValue from (
select distinct state from @A
union select distinct state from @B
union select distinct state from @C)a
left join @A b on a.state=b.state
left join @B c on a.state=c.state
left join @C d on a.state=d.state
state aValue aValue aValue
----------- ---------- ---------- ----------
1 aaa aav NULL
2 abc NULL fef
3 NULL bbe NULL
4 abb NULL fjd
5 NULL eef NULL
6 NULL NULL iej
7 efd efd NULL
8 NULL NULL ijd(8 個資料列受到影響)
if not object_id('a') is null
drop table a
Go
Create table a([AID] int,[State] int,[aValue] nvarchar(3))
Insert a
select 1,1,N'aaa' union all
select 2,2,N'abc' union all
select 3,4,N'abb' union all
select 4,7,N'efd'
Go
--> -->
if not object_id('b') is null
drop table b
Go
Create table b([BID] int,[State] int,[bValue] nvarchar(3))
Insert b
select 1,1,N'aav' union all
select 2,3,N'bbe' union all
select 3,5,N'eef' union all
select 4,7,N'efd'
Go
--> -->
if not object_id('c') is null
drop table c
Go
Create table c([CID] int,[State] int,[cValue] nvarchar(3))
Insert c
select 1,2,N'fef' union all
select 2,4,N'fjd' union all
select 3,6,N'iej' union all
select 4,8,N'ijd'
Go
;with t
as
(select 1 as State
union all
select State+1 from t where State<8),
t2
as
(select *,'a' as tab from a
union all
select * ,'b'from b
union all
select *,'c' from c)
select
t.State,
max(case when tab='a' then [aValue] end) as [aValue],
max(case when tab='b' then [aValue] end) as [bValue],
max(case when tab='c' then [aValue] end) as [cValue]
from
t
left join
t2 on t.State=t2.State
group by t.State
(4 個資料列受到影響)(4 個資料列受到影響)(4 個資料列受到影響)
State aValue bValue cValue
----------- ------ ------ ------
1 aaa aav NULL
2 abc NULL fef
3 NULL bbe NULL
4 abb NULL fjd
5 NULL eef NULL
6 NULL NULL iej
7 efd efd NULL
8 NULL NULL ijd
(8 個資料列受到影響)
drop table a
Go
Create table a([AID] int,[State] int,[aValue] nvarchar(3))
Insert a
select 1,1,N'aaa' union all
select 2,2,N'abc' union all
select 3,4,N'abb' union all
select 4,7,N'efd'
if not object_id('b') is null
drop table b
Go
Create table b([BID] int,[State] int,[bValue] nvarchar(3))
Insert b
select 1,1,N'aav' union all
select 2,3,N'bbe' union all
select 3,5,N'eef' union all
select 4,7,N'efd'if not object_id('c') is null
drop table c
Go
Create table c([CID] int,[State] int,[cValue] nvarchar(3))
Insert c
select 1,2,N'fef' union all
select 2,4,N'fjd' union all
select 3,6,N'iej' union all
select 4,8,N'ijd'
select Coalesce(A.State,B.State,C.State) as State,aValue,bValue,cValue from a
full join b
on a.State=b.State
full join c
on a.State=c.State
order by State/*
State aValue bValue cValue
----------- ------ ------ ------
1 aaa aav NULL
2 abc NULL fef
3 NULL bbe NULL
4 abb NULL fjd
5 NULL eef NULL
6 NULL NULL iej
7 efd efd NULL
8 NULL NULL ijd(所影响的行数为 8 行)
*/
再用Coalesce 函数提取 State 的值就行了