Question

我需要从询问中回来的是去年访问过的家庭户主及其所有亲属的名单。每个人都在客户表中,在被询问时,会与其他表格合并。

问题是,每有一个独一无二的日期,家庭成员就返回“一类”。我希望获得一系列类似结果(我略去一些栏目来澄清):

Head of household                Relative/child
----------------------------     ---------------------
John Smith -                     Katie Smith
John Smith                       Joe Smith
Tim Jones                        Mike Jones
Tim Jones                        Sally Jones
Kevin Barnett                    Corey Barnett
Kevin Barnett                    Cara Barnett
Kevin Barnett                    Austin Barnett




SELECT f.HeadOfHouseholdID,
  CONCAT( c.lastname,   ,  , c.firstname ) AS   HName ,
  v.Datevisited, f.RelationshipID, c2.ClientID,
  CONCAT( c2.lastname,   ,  , c2.firstname ) AS   Relative Name 
FROM client c
INNER JOIN clientfamily f ON c.ClientID = f.HeadOfHouseholdID
JOIN visits v ON c.clientid = v.clientID
JOIN client c2 ON c2.clientid = f.relativeID
WHERE v.datevisited BETWEEN CURDATE( ) - INTERVAL 1 YEAR AND CURDATE( )

! [成果] 页: 1

Answer 1

一位同事表示:

SELECT f.HeadOfHouseholdID  HOHID ,
    CONCAT( c.Lastname,  ,  , c.FirstName ) AS  Head of Household ,
    c.PhoneNumber AS  HOH Phone ,
    f.RelativeID,
    CONCAT( C2.Lastname,  ,  , C2.FirstName ) AS  Relative ,
    Relationship.Description AS  Relation ,
    C2.PhoneNumber AS  Relative Phone , C2.DOB,
    (
            (
            DATE_FORMAT( NOW( ) ,  %Y  ) - DATE_FORMAT( C2.DOB,  %Y  )
            ) -
            ( DATE_FORMAT( NOW( ) ,  00-%m-%d  ) < DATE_FORMAT( C2.DOB,  00-%m-%d  ) )
    ) AS Age
    FROM client c
    INNER JOIN clientfamily f ON c.clientid = f.headofhouseholdid
    JOIN (
            SELECT ClientID, MAX( DateVisited ) AS  DateVisited 
            FROM Visits
            GROUP BY ClientID
    ) v ON c.clientid = v.clientid
    JOIN client c2 ON c2.clientid = f.relativeid
    JOIN Relationship ON f.RelationshipID = Relationship.RelationshipID
    WHERE v.datevisited
            BETWEEN CURDATE( ) - INTERVAL 1 YEAR
            AND CURDATE( )

友情链接