TSQL OVER (PARTITION BY ... )

Question

在我的下一个技巧中，我只想为每个客户端选择最近的事件。我想要一个，而不是000017的四个事件。

OK  c_id    e_date  e_ser   e_att   e_recip Age c_cm    e_staff rn
--> 000017  2013-04-02 00:00:00.000 122 1   1   36  90510   90510   15
--> 000017  2013-02-26 00:00:00.000 122 1   1   36  90510   90510   20
--> 000017  2013-02-12 00:00:00.000 122 1   1   36  90510   90510   24
--> 000017  2013-01-29 00:00:00.000 122 1   1   36  90510   90510   27
--> 000188  2012-11-02 00:00:00.000 160 1   1   31  1289    1289    44
--> 001713  2013-10-01 00:00:00.000 142 1   1   26  2539    2539    1
--> 002531  2013-07-12 00:00:00.000 190 1   1   61  1689    1689    21
--> 002531  2013-06-14 00:00:00.000 190 1   1   61  1689    1689    30
--> 002531  2013-06-07 00:00:00.000 190 1   1   61  1689    1689    31
--> 002531  2013-05-28 00:00:00.000 122 1   1   61  1689    1689    33

下面是将我带到这个阶段的查询(也许您有一些建议来改进这一点，额外的嵌套查询创建t2表可能是多余的。)谢谢大家！

SELECT TOP(10)*
FROM (

  SELECT *
  FROM (

    SELECT (SELECT CASE WHEN 
     (e_att IN (1,2)
     AND e_date > DATEADD(month, -12, getdate())
     AND e_ser NOT IN (100,115)
     AND e_recip NOT IN ('2','7')
     AND (( (e_recip = '3') AND (DATEDIFF(Year, c_bd, GetDate())>10) ) OR (e_recip <> '3') )
     AND c_cm = e_staff)
     THEN '-->'
     WHEN 1=1 THEN ''
     END
     ) AS 'OK'
     ,c_id, e_date, e_ser, e_att, e_recip, DATEDIFF(Year, c_bd, GetDate()) AS 'Age', c_cm, e_staff
     ,row_number() OVER (PARTITION BY c_id ORDER BY e_date DESC) rn             
    FROM events INNER JOIN client ON e_case_no = c_id
    LEFT OUTER JOIN doc ON doc.doc_dbid = client.c_id
    WHERE client.c_id IN ( /* confidential query */ )
    AND e_date > DATEADD(month, -12, getdate())
    AND e_ser BETWEEN 11 AND 1000
    GROUP BY        c_id, e_date, e_ser, e_att, e_recip, c_bd, c_cm, e_staff
    ) t1
  ) t2
WHERE           OK = '-->'
ORDER BY        c_id, e_date DESC

Answer 1

看起来，下面的代码将为每个客户端生成按日期排序的行号：

,row_number() OVER (PARTITION BY c_id ORDER BY e_date DESC) rn             

因此，添加where rn=1应该为每个客户端生成最新的事件：

  ) t1
  WHERE rn = 1
) t2

Answer 2

以下是对原始查询的一些改进：

SELECT TOP(10) *
FROM (

    SELECT '-->' AS 'OK' -- always this see where.
     ,c_id, e_date, e_ser, e_att, e_recip, DATEDIFF(Year, c_bd, GetDate()) AS 'Age', c_cm, e_staff
     ,row_number() OVER (PARTITION BY c_id ORDER BY e_date DESC) rn             
    FROM events INNER JOIN client ON e_case_no = c_id
    LEFT OUTER JOIN doc ON doc.doc_dbid = client.c_id
    WHERE client.c_id IN ( /* confidential query */ )
          -- this part was in case and then filtered for later, if we put it in where now more efficient 
          (e_att IN (1,2)  AND e_date > DATEADD(month, -12, getdate())
            AND e_ser NOT IN (100,115)
            AND (( (e_recip = '3') AND DATEDIFF(Year, c_bd, GetDate()>10) ) OR e_recip NOT IN ('2', '3', '7') )
            AND c_cm = e_staff)


    AND e_date > DATEADD(month, -12, getdate())
    AND e_ser BETWEEN 11 AND 1000
    GROUP BY        c_id, e_date, e_ser, e_att, e_recip, c_bd, c_cm, e_staff
  ) t2
ORDER BY        c_id, e_date DESC

除了删除一些不需要的括号之外，如果您将内容从CASE语句移动到a，则不需要在外部查询中对其进行过滤，这会使它变得更简单。

添加来自McGarnagle答案的row_number语句，您应该会得到您想要的结果。