ROW_NUMBER() and GROUP BY with a twist

Question

我试图解释在MSAccess中构建的查询，并将其转化为SQL Server可用的SQL。

下面是SQL视图中Access的语句:

SELECT CUSTFILE.CUSTNUM, 
Last(GENPOL.eff_date) AS LastOfeff_date, 
First(GENPOL.appnum) AS FirstOfAPPNUM
FROM GENPOL INNER JOIN CUSTFILE ON GENPOL.entnum = CUSTFILE.ENTNUM
GROUP BY CUSTFILE.CUSTNUM
HAVING...
ORDER BY LastGENPOL.eff_date DESC;

我们知道First()和Last()函数在SQL Server中无效并且不工作，所以我尝试使用MIN()和MAX()函数，因为表没有增量ID字段或任何东西。一个大问题是，例如appnum的字段中可能有非常奇怪的数据，例如:

 MTP-021106

或

HMTP-271103

如果最后插入的appnum是HMTP-271103，并且genpol表中有更早的条目，如MTP-021106作为它们的appnum

Than MTP-021106将返回，因为M大于H。

同样，eff_date也可能有同样的问题，因为最新记录的日期可能比最后一个输入的日期早。

Last(GENPOL.eff_date) AS LastOfeff_date,

最终，查询为每个满足HAVING条件的CUSTFILE.CUSTNUM返回一行。

我认为可能是一个答案是使用ROW_NUMBER()函数来获得我需要的结果，但无法让它与GROUP BY语句一起工作。

表GENPOL:

<>之前(PK) appnum varchar (13)eff_date, datetimentnum varchar (15)之前表CUSTFILE:

<>之前(PK) CUSTNUM varchar (8)ENTNUM varchar (15)之前

示例数据<>之前CUSTNUM | lastoffff_date | FirstOfAppnumMi99103 | 2013-01-01 | mtp-991103Mi99104 | 2013-01-01 | mtp-991104Mi99105 | 2013-01-01 | mtp-991105

Answer 1

EDIT:修正了对其中一列的引用

根据您所列出的内容，应该可以这样做:

SELECT 
    cfile.CUSTNUM,
    aGENPOL.eff_date [LastOfeff_date],
    bGENPOL.appnum [FirstOfAPPNUM] 
FROM CUSTFILE cfile
CROSS APPLY (
    SELECT TOP 1 * 
    FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY entnum ORDER BY entnum) row, eff_date
        FROM GENPOL 
        WHERE entnum  = cfile.entnum        
    ) x
    ORDER BY row DESC
) aGENPOL 
CROSS APPLY (
    SELECT TOP 1 * 
    FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY entnum ORDER BY entnum) row, appnum
        FROM GENPOL 
        WHERE entnum  = cfile.entnum 
    ) y
    ORDER BY row
) bGENPOL

基本上，应用程序中的查询将通过按每个entnum对表进行分区，然后按行号排序(First为升序，Last为降序)来获取每种情况下的顶部记录。

由于连接查询相当复杂，我还选择使用CROSS APPLY，因为它在进行连接之前基本上过滤了记录。