27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-30 10:35:45
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[代码表]
if object_id('[代码表]') is not null drop table [代码表]
go
create table [代码表]([col1] varchar(4),[col2] varchar(4))
insert [代码表]
select 'A001','西瓜' union all
select 'A002','南瓜' union all
select 'A003','东瓜'
--> 测试数据:[产品表]
if object_id('[产品表]') is not null drop table [产品表]
go
create table [产品表]([id] int,[col1] varchar(4),[col2] varchar(4),[col3] varchar(4))
insert [产品表]
select 1,'A001','A002','A003' union all
select 2,'A002','A003',null
--------------开始查询--------------------------
select
a.id,b.col2 as 产品1,c.col2 as 产品2,isnull(d.col2,'') as 产品3
from
产品表 a
left join 代码表 b
on
a.col1 = b.col1
left join 代码表 c
on
a.col2 = c.col1
left join 代码表 d
on
a.col3 = d.col1
----------------结果----------------------------
/* id 产品1 产品2 产品3
----------- ---- ---- ----
1 西瓜 南瓜 东瓜
2 南瓜 东瓜
(2 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-30 10:35:45
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[代码表]
if object_id('[代码表]') is not null drop table [代码表]
go
create table [代码表]([col1] varchar(4),[col2] varchar(4))
insert [代码表]
select 'A001','西瓜' union all
select 'A002','南瓜' union all
select 'A003','东瓜'
--> 测试数据:[产品表]
if object_id('[产品表]') is not null drop table [产品表]
go
create table [产品表]([id] int,[col1] varchar(4),[col2] varchar(4),[col3] varchar(4))
insert [产品表]
select 1,'A001','A002','A003' union all
select 2,'A002','A003',null
--------------开始查询--------------------------
select
a.id,b.col2 as 产品1,c.col2 as 产品2,d.col2 as 产品3
from
产品表 a
left join 代码表 b
on
a.col1 = b.col1
left join 代码表 c
on
a.col2 = c.col1
left join 代码表 d
on
a.col3 = d.col1
----------------结果----------------------------
/* id 产品1 产品2 产品3
----------- ---- ---- ----
1 西瓜 南瓜 东瓜
2 南瓜 东瓜 NULL
(2 行受影响)
*/
SELECT
A.ID,
B.名字,
C.名字,
D.名字
FROM
产品表 A LEFT JOIN 代码表 B
ON A.NAME=B.NAME LEFT JOIN
代码表 C ON A.NAME=C.NAME LEFT JOIN
代码表 D ON A.NAME =D.NAME
SELECT A.ID,B.名字,C.名字,D.名字 FROM 产品表 A,代码表 B,代码表 C,代码表 D WHER A.NAME=B.NAME AND A.NAME=C.NAME AND A.NAME =D.NAME
select a.id,b.名称 as 产品1,c.名称 as 产品2,d.名称 as 产品3
from 产品表 as a
left join 代码表 as b
on a.产品1 = b.代码
left join 代码表 as c
on a.产品2 = c.代码
left join 代码表 as d
on a.产品3 = d.代码