Sql Server 2000 Puzzle

Question

 SELECT    Id ,
           ActionDate ,
           Operation ,
           Date ,
           [Payroll Number]    
FROM 
[dbo].[tblx]
1   2018-01-10 00:00:00.000 NEW     2018-01-30 00:00:00 X0154X
2   2018-01-10 00:00:00.000 NEW     2018-01-31 00:00:00 X0154X
3   2018-01-10 00:00:00.000 NEW     2018-02-01 00:00:00 X0154X
4   2018-01-10 00:00:00.000 DELETE  2018-02-01 00:00:00 X0154X
5   2018-01-10 00:00:00.000 NEW     2018-02-02 00:00:00 X0154X
6   2018-01-10 00:00:00.000 NEW     2018-02-11 00:00:00 X0154X

我需要基于薪资/日期组合的行动日期的最新记录。因此，我在SQL Server 2000上尝试了此。但是，问题是ROW_NUMBER()直到SQL Server 2008。

才引入。

这给了我4个而不是5个记录。ID 4的记录未返回，因为与动作日期保持联系。

SELECT     Id ,
           ActionDate ,
           Operation ,
           Date ,
           [Payroll Number]
  FROM (SELECT *, (SELECT COUNT(*)
                   FROM dbo.tblx AS counter
                   WHERE counter.[Payroll Number] = dbo.tblx.[Payroll Number]
                    AND counter.[Date] = dbo.tblx.[Date]
                    AND  counter.ActionDate >= dbo.tblx.ActionDate) AS rn
        FROM dbo.tblx
        WHERE Status IN ( 0, 5 )
          AND Category = 'Holiday') AS r1
  WHERE r1.rn = 1
  Id  ActionDate                Operation   Date                Payroll Number
  1   2018-01-10 00:00:00.000   NEW         2018-01-30 00:00:00 X0154X
  2   2018-01-10 00:00:00.000   NEW         2018-01-31 00:00:00 X0154X
  5   2018-01-10 00:00:00.000   NEW         2018-02-02 00:00:00 X0154X
  6   2018-01-10 00:00:00.000   NEW         2018-02-11 00:00:00 X0154X

有没有办法在SQL Server 2000上没有ROW_NUMBER()的情况下获得此方法？

Answer 1

在窗口前的功能中，您可以做：

SELECT x.* 
FROM [dbo].[tblx] x
WHERE x.ActionDate = (SELECT MAX(x2.ActionDate)
                      FROM [dbo].[tblx] x2
                      WHERE x2.[Payroll Number] = x.[Payroll Number] AND
                            x2.Date = x.Date
                     );

此语法用于不支持窗口函数的MySQL，SQLite和MS访问。

如果您可以通过id定义"最新"，请在子查询中使用它而不是日期：

SELECT x.* 
FROM [dbo].[tblx] x
WHERE x.id = (SELECT MAX(x2.id)
              FROM [dbo].[tblx] x2
              WHERE x2.[Payroll Number] = x.[Payroll Number] AND
                    x2.Date = x.Date
             );

Answer 2

这有效，但不是很好的代码或SQL。

SELECT 
             a.ActionDate ,
             a.Date ,
             a.[Payroll Number]
             , MAXC( a.id) AS ID_Required_To_Porcess
    FROM 
    [dbo].[tblx] a
    INNER JOIN ( 
    SELECT 
             x.ActionDate ,
             x.Date ,
             x.[Payroll Number]
    FROM [dbo].[tblx] x
    WHERE x.ActionDate = (SELECT MAX(x2.ActionDate)
                          FROM [dbo].[tblx] x2
                          WHERE x2.[Payroll Number] = x.[Payroll Number] AND
                                x2.Date = x.Date
                         )
    GROUP BY x.ActionDate ,
             x.Date ,
             x.[Payroll Number]

             ) b ON a.ActionDate=b.actionDate AND a.date=b.date AND a.[Payroll Number]=b.[Payroll Number]

    GROUP BY     a.ActionDate ,
             a.Date ,
             a.[Payroll Number]