select b1.name,b2.name from 数据表 left join b b1 on a.id1=b1.员工id left join b b2 on a.id2=b2.员工id
---------------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-05-04 11:08:13 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Blog : http://blog.csdn.net/htl258 ------------------------------------------------------------------------------------> 生成测试数据表: [ta] IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta] GO CREATE TABLE [ta] ([id] [int],[name] [nvarchar](10)) INSERT INTO [ta] SELECT '1','张三' UNION ALL SELECT '2','李四' UNION ALL SELECT '3','王五'--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([审核人] [int],[申请人] [int],[销售员] [int]) INSERT INTO [tb] SELECT '1','2','1' UNION ALL SELECT '2','3','3'--SELECT * FROM [ta] --SELECT * FROM [tb]-->SQL查询如下: select b.name [审核人],c.name 申请人,d.name 销售员 from tb a join ta b on a.审核人=b.id join ta c on a.申请人=c.id join ta d on a.销售员=d.id /* 审核人 申请人 销售员 ---------- ---------- ---------- 张三 李四 张三 李四 王五 王五(2 行受影响) */
from 一个表 a,员工表 b
where a.id=b.id
from 员工表 a
join 数据表 b
on a.id=b.员工id
都是id写入的。。
查询时候希望输出的是
id对应的名字
from 数据表
left join b b1 on a.id1=b1.员工id
left join b b2 on a.id2=b2.员工id
-- Author : htl258(Tony)
-- Date : 2010-05-04 11:08:13
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [ta]
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
GO
CREATE TABLE [ta] ([id] [int],[name] [nvarchar](10))
INSERT INTO [ta]
SELECT '1','张三' UNION ALL
SELECT '2','李四' UNION ALL
SELECT '3','王五'--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([审核人] [int],[申请人] [int],[销售员] [int])
INSERT INTO [tb]
SELECT '1','2','1' UNION ALL
SELECT '2','3','3'--SELECT * FROM [ta]
--SELECT * FROM [tb]-->SQL查询如下:
select b.name [审核人],c.name 申请人,d.name 销售员
from tb a
join ta b on a.审核人=b.id
join ta c on a.申请人=c.id
join ta d on a.销售员=d.id
/*
审核人 申请人 销售员
---------- ---------- ----------
张三 李四 张三
李四 王五 王五(2 行受影响)
*/