Início / dba / Perguntas / 29789
Accepted
Jonlabr
Asked: 2012-12-05 08:08:58 +0800 CST

Como agrupar com min(date) e selecionar outra coluna na mesma tabela

  • 772

Eu tenho uma consulta que retorna algo assim:

Name   Gender Job         date of hire
John   M      mechanic    2012-05-08
John   M      electrician 2010-01-01
Vicky  F      scientific  2012-11-11
Bob    M      NULL        NULL

Eu preciso ter o nome, sexo e nome do trabalho do primeiro emprego de cada pessoa. Mas eu não sei como. Minha consulta está assim:

select name,gender,jobname,hiredate
from person p
left join job j on p.personid = j.personid

Estou no Microsoft SQL Server 2000

Eu preciso disso como resultado:

Name   Gender Job         
John   M      electrician
Vicky  F      scientific 
Bob    M      NULL
sql-server-2000 group-by

2 respostas

  1. Best Answer

    Eu deduzo que seus dados são assim:

    Tabela de pessoas

    ╔══════════╦═══════╦════════╗
║ PersonID ║ Name  ║ Gender ║
╠══════════╬═══════╬════════╣
║        1 ║ John  ║ M      ║
║        2 ║ Vicky ║ F      ║
║        3 ║ Bob   ║ M      ║
╚══════════╩═══════╩════════╝

    Tabela de Trabalho

    ╔══════════╦═════════════╦════════════╗
║ PersonID ║   JobName   ║  HireDate  ║
╠══════════╬═════════════╬════════════╣
║        1 ║ Electrician ║ 2010-01-01 ║
║        1 ║ Mechanic    ║ 2012-05-08 ║
║        2 ║ Scientific  ║ 2012-11-11 ║
╚══════════╩═════════════╩════════════╝

    A primeira tarefa é encontrar o primeiro emprego (por data de contratação) para cada pessoa. Uma maneira legal de fazer isso é usando uma subconsulta correlacionada:

    SELECT j.*
FROM dbo.Job AS j
WHERE 
    j.HireDate =
    (
        SELECT MIN(j2.HireDate)
        FROM dbo.Job AS j2
        WHERE j2.PersonID = j.PersonID
    );

    Observe a correlação WHERE j2.PersonID = j.PersonIDentre as consultas internas e externas. A saída dessa consulta é:

    ╔══════════╦═════════════╦════════════╗
║ PersonID ║   JobName   ║  HireDate  ║
╠══════════╬═════════════╬════════════╣
║        1 ║ Electrician ║ 2010-01-01 ║
║        2 ║ Scientific  ║ 2012-11-11 ║
╚══════════╩═════════════╩════════════╝

    O plano de execução (dado um cluster PRIMARY KEYem PersonID, HireDate) é:

    Primeiro plano de trabalho

    O interessante desse plano é que a tabela Job só é verificada uma vez, apesar de haver duas referências a ela na consulta original. O plano usa uma otimização que chamo de Segmento Top . Essencialmente, o mecanismo de execução aproveita a ordem do índice para detectar o início de um novo grupo (segmento) e obter apenas a primeira linha de cada grupo (parte superior).

    Agora que temos esse resultado, tudo o que precisamos fazer é juntá-lo de volta à tabela Person:

    SELECT
    p.PersonName,
    p.Gender, 
    j.JobName
FROM dbo.Person AS p
LEFT JOIN
(
    -- Previous query
    SELECT j.*
    FROM dbo.Job AS j
    WHERE 
        j.HireDate =
        (
            SELECT MIN(j2.HireDate)
            FROM dbo.Job AS j2
            WHERE j2.PersonID = j.PersonID
        )
) AS j ON 
    j.PersonID = p.PersonID
OPTION (MERGE JOIN);

    O plano de execução é:

    Plano final

    O OPTION (MERGE JOIN)não é necessário; Acabei de adicioná-lo para mostrar o plano que você provavelmente obterá quando as tabelas contiverem um número maior de linhas do que neste pequeno exemplo.

    Definições de tabela e dados de amostra:

    CREATE TABLE dbo.Person
(
    PersonID    integer NOT NULL,
    PersonName  varchar(30) NOT NULL,
    Gender      char(1) NOT NULL,

    PRIMARY KEY (PersonID)
);

CREATE TABLE dbo.Job
(
    PersonID    integer NOT NULL,
    JobName     varchar(30) NOT NULL,
    HireDate    datetime NOT NULL,

    PRIMARY KEY (PersonID, HireDate)
);

INSERT dbo.Person
    (PersonID, PersonName, Gender)
SELECT 1, 'John', 'M' UNION ALL
SELECT 2, 'Vicky', 'F' UNION ALL
SELECT 3, 'Bob', 'M';

INSERT dbo.Job
    (PersonID, JobName, HireDate)
SELECT 1, 'Mechanic', '20120508' UNION ALL
SELECT 1, 'Electrician', '20100101' UNION ALL
SELECT 2, 'Scientific', '20121111';
    • 10

  2. Cometi um erro na consulta no comentário. Esqueci o gênero no grupo por. Esta consulta funciona para mim:

        select name,gender,SUBSTRING(job, 9, 50) as 'jobname'
    from(
        select name,gender,min(CONVERT(CHAR(8), hiredate, 112)+jobname) as'job' 
        from person p 
        left join job j on p.personid = j.personid 
        group by name,gender
     ) as query`
    • 1

relate perguntas