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.

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

我找到了一个简单的答案:我从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) 

HighResolutionMusic.com - Download Hi-Res Songs

1 Alan Walker

Diamond Heart flac

Alan Walker. 2018. Writer: Alan Walker;Sophia Somajo;Mood Melodies;James Njie;Thomas Troelsen;Kristoffer Haugan;Edvard Normann;Anders Froen;Gunnar Greve;Yann Bargain;Victor Verpillat;Fredrik Borch Olsen.
2 Sia

I'm Still Here flac

Sia. 2018. Writer: Sia.
3 Blinders

Breach (Walk Alone) flac

Blinders. 2018. Writer: Dewain Whitmore;Ilsey Juber;Blinders;Martin Garrix.
4 Halsey

Without Me flac

Halsey. 2018. Writer: Halsey;Delacey;Louis Bell;Amy Allen;Justin Timberlake;Timbaland;Scott Storch.
5 Cardi B

Taki Taki flac

Cardi B. 2018. Writer: Bava;Juan Vasquez;Vicente Saavedra;Jordan Thorpe;DJ Snake;Ozuna;Cardi B;Selena Gomez.
6 Bradley Cooper

Shallow flac

Bradley Cooper. 2018. Writer: Andrew Wyatt;Anthony Rossomando;Mark Ronson;Lady Gaga.
7 Martin Garrix

Yottabyte flac

Martin Garrix. 2018. Writer: Martin Garrix.
8 Little Mix

Woman Like Me flac

Little Mix. 2018. Writer: Nicki Minaj;Steve Mac;Ed Sheeran;Jess Glynne.
9 Lady Gaga

I'll Never Love Again flac

Lady Gaga. 2018. Writer: Benjamin Rice;Lady Gaga.
10 Kelsea Ballerini

This Feeling flac

Kelsea Ballerini. 2018. Writer: Andrew Taggart;Alex Pall;Emily Warren.
11 Mako

Rise flac

Mako. 2018. Writer: Riot Music Team;Mako;Justin Tranter.
12 Bradley Cooper

Always Remember Us This Way flac

Bradley Cooper. 2018. Writer: Lady Gaga;Dave Cobb.
13 Dewain Whitmore

Burn Out flac

Dewain Whitmore. 2018. Writer: Dewain Whitmore;Ilsey Juber;Emilio Behr;Martijn Garritsen.
14 Charli XCX

1999 flac

Charli XCX. 2018. Writer: Charli XCX;Troye Sivan;Leland;Oscar Holter;Noonie Bao.
15 Deep Chills

Run Free flac

Deep Chills. 2018.
16 Rita Ora

Let You Love Me flac

Rita Ora. 2018. Writer: Rita Ora.
17 Avril Lavigne

Head Above Water flac

Avril Lavigne. 2018. Writer: Stephan Moccio;Travis Clark;Avril Lavigne.
18 Khalid

Better flac

Khalid. 2018. Writer: Charlie Handsome;Jamil Chammas;Denis Kosiak;Tor Erik Hermansen;Mikkel Stoleer Eriksen;Khalid.
19 Diplo

Electricity flac

Diplo. 2018. Writer: Diplo;Mark Ronson;Picard Brothers;Wynter Gordon;Romy Madley Croft;Florence Welch.
20 Julia Michaels

There's No Way flac

Julia Michaels. 2018. Writer: Ian Kirkpatrick;Justin Tranter;Julia Michaels;Lauv.

Related questions

Hot questions

Language

Popular Tags