group by having 을 이용하여 그룹별 최대,최소 하나씩 가져 올수있는 방법

두 테이블에서 가장 최근에 입사한 사람 가져오기

 

#
# Table structure for table 'member'
#

CREATE TABLE member (
    t_code tinyint(3) unsigned NOT NULL default '0',
    m_code tinyint(3) unsigned NOT NULL default '0',
    m_name varchar(100) default NULL,
    m_date datetime default NULL,
    PRIMARY KEY (m_code,t_code),
    UNIQUE KEY NewIndex (m_date)
) TYPE=MyISAM;

 

#
# Dumping data for table 'member'
#

INSERT INTO member (t_code, m_code, m_name, m_date) VALUES("1", "1", "홍길동", "2004-12-13 09:10:05");
INSERT INTO member (t_code, m_code, m_name, m_date) VALUES("1", "2", "이순신", "2003-05-10 10:35:10");
INSERT INTO member (t_code, m_code, m_name, m_date) VALUES("2", "1", "이영희", "2003-12-01 12:20:34");
INSERT INTO member (t_code, m_code, m_name, m_date) VALUES("2", "2", "이나영", "2004-10-10 10:10:10");
INSERT INTO member (t_code, m_code, m_name, m_date) VALUES("3", "1", "강동원", "2001-03-14 11:15:35");
INSERT INTO member (t_code, m_code, m_name, m_date) VALUES("3", "2", "강호동", "2001-03-14 11:15:39");

#
# Table structure for table 'team'
#

CREATE TABLE team (
    t_code tinyint(3) unsigned NOT NULL default '0',
    t_name varchar(100) default NULL,
    PRIMARY KEY (t_code)
) TYPE=MyISAM;

 

#
# Dumping data for table 'team'
#

INSERT INTO team (t_code, t_name) VALUES("1", "프로그램팀");
INSERT INTO team (t_code, t_name) VALUES("2", "디자인팀");
INSERT INTO team (t_code, t_name) VALUES("3", "기획팀");

######################## 문제
team 테이블
t_code  t_name
1         프로그램팀
2         디자인팀
3         기획팀

member 테이블
t_code  m_code  m_name m_date
1          1           홍길동     2004-12-13 09:10:05
1          2           이순신     2003-05-10 10:35:10
2          1           이영희     2003-12-01 12:20:34
2          2           이나영     2004-10-10 10:10:10
3          1           강동원     2001-03-14 11:15:35
3          2           강호동     2001-03-14 11:15:39

각 팀별로 최근 입사일 1명씩 구하시오 (결과는 다음과 같이)
t_name       m_name m_date
프로그램팀  홍길동    2004-12-13 09:10:05
디자인팀     이나영    2004-10-10 10:10:10
기획팀        강호동    2001-03-14 11:15:39

 

 

 

========================================================================

 

######################## 해답
select
    a.t_name,
    b.m_name,
    b.m_date
from
    team a,
    member b,
    member c
where
    a.t_code = b.t_code
    and a.t_code = c.t_code
    group by a.t_name, b.m_code having b.m_date = max(c.m_date)