这两个表链接查询语句怎么写啊

Question

SELECT a.id,b.userName,c.userName,a.Re FROM table1 AS a INNER JOIN table2 AS b ON a.useridA=b.id INNER JOIN table2 AS c ON a.useridB=c.id

Answer 1

SELECT
    a.id, b.userName, c.userName, a.Re
FROM
    table1 AS a
INNER JOIN table2 AS b
ON  a.useridA=b.id
INNER JOIN table2 AS c
ON  a.useridB=c.id

Answer 2

我表这样设计是不是有问题，这个还有很多userid，，那sql语句会很复杂

Answer 3

/****** Object:  Table [dbo].[Table1]    Script Date: 03/03/2014 15:51:32 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Table1](
[id] [int] NOT NULL,
[useridA] [int] NULL,
[useridB] [int] NULL,
[Re] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]GO
GO/****** Object:  Table [dbo].[Table2]    Script Date: 03/03/2014 15:51:41 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Table2](
[id] [int] NOT NULL,
[userName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]GO
--插入数据 INSERT [Table1] ( [id] , [useridA] , [useridB] , [Re] ) VALUES ( 1 , 1 , 2 , '3434' )
 INSERT [Table1] ( [id] , [useridA] , [useridB] , [Re] ) VALUES ( 2 , 2 , 3 , 'rewr' )
 INSERT [Table1] ( [id] , [useridA] , [useridB] , [Re] ) VALUES ( 3 , 1 , 1 , 'efsfsfds' )
 INSERT [Table1] ( [id] , [useridA] , [useridB] , [Re] ) VALUES ( 4 , 2 , 1 , '5454' )INSERT [Table2] ( [id] , [userName] ) VALUES ( 1 , N'张一' )
 INSERT [Table2] ( [id] , [userName] ) VALUES ( 2 , N'张二' )
 INSERT [Table2] ( [id] , [userName] ) VALUES ( 3 , N'张三' )
 INSERT [Table2] ( [id] , [userName] ) VALUES ( 4 , N'张四' ) --SELECT * FROM [Table1]
 SELECT a.id,b.username,c.username,a.re
 FROM [Table1] a LEFT JOIN [Table2] b ON b.id=a.userida
 LEFT JOIN [Table2] c ON c.id=a.useridb
 /*
 id          username                                           username                                           re
----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1           张一                                                 张二                                                 3434
2           张二                                                 张三                                                 rewr
3           张一                                                 张一                                                 efsfsfds
4           张二                                                 张一                                                 5454 */

Answer 4

下面是我的建表脚本，其中
是其它表的主建，太多了。表这么设计好像不好啊。要很多表链接起来。/****** Object:  Table [dbo].[NCC_Info]    Script Date: 03/03/2014 16:35:00 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[NCC_Info](
[Info_ID] [int] IDENTITY(1,1) NOT NULL,
[Info_Regional] [int] NULL,
[Info_ReleaseTime] [datetime] NULL,
[Info_ContractNO] [nvarchar](100) NULL,
[Info_CustomerName] [nvarchar](500) NULL,
[Info_CustomerContacts] [nvarchar](100) NULL,
[Info_CustomerTel] [nvarchar](100) NULL,
[Info_ContractAmount] [numeric](18, 0) NULL,
[Info_ISUserID] [int] NULL,
[Info_ISUserDeptID] [int] NULL,
[Info_ISUserDeptGroupID] [int] NULL,
[Info_MangerUID] [int] NULL,
[Info_MangerUserDeptID] [int] NULL,
[Info_MangerDeptGroupID] [int] NULL,
[Info_OSAssistantUID] [int] NULL,
[Info_OSAssistantDeptID] [int] NULL,
[Info_OSAssistantDeptGroupID] [int] NULL,
[Info_OSUserID] [int] NULL,
[Info_OSUserDeptID] [int] NULL,
[Info_OSUserDeptGroupID] [int] NULL,
[Info_IdentifyingCode] [nvarchar](50) NULL,
[info_Status] [int] NULL,
 CONSTRAINT [PK_NCC_Info] PRIMARY KEY CLUSTERED 
(
[Info_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地域' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_Regional'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发放日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_ReleaseTime'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'合同编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_ContractNO'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_CustomerName'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'联系人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_CustomerContacts'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'联系方式' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_CustomerTel'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'合同金额' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_ContractAmount'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IS员工Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_ISUserID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IS部门' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_ISUserDeptID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IS部门组' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_ISUserDeptGroupID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'合同管理员ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_MangerUID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'合同管理员部门id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_MangerUserDeptID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'合同管理员部门组id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_MangerDeptGroupID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OS助理id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_OSAssistantUID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OS助理部门id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_OSAssistantDeptID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OS助理部门组id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_OSAssistantDeptGroupID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OS员工id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_OSUserID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'os员工部门id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_OSUserDeptID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'os员工部门组id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_OSUserDeptGroupID'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'验证码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'Info_IdentifyingCode'
GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程流转状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NCC_Info', @level2type=N'COLUMN',@level2name=N'info_Status'
GO