代码之家 › 专栏 › 技术社区 › Rodrick

如何从mysql上的每个类别中获取最大id?

mysql

Rodrick · 技术社区 · 7 年前

+-----------+---------+-------------------+-----------+-------------+----------+
| PERSON_ID | CERT_ID | CERTIFICATION     | STATE     | EXPIRE_DATE | VALIDITY |
+-----------+---------+-------------------+-----------+-------------+----------+
| 111       | 53961   | CERTIFICATION ONE | DELIVERED | 09-05-2018  | EXPIRED  |
+-----------+---------+-------------------+-----------+-------------+----------+
| 111       | 88196   | CERTIFICATION ONE | DELIVERED | 16-03-2020  | VALID    |
+-----------+---------+-------------------+-----------+-------------+----------+
| 111       | 96943   | CERTIFICATION TWO | DELIVERED | 04-12-2020  | VALID    |
+-----------+---------+-------------------+-----------+-------------+----------+

在这种情况下,我希望得到以下结果:

+-----------+---------+-------------------+-----------+-------------+----------+
| PERSON_ID | CERT_ID | CERTIFICATION     | STATE     | EXPIRE_DATE | VALIDITY |
+-----------+---------+-------------------+-----------+-------------+----------+
| 111       | 88196   | CERTIFICATION ONE | DELIVERED | 16-03-2020  | VALID    |
+-----------+---------+-------------------+-----------+-------------+----------+
| 111       | 96943   | CERTIFICATION TWO | DELIVERED | 04-12-2020  | VALID    |
+-----------+---------+-------------------+-----------+-------------+----------+

更多信息:有时有效期可能全部过期或对每种证书有效取决于每个证书的过期日期。

更新:这是我要找的东西的简化。这是我使用的实际查询,但只显示一个结果,因为我使用的是max\u id。不知道如何按证书正确分组。

SELECT 
    PERSON_ID,
    WORKER,
    job,
    MAX(CRED_ID) AS CRED_ID,
    CREDENTIAL,
    date_1,
    date_2,
    date_3,
    IF(date_2 = 'Not requerided',
        IF(STR_TO_DATE(date_1, '%d-%m-%Y') <= STR_TO_DATE(date_3, '%d-%m-%Y'),
            date_1,
            date_3),
        CASE
            WHEN
                STR_TO_DATE(date_1, '%d-%m-%Y') <= STR_TO_DATE(date_2, '%d-%m-%Y')
                    AND STR_TO_DATE(date_1, '%d-%m-%Y') <= STR_TO_DATE(date_3, '%d-%m-%Y')
            THEN
                date_1
            WHEN
                STR_TO_DATE(date_2, '%d-%m-%Y') <= STR_TO_DATE(date_1, '%d-%m-%Y')
                    AND STR_TO_DATE(date_2, '%d-%m-%Y') <= STR_TO_DATE(date_3, '%d-%m-%Y')
            THEN
                date_2
            ELSE date_3
        END) AS END_DATE,
    STATUS
FROM
    (SELECT 
        t.PERSON_ID,
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT(t.NAME, ' ', t.LASTNAME), 'Ã', 'A'), 'Ã', 'E'), 'Ã', 'I'), 'Ã', 'O'), 'Ã', 'Ã'), 'Ã', 'N') AS WORKER,
            (SELECT 
                    CONCAT(ca.name, ' - ', c.name)
                FROM
                    job c
                INNER JOIN job_agrupado ca ON c.agrupado_id = ca.agrupado_id
                WHERE
                    c.job_id = cl.job_id) AS job,
            cl.laboral_id AS CRED_ID,
            c.cred_id AS CREDENTIAL,
            (SELECT 
                    IF(exam.OLD = 1, DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.EVALUATION_DATE, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(exam.EXPIRE_DATE = ''
                            OR exam.EXPIRE_DATE IS NULL, DATE_FORMAT(DATE_ADD(exam.EVALUATION_DATE, INTERVAL exam.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y')))) AS EXPIRATION
                FROM
                    eva_competences revalidation
                LEFT JOIN eva_competences exam ON (revalidation.recognized = exam.EVA_LAB_ID)
                WHERE
                    revalidation.TIPO_EVA_ID = 1
                        AND revalidation.LABORAL_ID = cl.laboral_id
                LIMIT 1) AS date_1,
            IFNULL((SELECT 
                    IF(exam.OLD = 1, DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.EVALUATION_DATE, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(exam.EXPIRE_DATE = ''
                            OR exam.EXPIRE_DATE IS NULL, DATE_FORMAT(DATE_ADD(exam.EVALUATION_DATE, INTERVAL exam.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y')))) AS EXPIRATION
                FROM
                    eva_competences revalidation
                LEFT JOIN eva_competences exam ON (revalidation.recognized = exam.EVA_LAB_ID)
                WHERE
                    revalidation.TIPO_EVA_ID = 2
                        AND revalidation.LABORAL_ID = cl.laboral_id
                LIMIT 1), 'No Requerido') AS date_2,
            (SELECT 
                    IF(exam.OLD = 1, DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.EVALUATION_DATE, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(exam.EXPIRE_DATE = ''
                            OR exam.EXPIRE_DATE IS NULL, DATE_FORMAT(DATE_ADD(exam.EVALUATION_DATE, INTERVAL exam.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y')))) AS EXPIRATION
                FROM
                    eva_competences revalidation
                LEFT JOIN eva_competences exam ON (revalidation.recognized = exam.EVA_LAB_ID)
                WHERE
                    revalidation.TIPO_EVA_ID = 3
                        AND revalidation.LABORAL_ID = cl.laboral_id
                LIMIT 1) AS date_3,
            (SELECT 
                    MIN(IF(exam.OLD = 1, DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.EVALUATION_DATE, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(exam.EXPIRE_DATE = ''
                            OR exam.EXPIRE_DATE IS NULL, DATE_FORMAT(DATE_ADD(exam.EVALUATION_DATE, INTERVAL exam.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(exam.EXPIRE_DATE, '%d-%m-%Y'))))) AS END_DATE
                FROM
                    eva_competences revalidation
                LEFT JOIN eva_competences exam ON (revalidation.recognized = exam.EVA_LAB_ID)
                WHERE
                    revalidation.LABORAL_ID = cl.laboral_id) AS END_DATE,
            (SELECT 
                    IF(MIN(IF(exam.OLD = 1, DATE_FORMAT(exam.EXPIRE_DATE, '%Y-%m-%d'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.EVALUATION_DATE, INTERVAL revalidation.VALIDITY YEAR), '%Y-%m-%d'), IF(exam.EXPIRE_DATE = ''
                            OR exam.EXPIRE_DATE IS NULL, DATE_FORMAT(DATE_ADD(exam.EVALUATION_DATE, INTERVAL exam.VALIDITY YEAR), '%Y-%m-%d'), DATE_FORMAT(exam.EXPIRE_DATE, '%Y-%m-%d'))))) > CURDATE(), 'VALID', 'EXPIRED')
                FROM
                    eva_competences revalidation
                LEFT JOIN eva_competences exam ON (revalidation.recognized = exam.EVA_LAB_ID)
                WHERE
                    revalidation.LABORAL_ID = cl.laboral_id) AS STATUS
    FROM
        competences_laborales cl
    LEFT JOIN WORKERS t ON (cl.WORKER_ID = t.WORKER_ID)
    LEFT JOIN CREDENTIALS c ON (c.lab_id = cl.laboral_id)
    WHERE
        t.PERSON_ID = '111'
            AND cl.STATUS IN (12 , 13, 7, 8, 9)
    ORDER BY PERSON_ID , CRED_ID DESC) AS table2
GROUP BY PERSON_ID;

+-----------+---------------+---------------------+---------+------------+------------+------------+------------+------------+---------+
| PERSON_ID | WORKER        | CERTIFICATION (JOB) | CRED_ID | CREDENTIAL | DATE_1     | DATE_2     | DATE_3     | END_DATE   | STATUS  |
+-----------+---------------+---------------------+---------+------------+------------+------------+------------+------------+---------+
| 111       | NAME LASTNAME | CERTIFICATION ONE   | 53961   | 53961      | 14-02-2017 | 07-02-2017 | 21-02-2017 | 07-02-2017 | EXPIRED |
+-----------+---------------+---------------------+---------+------------+------------+------------+------------+------------+---------+
| 111       | NAME LASTNAME | CERTIFICATION TWO   | 88196   | 88196      | 08-02-2020 | 09-02-2021 | 09-02-2021 | 08-02-2020 | VALID   |
+-----------+---------------+---------------------+---------+------------+------------+------------+------------+------------+---------+
| 111       | NAME LASTNAME | CERTIFICATION TWO   | 96943   | 96943      | 23-03-2020 | 31-03-2019 | 21-02-2020 | 31-03-2019 | VALID   |
+-----------+---------------+---------------------+---------+------------+------------+------------+------------+------------+---------+

1 回复 | 直到 7 年前

Nick SamSmith1986 7 年前

JOIN CERT_ID 分组依据 CERTIFICATION :

SELECT c1.*
FROM certifications c1
JOIN (SELECT CERTIFICATION, MAX(CERT_ID) AS MAX_CERT_ID
      FROM certifications
      GROUP BY CERTIFICATION) c2 ON c2.CERTIFICATION = c1.CERTIFICATION AND c2.MAX_CERT_ID = c1.CERT_ID

PERSON_ID   CERT_ID CERTIFICATION       STATE       EXPIRE_DATE VALIDITY
111         88196   CERTIFICATION ONE   DELIVERED   16-03-2020  VALID
111         96943   CERTIFICATION TWO   DELIVERED   04-12-2020  VALID

Demo on dbfiddle