mysql - 如何使用联合表从多个记录中选择不同的记录

Question

这个问题已经有答案了:

Get top n records for each group of grouped results (12 个回答)

已关闭 6 年前。

我使用联合从差异表中选择一组列。下面是MySQL的sql脚本。我需要根据 contactId 和 created_date 仅选择最近插入的记录。

SELECT contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action,screenid
FROM
  (SELECT `contactId` AS contactId, `createdDate` AS created_date,
          CASE
              WHEN `riskRatingFlag`='1' THEN 'HIGH'
              WHEN `riskRatingFlag`='0' THEN 'LOW'
              ELSE NULL
          END AS riskRating_Flag,
          CASE
              WHEN `pepFlag`='1' THEN 'YES'
              WHEN `pepFlag`='0' THEN 'NO'
              ELSE NULL
          END AS pep_Flag,
          `remarks` AS remarks,
          'ResolveHit_T' AS `tablename`,
            'Initiate Review' AS action,
          `resolveHitId` AS screenid
   FROM `ast_CustomersScreenedResolveHit_T`
   UNION SELECT `contactId` AS contactId, `createdDate` AS created_date,
                CASE
                    WHEN `riskRatingFlag`='1' THEN 'HIGH'
                    WHEN `riskRatingFlag`='0' THEN 'LOW'
                    ELSE NULL
                END AS riskRating_Flag,
                CASE
                    WHEN `pepFlag`='1' THEN 'YES'
                    WHEN `pepFlag`='0' THEN 'NO'
                    ELSE NULL
                END AS pep_Flag,
                `remarks` AS remarks,
                'CountryRisk_T' AS `tablename`,
'Initiate Review' AS action,
                `countryRiskId` AS screenid
   FROM `ast_CustomersScreenedCountryRisk_T`
   UNION SELECT `contactId` AS contactId, `createdDate` AS created_date,
                CASE
                    WHEN `riskRatingFlag`='1' THEN 'HIGH'
                    WHEN `riskRatingFlag`='0' THEN 'LOW'
                    ELSE NULL
                END AS riskRating_Flag,
                CASE
                    WHEN `pepFlag`='1' THEN 'YES'
                    WHEN `pepFlag`='0' THEN 'NO'
                    ELSE NULL
                END AS pep_Flag,
                `remarks` AS remarks,
                'OccupationRisk_T' AS `tablename`,
'Initiate Review' AS action,
                `occupationRiskId` AS screenid
   FROM `ast_CustomersScreenedOccupationRisk_T`) AS `result`

ORDER BY `result`.`created_date` DESC

下图中提到了预期的输出 expected result

但是我得到的结果是 result what I got

Answer 1

它会起作用，但请参阅代码中的评论“您可能需要更多专栏”。如果存在具有相同 contactId 和created_Date 的多行，您需要添加其他列才能获得准确的输出。

select contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action, screenid
from (
    select contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action, screenid,
        @running:=if(@previous=contactId,@running,0) + 1 as rownum,
        @previous:=contactId
    from 
    (
        SELECT contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action,screenid
        FROM
          (
            SELECT `contactId` AS contactId, `createdDate` AS created_date,
              CASE
                  WHEN `riskRatingFlag`='1' THEN 'HIGH'
                  WHEN `riskRatingFlag`='0' THEN 'LOW'
                  ELSE NULL
              END AS riskRating_Flag,
              CASE
                  WHEN `pepFlag`='1' THEN 'YES'
                  WHEN `pepFlag`='0' THEN 'NO'
                  ELSE NULL
              END AS pep_Flag,
              `remarks` AS remarks,
              'ResolveHit_T' AS `tablename`,
                'Initiate Review' AS action,
              `resolveHitId` AS screenid
           FROM `ast_CustomersScreenedResolveHit_T`
           UNION 
           SELECT `contactId` AS contactId, `createdDate` AS created_date,
            CASE
                WHEN `riskRatingFlag`='1' THEN 'HIGH'
                WHEN `riskRatingFlag`='0' THEN 'LOW'
                ELSE NULL
            END AS riskRating_Flag,
            CASE
                WHEN `pepFlag`='1' THEN 'YES'
                WHEN `pepFlag`='0' THEN 'NO'
                ELSE NULL
            END AS pep_Flag,
            `remarks` AS remarks,
            'CountryRisk_T' AS `tablename`,
            'Initiate Review' AS action,
            `countryRiskId` AS screenid
           FROM `ast_CustomersScreenedCountryRisk_T`
           UNION SELECT `contactId` AS contactId, `createdDate` AS created_date,
            CASE
                WHEN `riskRatingFlag`='1' THEN 'HIGH'
                WHEN `riskRatingFlag`='0' THEN 'LOW'
                ELSE NULL
            END AS riskRating_Flag,
            CASE
                WHEN `pepFlag`='1' THEN 'YES'
                WHEN `pepFlag`='0' THEN 'NO'
                ELSE NULL
            END AS pep_Flag,
            `remarks` AS remarks,
            'OccupationRisk_T' AS `tablename`,
            'Initiate Review' AS action,
            `occupationRiskId` AS screenid
           FROM `ast_CustomersScreenedOccupationRisk_T`) AS `result`

        ORDER BY `result`.`created_date` DESC
    ) t
    order by contactId, created_date DESC -- You may need to add more column here
) g
WHERE g.rownum = 1;

您还可以使用 ROW_NUMBER() OVER(PARTITION BY __ ORDER BY __)，但对于具有相同 contactId 和creation_date 的多行，它将不起作用。

select contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action, screenid
from (
    select contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action, screenid,
        ROW_NUMBER() OVER (PARTITION BY contactId ORDER BY created_date DESC) AS rownum
    from 
    (
        SELECT contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action,screenid
        FROM
          (
            SELECT `contactId` AS contactId, `createdDate` AS created_date,
              CASE
                  WHEN `riskRatingFlag`='1' THEN 'HIGH'
                  WHEN `riskRatingFlag`='0' THEN 'LOW'
                  ELSE NULL
              END AS riskRating_Flag,
              CASE
                  WHEN `pepFlag`='1' THEN 'YES'
                  WHEN `pepFlag`='0' THEN 'NO'
                  ELSE NULL
              END AS pep_Flag,
              `remarks` AS remarks,
              'ResolveHit_T' AS `tablename`,
                'Initiate Review' AS action,
              `resolveHitId` AS screenid
           FROM `ast_CustomersScreenedResolveHit_T`
           UNION 
           SELECT `contactId` AS contactId, `createdDate` AS created_date,
            CASE
                WHEN `riskRatingFlag`='1' THEN 'HIGH'
                WHEN `riskRatingFlag`='0' THEN 'LOW'
                ELSE NULL
            END AS riskRating_Flag,
            CASE
                WHEN `pepFlag`='1' THEN 'YES'
                WHEN `pepFlag`='0' THEN 'NO'
                ELSE NULL
            END AS pep_Flag,
            `remarks` AS remarks,
            'CountryRisk_T' AS `tablename`,
            'Initiate Review' AS action,
            `countryRiskId` AS screenid
           FROM `ast_CustomersScreenedCountryRisk_T`
           UNION SELECT `contactId` AS contactId, `createdDate` AS created_date,
            CASE
                WHEN `riskRatingFlag`='1' THEN 'HIGH'
                WHEN `riskRatingFlag`='0' THEN 'LOW'
                ELSE NULL
            END AS riskRating_Flag,
            CASE
                WHEN `pepFlag`='1' THEN 'YES'
                WHEN `pepFlag`='0' THEN 'NO'
                ELSE NULL
            END AS pep_Flag,
            `remarks` AS remarks,
            'OccupationRisk_T' AS `tablename`,
            'Initiate Review' AS action,
            `occupationRiskId` AS screenid
           FROM `ast_CustomersScreenedOccupationRisk_T`) AS `result`

        ORDER BY `result`.`created_date` DESC
    ) t
) g
WHERE g.rownum = 1;