在不使用临时表的情况下选择不同的记录

Question

我有一个第三方表，其中填充了一些杂乱的数据，我需要从中获取最新的不同记录。每年或每次“人”发生变化时，该表都会添加一个新行。该表的工作原理是基于最近的 ActiveDate 是正确的人。我创建了一个模拟表和数据来显示这一点。

CREATE TABLE `Persons` (
  `PersonId` varchar(200) NOT NULL,
  `Name` varchar(200) NOT NULL DEFAULT '',
  `ActiveDate` varchar(25) NOT NULL,
  `ExpireDate` varchar(25) DEFAULT NULL,
  `Job` varchar(200) NOT NULL DEFAULT '',
  `Position` varchar(200) NOT NULL DEFAULT ''
)

还有一些模拟数据：

Id       |`Name`        |ActiveDate              |ExpireDate             |Job       |`Position`
---------------------------------------------------------------------------------------------------
J1234    |Doe, John     |2010-08-15 00:00:00     |2011-08-15 00:00:00    |Worker    |Janitor
J1234    |Doe, John     |2011-08-15 00:00:00     |0000-00-00 00:00:00    |Worker    |Janitor
777      |Doe, Jane     |2010-06-04 00:00:00     |0000-00-00 00:00:00    |Boss      |Janitor
777      |Doe, Jane     |2011-04-30 00:00:00     |0000-00-00 00:00:00    |Boss      |Janitor
654G     |Smith, Jane   |2011-01-20 00:00:00     |0000-00-00 00:00:00    |Worker    |Janitor

该表还具有 ExpireDate 列，该列实际上是由最终用户设置的，并且并不总是设置得太多，令我沮丧。目前，我正在使用虚拟表将不同的记录拉出并存储当天。我会使用临时表，但我不是 100% 确定如何在 MySQL 中使用，而且我不喜欢它们。我这样做只是暂时的，希望获得更好的 SQL。

然后，数据必须与许多其他表连接起来才能得到成品。但我仍然需要处理初始的一组不同数据。并且从一开始就加入另一个表是行不通的。

因此，这就是我如何提取数据、存储数据，然后稍后再次提取数据并将其连接到其他表：

INSERT INTO tmp_Person (Id, `Name`, Job, `Position`) 
    SELECT DISTINCT Id, `Name`, Job, `Position`
    FROM Person 

SELECT  tmp_Person.Id, 
    tmp_Person.`Name`, 
    tmp_Person.Job, 
    tmp_Person.`Position`,
    Pricing.Cost, 
    Pricing.Benefit

    FROM tmp_Person
    LEFT OUTER JOIN Pricing AS CL ON CL.PersonId = tmp_Person.Id 
        AND CL.PriceScredule = 'Major-Client' 
        AND CL.ExpireDate = '0000-00-00 00:00:00'
    LEFT OUTER JOIN Pricing AS Inter ON Inter.PersonId = tmp_Person.Id 
        AND Inter.PriceScredule = 'Internal-Client' 
        AND Inter.ExpireDate = '0000-00-00 00:00:00'

我如何编写此代码以避免使用临时表（以任何形式）处理重复行的成本）？希望我已经说得足够清楚了，如果没有，我可以很乐意补充或澄清。

Answer 1

将 tmp_Person 替换为临时表的代码：

SELECT  tmp_Person.Id, 
    tmp_Person.`Name`, 
    tmp_Person.Job, 
    tmp_Person.`Position`,
    CL.Cost     AS MajorCost,              
    CL.Benefit  AS MajorBenefit,   
    Inter.Cost    AS InternalCost,
    Inter.Benefit AS InternalBenefit

    FROM 
      ( SELECT DISTINCT Id, `Name`, Job, `Position`
        FROM Person 
      )
      AS tmp_Person
    LEFT OUTER JOIN Pricing AS CL ON CL.PersonId = tmp_Person.Id 
        AND CL.PriceScredule = 'Major-Client' 
        AND CL.ExpireDate = '0000-00-00 00:00:00'
    LEFT OUTER JOIN Pricing AS Inter ON Inter.PersonId = tmp_Person.Id 
        AND Inter.PriceScredule = 'Internal-Client' 
        AND Inter.ExpireDate = '0000-00-00 00:00:00'

正如 @Andriy 发现的那样，在 SELECT 列表中使用 Pricing.Cost 或 Pricing.Benefit会引发错误。我猜你发帖的时候忘记修改了。

Answer 2

在我意识到问题是针对 mysql 之前将其放在一起，但原理应该是相同的，这将为您提供每个 PersonID 的记录以及 Person 表中最新的 ActiveDate 。

select *
from
(
 select persons.*, ROW_NUMBER() over(partition by personid order by personid, activedate desc) as rn 
 from persons
) basedata
where basedata.rn=1