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

G Davies 05/11/2018. 3 answers, 377 views

SELECT
Pupils.txtSchoolID
FROM iSAMS.dbo.TblPupilManagementPupils Pupils
WHERE Pupils.intSystemStatus = 1
AND EXISTS (
) 

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 

Edit, to add a bit of clarity:

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

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

SQLRaptor 05/11/2018.

SELECT DISTINCT
FROM iSAMS.dbo.TblPupilManagementPupils Pupils
WHERE Pupils.intSystemStatus = 1 

Evan Carroll 05/11/2018.

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 

EXISTS的模式也是

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

G Davies 05/16/2018.

SELECT DISTINCT
Pupils.txtForename, Pupils.txtSurname
LEFT OUTER JOIN TblPupilManagementPupils AS Pupils
WHERE  (Pupils.intSystemStatus = 1)
AND    (Addresses.intJustContact <> 1)