复杂的关联SQL语句，在线等

[w362435819]

表一：

CheckID	CheckDate	CheckUser	WID	Remark	Status	ModifyDate	ModifyUser
gfd	2012-08-02 13:55:01.660	2	2	gfd	0	2012-08-02 13:55:01.660	1

表二：

UserID	Account	Password	UserName	EmployeeNumber	DeptCode	RoleID	Telephone	Email	Status	ModifyDate	ModifyUser
1	SuperAdmin	E10ADC3949BA59ABBE56E057F20F883E	超级管理员	0001	001	1	123456	[e-mail]	1	2012-07-12 14:25:36.840	1
2	Admin	E10ADC3949BA59ABBE56E057F20F883E	管理员	0002	004	2	123456	[e-mail]	1	2012-07-12 14:25:23.787	1
3	Damon	E10ADC3949BA59ABBE56E057F20F883E	Damon	0003	005	3	123456	[e-mail]	1	2012-07-13 16:40:26.430	2

现在想实现，查询表一的内容，CheckUser和ModifyUser是用户ID，但是想把CheckUser换成名字，ModifyUser换成名字

[w362435819]

来高手

[w362435819]

真的很急啊

[--小F--]

select
  a.CheckID,CheckDate,b.UserName,a.WID,a.Remark,a.Status,a.ModifyDate,b.UserName
from
  表二 b
left join
  表一 a
on
  b.RoleID=a.CheckUser
left join
  表1 a
on
  b.ModifyUser=a.CheckUser

[w362435819]

[Quote=引用 3 楼 的回复:]
SQL code

select
a.CheckID,CheckDate,b.UserName,a.WID,a.Remark,a.Status,a.ModifyDate,b.UserName
from
表二 b
left join
表一 a
on
b.RoleID=a.CheckUser
left join
表1 a
on
b.ModifyUser……
[/Quote]想要得到的结果是表1的checkuser和ModifyUser

[w362435819]

CheckID	CheckDate	CheckUser	WID	Remark	Status	ModifyDate	ModifyUser	statusg
gfd	2012-08-02 13:55:01.660	2	2	gfd	0	2012-08-02 13:55:01.660	1	盘点中
gfd	2012-08-02 13:55:01.660	2	2	gfd	0	2012-08-02 13:55:01.660	1	盘点中

把CheckUser和ModifyUser换成名字

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2012-08-02 16:02:08
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[表一]
if object_id('[表一]') is not null drop table [表一]
go 
create table [表一]([CheckID] varchar(3),[CheckDate] datetime,[CheckUser] int,[WID] int,[Remark] varchar(3),[Status] int,[ModifyDate] datetime,[ModifyUser] int)
insert [表一]
select 'gfd','2012-08-02 13:55:01.660',2,2,'gfd',0,'2012-08-02 13:55:01.660',1
--> 测试数据：[表二]
if object_id('[表二]') is not null drop table [表二]
go 
create table [表二]([codeUserID] int,[Account] varchar(10),[Password] varchar(32),[UserName] varchar(10),[EmployeeNumber] varchar(4),[DeptCode] varchar(3),[RoleID] int,[Telephone] int,[Email] varchar(12),[Status] int,[ModifyDate] datetime,[ModifyUser] int)
insert [表二]
select 1,'SuperAdmin','E10ADC3949BA59ABBE56E057F20F883E','超级管理员','0001','001',1,123456,'[e-mail]',1,'2012-07-12 14:25:36.840',1 union all
select 2,'Admin','E10ADC3949BA59ABBE56E057F20F883E','管理员','0002','004',2,123456,'[e-mail]',1,'2012-07-12 14:25:23.787',1 union all
select 3,'Damon','E10ADC3949BA59ABBE56E057F20F883E','Damon','0003','005',3,123456,'[e-mail]',1,'2012-07-13 16:40:26.430',2
--------------开始查询--------------------------
select
  a.CheckID,a.CheckDate,b.UserName,a.WID,a.Remark,a.Status,a.ModifyDate,c.UserName
from
  表一 a
left join
  表二 b
on
  a.CheckUser=b.RoleID
left join
  表二  c
on
  a.ModifyUser=c.RoleID
----------------结果----------------------------
/* CheckID CheckDate               UserName   WID         Remark Status      ModifyDate              UserName
------- ----------------------- ---------- ----------- ------ ----------- ----------------------- ----------
gfd     2012-08-02 13:55:01.660 管理员        2           gfd    0           2012-08-02 13:55:01.660 超级管理员

(1 行受影响)
*/

[w362435819]

[Quote=引用 6 楼 的回复:]
SQL code

----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2012-08-02 16:02:08
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM)……
[/Quote]
我昨天已经搞定了，只要关联两遍就行了