复杂的关联SQL语句

2012-09-11

复杂的关联SQL语句，在线等表一：SQL codeCheckIDCheckDateCheckUserWIDRemarkStatusModifyDateModifyUsergf

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

SQL code

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

表二：

SQL code

UserID    Account    Password    UserName    EmployeeNumber    DeptCode    RoleID    Telephone    Email    Status    ModifyDate    ModifyUser1    SuperAdmin    E10ADC3949BA59ABBE56E057F20F883E    超级管理员    0001    001    1    123456    admin@qq.com    1    2012-07-12 14:25:36.840    12    Admin    E10ADC3949BA59ABBE56E057F20F883E    管理员    0002    004    2    123456    admin@qq.com    1    2012-07-12 14:25:23.787    13    Damon    E10ADC3949BA59ABBE56E057F20F883E    Damon    0003    005    3    123456    damon@qq.com    1    2012-07-13 16:40:26.430    2

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

[解决办法]

SQL code

------------------------------ 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,'admin@qq.com',1,'2012-07-12 14:25:36.840',1 union allselect 2,'Admin','E10ADC3949BA59ABBE56E057F20F883E','管理员','0002','004',2,123456,'admin@qq.com',1,'2012-07-12 14:25:23.787',1 union allselect 3,'Damon','E10ADC3949BA59ABBE56E057F20F883E','Damon','0003','005',3,123456,'damon@qq.com',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.UserNamefrom  表一 aleft join  表二 bon  a.CheckUser=b.RoleIDleft join  表二  con  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 行受影响)*/

热点排行