DISTINCT子句未按预期工作[已关闭]

G Davies 05/11/2018. 3 answers, 377 views
sql-server t-sql distinct

我正在使用以下声明:

SELECT
     Pupils.txtSchoolID
    ,AddressLink.intAddressID
    ,Addresses.txtLabelSalutation
    ,Addresses.intDeceased
    ,Addresses.intSecondaryDeceased
    ,AddressLink.intAddressID
FROM iSAMS.dbo.TblPupilManagementPupils Pupils
JOIN iSAMS.dbo.TblPupilManagementAddressLink AddressLink
    ON Pupils.txtSchoolID = AddressLink.txtSchoolID
JOIN iSAMS.dbo.TblPupilManagementAddresses Addresses 
    ON AddressLink.intAddressID = Addresses.TblPupilManagementAddressesID
WHERE Pupils.intSystemStatus = 1 
   AND EXISTS (
       SELECT DISTINCT intAddressID
       FROM TblPupilManagementAddressLink
       ) 

我试图只返回唯一的intAddressID但这不起作用。 我得到的输出的一个例子是(为了清楚起见,我列出了一些列):

txtLabelSalutation          intAddressID

Mr S Galvin  & Ms A Boe     7271
Mr A Gibson                 7272
Mrs V Gibson                7273
Mr and Mrs D Ip             7275
**Mr and Mrs L Medlock      7280**
**Mr and Mrs L Medlock      7280**
Mr and Mrs F Prego          7285
**Mr and Mrs J Charlesworth 7288**
**Mr and Mrs J Charlesworth 7288**
**Mr and Mrs J Tanner       7291**
**Mr and Mrs J Tanner       7291**
Mr and Mrs N Terry          7292
Mr and Mrs G Van Dort       7293 

我尝试过各种方法来尝试使用GROUP BYIN来工作,但他们都没有给我想要的结果。

Edit, to add a bit of clarity:

StudentID都是唯一的,每个地址都有一个唯一的AddressID ,但是多个学生(兄弟姐妹)可以分配相同的AddressID

我用多种方式修改了我的查询中的连接,并且无法获得产生所需结果的语句。 问题是数据库使用链接表将TblPupilManagementPupils连接到TblPupilManagementAddresses

Pupil表存储每个学生的活动AddressID ,所以如果有兄弟姐妹, AddressID将与每个唯一的StudentID重复。 Address表没有用于非活动地址的标志,它使用到Pupil表的链接。

链接表只有两个属性: txtSchoolIDintAddressID 。 要获得活动地址,请使用WHERE Pupils.intSystemStatus = 1 ,因为这只会返回活动学生和分配给他们的AddressID

3 Answers


SQLRaptor 05/11/2018.

你的EXISTS条款没有意义。 子查询: SELECT DISTINCT tAddressID FROM TblPupilManagementAddressLink与父查询不相关,因此将仅评估一次。 如果TblPupilManagementAddressLink中存在一行,则对于所有行,EXISTS将为TRUE,如果不是,则根本不会返回任何行。

因为你没有提供足够的背景知识,所以很难说出你在这里寻找什么。 如果你所要做的只是从父查询中删除重复的行,试试这个:

SELECT DISTINCT
        FROM iSAMS.dbo.TblPupilManagementPupils Pupils
  INNER JOIN [iSAMS].[dbo].TblPupilManagementAddressLink AddressLink ON Pupils.txtSchoolID = AddressLink.txtSchoolID
  INNER JOIN [iSAMS].[dbo].TblPupilManagementAddresses Addresses ON AddressLink.intAddressID = Addresses.TblPupilManagementAddressesID
  WHERE Pupils.intSystemStatus = 1 

Evan Carroll 05/11/2018.

这里的问题是你的重复行来自JOIN而不是你的屠杀DISTINCT语句..你需要解决这个问题。

INNER JOIN [iSAMS].[dbo].TblPupilManagementAddressLink AddressLink ON Pupils.txtSchoolID = AddressLink.txtSchoolID
INNER JOIN [iSAMS].[dbo].TblPupilManagementAddresses Addresses ON AddressLink.intAddressID = Addresses.TblPupilManagementAddressesID 

如果有两个,你会得到一个重复的行

  • 您的AddressLink.txtSchoolID Pupils.txtSchoolID
  • Addresses.TblPupilManagementAddressesID用于AddressLink.intAddressID

为了确保你理解这一点,请看下面的内容

SELECT *
FROM ( VALUES (1) ) AS t(x)
INNER JOIN ( VALUES
  (1,4),
  (1,5)
) AS g(x,y)
  ON t.x = g.x;

x   x   y
1   1   4
1   1   5 

dbfiddle

EXISTS的模式也是

AND EXISTS (
  SELECT 1
  FROM t2
  WHERE t1.foo = t2.foo
) 

这具有eliminating上述效果的优点,但其代价在于来自联接的数据被丢弃并且仅用于选择性。 在上面的例子中,你不会在select语句的t2中访问数据(或任何行)。

如果你想访问JOIN的产品,你必须JOIN 而且,在没有重复的情况下这样做会require您指示SQL,以确定要JOIN哪一行


G Davies 05/16/2018 at 16:57.

感谢大家的帮助,非常感谢。

我找到了一个简单的答案:我从SELECT语句中取出了txt.SchoolID ,现在它只返回唯一的AddressID的现场学生。

以下是工作说明:

SELECT DISTINCT 
       AddressLink.intAddressID, Addresses.txtLabelSalutation, Addresses.txtAddress1,
       Addresses.txtAddress2, Addresses.txtAddress3, Addresses.txtTown, 
       Addresses.txtCounty, Addresses.txtCountry, Addresses.txtPostcode,
       Addresses.txtRelationType, Pupils.intNCYear, Pupils.txtSchoolID,
       Pupils.txtForename, Pupils.txtSurname
FROM   TblPupilManagementAddresses AS Addresses
         INNER JOIN TblPupilManagementAddressLink AS AddressLink
               ON Addresses.TblPupilManagementAddressesID = AddressLink.intAddressID
         LEFT OUTER JOIN TblPupilManagementPupils AS Pupils
               ON AddressLink.txtSchoolID = Pupils.txtSchoolID
WHERE  (Pupils.intSystemStatus = 1)
AND    (Addresses.txtAddress1 <> '')
AND    (Addresses.txtRelationType <> 'OLD Billing')
AND    (Addresses.intJustContact <> 1) 

Related questions

Hot questions

Language

Popular Tags