SQL Join语句问题 [英] SQL Join Statement problem
问题描述
我有以下SQL语句，它从
数据库中提取一些细节。正如你所看到的那样，只有一张表我可以创建一个临时副本。
我这样做的原因是因为在表中我只有''standIn''
按整数列出，我希望按名称返回''standIn''。
我希望这很清楚。
该语句有效但我现在注意到它在SQL分析器中列出了多个
返回值，例如它列出了三个不同的行为一个
用户和这些已经超过了相关用户的StandIns。
目前这不是问题，但它可能是，我想
知道为什么这样做呢。我可以更改声明来阻止这个，我的b $ b一直在弄乱连接部分，但没有运气。
任何帮助都非常感谢。
------------------------------------------ -----------
SELECT T2.FirstName AS StandIn_FirstName，T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff作为T2
ON T1.StaffNo = T2.StandIn
WHERE（T1.NTUserName =''auser'' ）
Hi,
I have the following SQL statement which is pulling a few details from
a database. As you can see, there is only the one table from which i
am creating a temporary copy.
The reason I do this is because in the table i only have the ''standIn''
listed by integer and i want to return the ''standIn'' by name.
I hope this is clear enough.
The statement works but i am now noticing that it lists multiple
returns in SQL Analyser e.g it is listing three different rows for one
user and these have all been past StandIns for the user in question.
It is not a problem at the moment but it may be and i would like to
know why it is doing this. Can i change the statement to stop this, i
have been messing with the join part but no luck.
Any help greatly appreciated.
-----------------------------------------------------
SELECT T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.NTUserName = ''auser'')
推荐答案
2004年8月11日04:57:11 -0700，kieran写道：
On 11 Aug 2004 04:57:11 -0700, kieran wrote:
你好，
我有以下SQL语句，它从数据库中提取一些细节。正如你所看到的，只有一张表我正在创建一个临时副本。
我这样做的原因是因为在表格中我只有''standIn''
按整数列出，我希望按名称返回''standIn''。
我希望这很清楚。
声明有效，但我现在注意到它在SQL分析器中列出了多个
返回值，例如它为一个
用户列出了三个不同的行，这些行已经超过了相关用户的StandIns。
目前这不是问题但它可能是，我想知道它为什么这样做。我可以改变声明来阻止它，我一直在搞乱连接部分，但没有运气。
任何帮助都非常感激。
---- -------------------------------------------------
SELECT T2.FirstName AS StandIn_FirstName，T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE（T1.NTUserName =''auser''）
Hi,
I have the following SQL statement which is pulling a few details from
a database. As you can see, there is only the one table from which i
am creating a temporary copy.
The reason I do this is because in the table i only have the ''standIn''
listed by integer and i want to return the ''standIn'' by name.
I hope this is clear enough.
The statement works but i am now noticing that it lists multiple
returns in SQL Analyser e.g it is listing three different rows for one
user and these have all been past StandIns for the user in question.
It is not a problem at the moment but it may be and i would like to
know why it is doing this. Can i change the statement to stop this, i
have been messing with the join part but no luck.
Any help greatly appreciated.
-----------------------------------------------------
SELECT T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.NTUserName = ''auser'')
嗨Kieran，
在不了解数据库中的表结构和数据的情况下很难说清楚。请发布以下内容：
1.表结构，以DDL的形式（CREATE TABLE语句，包括
所有约束;不相关的列可能省略） ;
2.样本数据（以INSERT统计的形式）;
3.根据提供的样本数据，您希望看到的输出;
4.您正试图解决的业务问题的描述。
Best，Hugo
-
（删除_NO_和_SPAM_以获取我的电子邮件地址）
Hi Kieran,
Hard to tell without knowing anything about the table structure and data
in your database. Please post the following:
1. Table structure, in the form of DDL (CREATE TABLE statements, including
all constraints; irrelevant columns may be omitted);
2. Sample data (in the form of INSERT stattements);
3. The output you''d like to see, based on the sample data provided;
4. A description of the business problem you''re trying to solve.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
你好雨果，
这里是创建表，更新和选择语句 - 我不确定
你的意思是ddl，但你需要的所有项目测试它是
这里。
这对我来说真的很令人沮丧，因为它返回一行，其中
是select语句。但是当你在这之后进行更新
（更改standIn整数）时，它有时会返回两行。它确实没有遵循某种模式。
似乎没有遵循某种模式。如果你把所有的更新放在一起然后做一个选择，它就不会这样做。做一个更新，然后
尝试选择语句有时会带回一行，有时候会回到
两个。
也许我失踪了一些非常明显的东西
感谢您的帮助。
------------------ -------------------
CREATE TABLE [dbo]。[tblStaff]（
[ StaffNo] [int] IDENTITY（1,1）NOT NULL，
[FirstName] [varchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[LastName] [varchar]（ 50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[StandIn] [int] NULL，
）ON [PRIMARY]
GO
-------------------------------------
插入tblstaff
值（''fname1''，''lname2''，2）
插入tblstaff
值（''fname1''，''lname2''，1）
插入tblstaff
值（''fname1''，'' lname2''，1）
----做个人在这之后就这样了！
UPDATE tblstaff
SET StandIn = 3
WHERE StaffNo = 2
------------------------------------
SELECT T2.FirstName AS StandIn_FirstName，T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE（T1.StaffNo = 2）
------------- ------------------------
UPDATE tblstaff
SET StandIn = 3
WHERE StaffNo = 2
***通过Developersdex发送 http://www.developersdex.com ***
不要只是参加USENET ......获得奖励！
Hi Hugo,
Here is the create table, update and select statements - im not sure
what u mean by ddl, but all the staements you will need to test it are
here.
This is really frustrating me now because it returns a single row with
the select statement. However when you do an update after this
(changing the standIn integer) it returns two rows sometimes. It does
not seem to follow a certain pattern. It doesnt do it if you put all
the updates in together and then do a select. Doing an update and then
trying the select statement sometimes brings back one row and sometimes
two.
Maybe I am missing something really obvious
Thanks for all help.
-------------------------------------
CREATE TABLE [dbo].[tblStaff] (
[StaffNo] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StandIn] [int] NULL ,
) ON [PRIMARY]
GO
-------------------------------------
Insert into tblstaff
values (''fname1'', ''lname2'', 2)
Insert into tblstaff
values (''fname1'', ''lname2'', 1)
Insert into tblstaff
values (''fname1'', ''lname2'', 1)
----Do individually after here----
UPDATE tblstaff
SET StandIn = 3
WHERE StaffNo = 2
------------------------------------
SELECT T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.StaffNo = 2)
-------------------------------------
UPDATE tblstaff
SET StandIn = 3
WHERE StaffNo = 2
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
kieran h（ki********@hotmail.com）写道：
kieran h (ki********@hotmail.com) writes:
这是创建表，更新和选择语句 - im不确定你是什么意思ddl，但所有你需要测试它的标志是
。
DDL =数据定义语言。雨果还没有学会用较少的经验丰富的用户说话。或者他只是势利。无论如何，CREATE TABLE
就是他所追求的，所以你把它做到了很远。
现在这真让我感到沮丧，因为它返回了一行
select语句。但是当你在此之后进行更新
（更改standIn整数）时，它有时会返回两行。它似乎没有遵循某种模式。如果您将所有更新放在一起然后进行选择，它就不会这样做。做一个更新，然后尝试选择语句有时会带回一行，有时候会返回两行。
Here is the create table, update and select statements - im not sure
what u mean by ddl, but all the staements you will need to test it are
here.
DDL = Data Definition Language. Hugo hasn''t learnt to speak to less
experienced users. Or he just being snobbish. Anyway, CREATE TABLE
is what he was after, so you got it right to far.
This is really frustrating me now because it returns a single row with
the select statement. However when you do an update after this
(changing the standIn integer) it returns two rows sometimes. It does
not seem to follow a certain pattern. It doesnt do it if you put all
the updates in together and then do a select. Doing an update and then
trying the select statement sometimes brings back one row and sometimes
two.
是的，但规则就是你提供演示
问题的脚本，并将分析留给小组。另外，对于一个像这样的情况，这是一个好主意，提供所需的输出。
另外，为了这个例子，它'可能最好没有
StaffNo作为IDENTITY列，所以你知道哪个值是哪个。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
用于SQL Server SP3的联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于SQL Join语句问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
