Question

我对这一点如此新,甚至对与我类似的问题的答复,对我来说确实没有任何意义(我上周开始)。

我试图从表1中列出地址,每个地址都有多个地址,有相同的第一和姓(即John Smith和John Smith)。我在不同的领域有第一名和姓。

审判

SELECT *  
FROM addresses  
WHERE EXISTS (  
    SELECT individuals.FirstName  
    AND individuals.Surname  
    FROM individuals  
    WHERE addresses.AddressID = individuals.AddressID  
    GROUP BY addresses.StreetName  
    HAVING COUNT( * ) >1`

but this just gives me a list of every address that has more than one person in it.. Can anyone give me a (simpleish) answer that I might get my head around. Thanks, Stacey

Answer 1

How about:

SELECT 
    a.*

FROM 
    addresses a
        JOIN
    individuals i
    ON a.AddressID = i.AddressID

GROUP BY
    a.StreetName, i.FirstName, i.Surname

HAVING
    COUNT(1) > 1

Answer 2

如果是,

individuals.FirstName, individuals.Surname

之后

GROUP BY addresses.StreetName

那么,这应当做你们想要做的事情。

Answer 3

SELECT addresses.* 
FROM addresses
INNER JOIN
(
    SELECT AddressID 
    FROM individuals
    GROUP BY AddressID, FirstName, Surname
    HAVING COUNT(*) > 1
) as tbl1
ON tbl1.AddressID = addresses.AddressID

友情链接