看例子,两个表
表A:
ID NUMA NUMB NUMC
1 1.0 2.0 1.0
2 2.0 2.0 3.0
3 1.0 3.0 3.0
........................表B:
ID BD INFO
1 1.0 东北风
2 2.0 东风
3 3.0 东南风
4 4.0 南风
...............怎么样连接这两个表,可形成视图
视图A:
ID NUMA NUMB NUMC
1 东北风 东风 东北风
2 东风 东风 东南风
3 东北风 东南风 东南风
..................
谢谢!!!
表A:
ID NUMA NUMB NUMC
1 1.0 2.0 1.0
2 2.0 2.0 3.0
3 1.0 3.0 3.0
........................表B:
ID BD INFO
1 1.0 东北风
2 2.0 东风
3 3.0 东南风
4 4.0 南风
...............怎么样连接这两个表,可形成视图
视图A:
ID NUMA NUMB NUMC
1 东北风 东风 东北风
2 东风 东风 东南风
3 东北风 东南风 东南风
..................
谢谢!!!
from A
left join B on a.NUMA=b.BD
left join C on a.NUMB=c.BD
left join D on a.NUMC=d.BD
-- Author : htl258(Tony)
-- Date : 2010-02-10 15:35:06
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([ID] INT,[NUMA] DECIMAL(18,1),[NUMB] DECIMAL(18,1),[NUMC] DECIMAL(18,1))
INSERT [a]
SELECT 1,1.0,2.0,1.0 UNION ALL
SELECT 2,2.0,2.0,3.0 UNION ALL
SELECT 3,1.0,3.0,3.0
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([ID] INT,[BD] DECIMAL(18,1),[INFO] NVARCHAR(10))
INSERT [b]
SELECT 1,1.0,N'东北风' UNION ALL
SELECT 2,2.0,N'东风' UNION ALL
SELECT 3,3.0,N'东南风' UNION ALL
SELECT 4,4.0,N'南风'
GO
--SELECT * FROM [b]-->SQL查询如下:
select a.ID,b.INFO NUMA,c.INFO NUMB,d.INFO NUMC
from A
left join B on a.NUMA=b.BD
left join B c on a.NUMB=c.BD
left join B d on a.NUMC=d.BD
/*
ID NUMA NUMB NUMC
1 东北风 东风 东北风
2 东风 东风 东南风
3 东北风 东南风 东南风
*/
--> Author : wufeng4552
--> Date : 2010-02-10
if not object_id('ta') is null
drop table ta
Go
Create table ta([ID] int,[NUMA] decimal(18,1),[NUMB] decimal(18,1),[NUMC] decimal(18,1))
Insert ta
select 1,1.0,2.0,1.0 union all
select 2,2.0,2.0,3.0 union all
select 3,1.0,3.0,3.0
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[BD] decimal(18,1),[INFO] nvarchar(3))
Insert tb
select 1,1.0,N'东北风' union all
select 2,2.0,N'东风' union all
select 3,3.0,N'东南风' union all
select 4,4.0,N'南风'
Go
select a.id,
b.[INFO] [NUMA],
c.[INFO] [NUMB],
d.[INFO] [NUMC]
from ta a inner join tb b on a.[NUMA]=b.[BD]
inner join tb c on a.[NUMA]=c.[BD]
inner join tb d on a.[NUMA]=d.[BD]
order by a.id
/*
id NUMA NUMB NUMC
----------- ---- ---- ----
1 东北风 东北风 东北风
2 东风 东风 东风
3 东北风 东北风 东北风(3 個資料列受到影響)*/
declare @ta table([ID] int,[NUMA] numeric(2,1),[NUMB] numeric(2,1),[NUMC] numeric(2,1))
insert @ta
select 1,1.0,2.0,1.0 union all
select 2,2.0,2.0,3.0 union all
select 3,1.0,3.0,3.0--> 测试数据:@tB
declare @tB table([ID] int,[BD] numeric(2,1),[INFO] varchar(6))
insert @tB
select 1,1.0,'东北风' union all
select 2,2.0,'东风' union all
select 3,3.0,'东南风' union all
select 4,4.0,'南风'select a.id,b.INFO as NUMA ,c.INFO asNUMB ,d.INFO as NUMC from @ta a
left join @tb b on a.id =b.id
left join @tb c on a.id =c.id
left join @tb d on a.id =d.id
/*id NUMA asNUMB NUMC
----------- ------ ------ ------
1 东北风 东北风 东北风
2 东风 东风 东风
3 东南风 东南风 东南风(3 行受影响)*/
select a.id,b1.info as NUMA,b2.info as NUMB,b3.info as NUMC
from a,b b1,b b2,b b3
where a.numa=b1.bd and a.numb=b2.bd and a.numc=b3.bd
order by a.id