从重复结果中选择具有最大日期的行

Note: I am working with SQL 2000.

注意:我正在使用SQL 2000。

I would like to select the row with the MAX date for t2.JobCompletionTime by doing something like this for this Join but I know this is not the correct code/syntax as it does not work...but I hope it is clear what I am trying to do.

我想通过为此Join做类似的事情为t2.JobCompletionTime选择具有MAX日期的行但我知道这不是正确的代码/语法,因为它不起作用...但我希望它清楚我是什么我想做。

LEFT OUTER JOIN (
  SELECT *
  FROM DSM_StandardProcedureActivityView
  WHERE
  ( (PackageName = 'Adobe Acrobat 10'  AND PackageVersion = '-1.0') AND
    (MAX (JobCompletionTime))
  )
) t2 ON t1.UUID = t2.UUID

My full working SQL code as it stands now is:

我现在的完整工作SQL代码是:

SELECT 
t1.Business,
t1.[Sub-Business],
t1.Pole,
t1.DomainManager,
t1.ScalabilityServer,
t1.Location,
t1.Country,
t1.Name,
t3.VolumeName,
t3.VolumeFreeInMB,
t3.VolumeSizeInMB,
FROM (
  SELECT *
  FROM DSM_StandardGroupMembersView
  WHERE 
  (  GroupName = 'Adobe Acrobat'
  )
) t1
LEFT OUTER JOIN (
  SELECT *
  FROM DSM_StandardProcedureActivityView
  WHERE
  ( (PackageName = 'Adobe Acrobat 10'  AND PackageVersion = '-1.0')
  )
) t2 ON t1.UUID = t2.UUID
LEFT OUTER JOIN (
  SELECT *
  FROM DSM_StandardHardwareDiskUsageView
  WHERE VolumeName = 'C:\'
) t3 ON t1.Name = t3.Name
ORDER BY t1.Business, t1.[Sub-Business], t1.Pole, t1.DomainManager, t1.ScalabilityServer, t1.Country, t1.Location, [Job Status], t1.Name

3 个解决方案

#1

Going through this in steps as to make it more obvious where I might make an interpretation error here...

逐步完成这一步,以便更明显地在这里我可以解释错误...

Step 1 : beautifying your query a bit + leaving out the ORDER BY as you can add that one easily later on again

第1步:稍微美化您的查询+省略ORDER BY,因为您可以稍后再轻松添加该查询

SELECT t1.Business,
       t1.[Sub-Business],
       t1.Pole,
       t1.DomainManager,
       t1.ScalabilityServer,
       t1.Location,
       t1.Country,
       t1.Name,
       t3.VolumeName,
       t3.VolumeFreeInMB,
       t3.VolumeSizeInMB

  FROM DSM_StandardGroupMembersView  t1

  LEFT OUTER JOIN DSM_StandardProcedureActivityView t2
               ON t2.PackageName = 'Adobe Acrobat 10'  
              AND t2.PackageVersion = '-1.0'
              AND t2.UUID = t1.UUID

  LEFT OUTER JOIN DSM_StandardHardwareDiskUsageView t3
               ON t3.VolumeName = 'C:'
              AND t3.Name = t1.Name

 WHERE t1.GroupName = 'Adobe Acrobat'

The way I understand your question is that you you want to limit the records from DSM_StandardProcedureActivityView t2 to just those that represent the latest situation (based on the t2.JobCompletionTime field). Right ? Now either we go grouping this upfront and then join with those that match (by UUID), or we think of this the other way around and first let the list be limited to the needed UUID's and then strip out all those that have a more recent version. Since you're on SQL2000 I'd suggest the latter as I'm not sure the Query Optimizer would be able to optimize the former. (feel free to test =)

我理解你的问题的方式是你想要将DSM_StandardProcedureActivityView t2的记录限制为那些代表最新情况的记录(基于t2.JobCompletionTime字段)。对 ?现在我们要先预先分组,然后加入匹配的那些(通过UUID),或者我们反过来考虑这一点,首先让列表限制为所需的UUID,然后删除所有那些最近的UUID版。因为你在SQL2000上我会建议后者,因为我不确定查询优化器是否能够优化前者。 (随意测试=)

This would then look something like this :

这将看起来像这样:

SELECT t1.Business,
       t1.[Sub-Business],
       t1.Pole,
       t1.DomainManager,
       t1.ScalabilityServer,
       t1.Location,
       t1.Country,
       t1.Name,
       t3.VolumeName,
       t3.VolumeFreeInMB,
       t3.VolumeSizeInMB

  FROM DSM_StandardGroupMembersView  t1

  LEFT OUTER JOIN DSM_StandardProcedureActivityView t2
               ON t2.PackageName = 'Adobe Acrobat 10'  
              AND t2.PackageVersion = '-1.0'
              AND t2.UUID = t1.UUID
              -- limit to latest records only!
              AND NOT EXISTS ( SELECT *
                                 FROM DSM_StandardProcedureActivityView t2_newer
                                WHERE t2_newer.UUID = t2.UUID
                                  AND t2_newer.JobCompletionTime > t2.JobCompletionTime )

  LEFT OUTER JOIN DSM_StandardHardwareDiskUsageView t3
               ON t3.VolumeName = 'C:'
              AND t3.Name = t1.Name

 WHERE t1.GroupName = 'Adobe Acrobat'

Hope this helps.

希望这可以帮助。

#2

You've posted too much irrelevant SQL for me to wade through, so I'll just fix your excerpt:

你已经发布了太多不相关的SQL供我浏览,所以我只修复你的摘录:

LEFT OUTER JOIN (
  SELECT * FROM (
    SELECT *
    FROM DSM_StandardProcedureActivityView
    WHERE PackageName = 'Adobe Acrobat 10'
    AND PackageVersion = '-1.0'
    ORDER BY JobCompletionTime DESC) x
   ) y
   GROUP BY PackageName, PackageVersion
) t2 ON t1.UUID = t2.UUID

What's happening here is the inner query gets the rows sorted by lastest JobCompletionTime first, the next level query uses a group by on the matching columns, which in mysql, gives you the first matching row (the latest, since we sorted the rows)

这里发生的是内部查询首先获取按最新JobCompletionTime排序的行,下一级查询在匹配列上使用group by,在mysql中,为您提供第一个匹配行(最新的,因为我们对行进行了排序)

#3

A ranking function called ROW_NUMBER() could help you here

名为ROW_NUMBER()的排名函数可以帮助您

LEFT OUTER JOIN (
    SELECT 
        *, 
        rowNumber = ROW_NUMBER() OVER (ORDER BY JobCompletionTime DESC)
    FROM DSM_StandardProcedureActivityView
    WHERE PackageName = 'Adobe Acrobat 10'
    AND PackageVersion = '-1.0'
) t2 ON t1.UUID = t2.UUID AND rowNumber = 1

Ordering by the JobCompletionTime (descending) should put the highest value as row number 1; hence the extra clause added to the join clause.

按JobCompletionTime(降序)排序应将最高值作为行号1;因此,extra子句被添加到join子句中。

#1