建立测试环境:
CREATE TABLE [RdRecord2] (
[ID] [int] NOT NULL,
[PersonCode] [char] (10) NULL,
[SHPersonCode] [char] (10) NULL)ALTER TABLE [RdRecord2] WITH NOCHECK ADD CONSTRAINT [PK_RdRecord2] PRIMARY KEY NONCLUSTERED ( [ID] )
INSERT [RdRecord2] ([ID],[PersonCode],[SHPersonCode]) VALUES ( 1,'001','002')
INSERT [RdRecord2] ([ID],[PersonCode],[SHPersonCode]) VALUES ( 2,'001','003')CREATE TABLE [Person] (
[PersonCode] [char] (10) NOT NULL,
[PersonName] [char] (50) NULL)ALTER TABLE [Person] WITH NOCHECK ADD CONSTRAINT [PK_Person] PRIMARY KEY NONCLUSTERED ( [PersonCode] )
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '001','人员1')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '002','人员2')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '003','人员3')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '004','人员4')=======================================
问题:我的查询语句:
SELECT rdr.[ID], rdr.PersonCode,Person.PersonName,
rdr.SHPersonCode,Person.PersonName as SHPersonName FROM RdRecord2 rdr
LEFT JOIN Person ON rdr.PersonCode=Person.PersonCode
LEFT JOIN Person p ON rdr.SHPersonCode=p.PersonCode
WHERE rdr.[ID]=1相当于一个表和另外一个表连接两次(一个表的两个字段和另一个表有关系),可是结果不正确,
由于原表字段非常多,只选了几个简单的字段,且可为空,只能用Left join
CREATE TABLE [RdRecord2] (
[ID] [int] NOT NULL,
[PersonCode] [char] (10) NULL,
[SHPersonCode] [char] (10) NULL)ALTER TABLE [RdRecord2] WITH NOCHECK ADD CONSTRAINT [PK_RdRecord2] PRIMARY KEY NONCLUSTERED ( [ID] )
INSERT [RdRecord2] ([ID],[PersonCode],[SHPersonCode]) VALUES ( 1,'001','002')
INSERT [RdRecord2] ([ID],[PersonCode],[SHPersonCode]) VALUES ( 2,'001','003')CREATE TABLE [Person] (
[PersonCode] [char] (10) NOT NULL,
[PersonName] [char] (50) NULL)ALTER TABLE [Person] WITH NOCHECK ADD CONSTRAINT [PK_Person] PRIMARY KEY NONCLUSTERED ( [PersonCode] )
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '001','人员1')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '002','人员2')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '003','人员3')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '004','人员4')=======================================
问题:我的查询语句:
SELECT rdr.[ID], rdr.PersonCode,Person.PersonName,
rdr.SHPersonCode,Person.PersonName as SHPersonName FROM RdRecord2 rdr
LEFT JOIN Person ON rdr.PersonCode=Person.PersonCode
LEFT JOIN Person p ON rdr.SHPersonCode=p.PersonCode
WHERE rdr.[ID]=1相当于一个表和另外一个表连接两次(一个表的两个字段和另一个表有关系),可是结果不正确,
由于原表字段非常多,只选了几个简单的字段,且可为空,只能用Left join
解决方案 »
- 重要:int类型与varchar类型的隐式转化
- 删除表中某几个字段相同的数据
- 抽取随机数据保存到中间表的问题,急!!!
- SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has be
- 请问怎么把表中的列名一起复制下来?
- SqlServer2000 update触发器
- Sql据多个条件 查询每个条件的前N条记录
- 我在设置sql server ODBC时,提示拒绝连接是什么原因。
- 关于空数据的求和
- 这是二个批处理的问题,请大家帮助!其一是在一个名为EMP_no的表中插入3000行,emp_no为1-3000,且为唯一值....详情请点击谢谢!
- 请帮忙优化一条sql语句!谢谢!
- 请教一条SQL语句的写法!
rdr.PersonCode,Person.PersonName,
rdr.SHPersonCode,
(select Person.PersonName from Person where PersonCode = rdr.PersonCode and rdr.[ID]=1) as SHPersonName
FROM RdRecord2 rdr
LEFT JOIN Person ON rdr.PersonCode=Person.PersonCode
WHERE rdr.[ID]=1
[ID] [int] NOT NULL,
[PersonCode] [char] (10) NULL,
[SHPersonCode] [char] (10) NULL)ALTER TABLE [RdRecord2] WITH NOCHECK ADD CONSTRAINT [PK_RdRecord2] PRIMARY KEY NONCLUSTERED ( [ID] )
INSERT [RdRecord2] ([ID],[PersonCode],[SHPersonCode]) VALUES ( 1,'001','002')
INSERT [RdRecord2] ([ID],[PersonCode],[SHPersonCode]) VALUES ( 2,'001','003')CREATE TABLE [Person] (
[PersonCode] [char] (10) NOT NULL,
[PersonName] [char] (50) NULL)ALTER TABLE [Person] WITH NOCHECK ADD CONSTRAINT [PK_Person] PRIMARY KEY NONCLUSTERED ( [PersonCode] )
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '001','人员1')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '002','人员2')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '003','人员3')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '004','人员4')SELECT rdr.[ID],
rdr.PersonCode,Person.PersonName,
rdr.SHPersonCode,
(select Person.PersonName from Person where PersonCode = rdr.PersonCode and rdr.[ID]=1) as SHPersonName
FROM RdRecord2 rdr
LEFT JOIN Person ON rdr.PersonCode=Person.PersonCode
WHERE rdr.[ID]=1--drop table rdrecord2,person/*
ID PersonCode PersonName SHPersonCode SHPersonName
----------- ---------- -------------------------------------------------- ------------ --------------------------------------------------
1 001 人员1 002 人员1 (所影响的行数为 1 行)*/
rdr.SHPersonCode,P.PersonName as SHPersonName ---这里别名写错了 FROM RdRecord2 rdr
LEFT JOIN Person ON rdr.PersonCode=Person.PersonCode
LEFT JOIN Person p ON rdr.SHPersonCode=p.PersonCode
WHERE rdr.[ID]=1
SELECT rdr.[ID], rdr.PersonCode,P1.PersonName,
rdr.SHPersonCode,P2.PersonName as SHPersonName FROM RdRecord2 rdr
LEFT JOIN Person p1 ON rdr.PersonCode=P1.PersonCode
LEFT JOIN Person p2 ON rdr.SHPersonCode=p2.PersonCode
WHERE rdr.[ID]=1
CREATE TABLE [RdRecord2] (
[ID] [int] NOT NULL,
[PersonCode] [char] (10) NULL,
[SHPersonCode] [char] (10) NULL)ALTER TABLE [RdRecord2] WITH NOCHECK ADD CONSTRAINT [PK_RdRecord2] PRIMARY KEY NONCLUSTERED ( [ID] )
INSERT [RdRecord2] ([ID],[PersonCode],[SHPersonCode]) VALUES ( 1,'001','002')
INSERT [RdRecord2] ([ID],[PersonCode],[SHPersonCode]) VALUES ( 2,'001','003')CREATE TABLE [Person] (
[PersonCode] [char] (10) NOT NULL,
[PersonName] [char] (50) NULL)ALTER TABLE [Person] WITH NOCHECK ADD CONSTRAINT [PK_Person] PRIMARY KEY NONCLUSTERED ( [PersonCode] )
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '001','人员1')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '002','人员2')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '003','人员3')
INSERT [Person] ([PersonCode],[PersonName]) VALUES ( '004','人员4')SELECT rdr.[ID],
rdr.PersonCode,Person.PersonName,
rdr.SHPersonCode,
(select Person.PersonName from Person left join rdrecord2 on Person.PersonCode = rdrecord2.PersonCode where rdrecord2.[ID]=1) as SHPersonName
FROM RdRecord2 rdr
LEFT JOIN Person ON rdr.PersonCode=Person.PersonCode
WHERE rdr.[ID]=1--drop table rdrecord2,person/*
ID PersonCode PersonName SHPersonCode SHPersonName
----------- ---------- -------------------------------------------------- ------------ --------------------------------------------------
1 001 人员1 002 人员1 (所影响的行数为 1 行)*/
的查询结果有错误,具体是
应该是:
/*
ID PersonCode PersonName SHPersonCode SHPersonName
----------- ---------- -------------------------------------------------- ------------
1 001 人员1 002 人员2 --这里是002的姓名
SELECT rdr.[ID], rdr.PersonCode,P1.PersonName,
rdr.SHPersonCode,p2.PersonName as SHPersonName FROM RdRecord2 rdr
LEFT JOIN Person p1 ON rdr.PersonCode=Person.PersonCode
LEFT JOIN Person p2 ON rdr.SHPersonCode=p.PersonCode
WHERE rdr.[ID]=1
/*
ID PersonCode PersonName SHPersonCode SHPersonName
----------- ---------- -------------------------------------------------- ------------ --------------------------------------------------
1 001 人员1 002 人员2
*/
rdr.SHPersonCode,p2.PersonName as SHPersonName FROM RdRecord2 rdr
LEFT JOIN Person p1 ON rdr.PersonCode=P1.PersonCode
LEFT JOIN Person p2 ON rdr.SHPersonCode=p2.PersonCode
WHERE rdr.[ID]=1
/*
ID PersonCode PersonName SHPersonCode SHPersonName
----------- ---------- -------------------------------------------------- ------------ --------------------------------------------------
1 001 人员1 002 人员2
*/