如何按列数少于所选列数进行分组 [英] How to group by less columns than selected

问题描述

我在这里遇到了一个问题(使用 SQL Server 2005).

I'm facing a problem here (using SQL Server 2005).

我的 SELECT 查询如下所示:

My SELECT query looks like this:

SELECT 
a.str_column1, b.str_column2, c.date_column3, c.guid_column4
FROM table
....
joining the other tables here to get my columns
....
GROUP BY 
    a.str_column1, b.str_column2, c.date_column3, c.guid_column4

这将给出这样的东西

a.str_column1    b.str_column2    c.date_column3    c.guid_column4
------------------------------------------------------------------
a1               b1                15/07/2013       someID    
a2               b2                05/06/2012       someID
a1               b1                07/08/2013       someID
....

现在我想让它按 a.str_column1 和 b.str_column2 分组，只获取最新的(order by c.dat_column3代码>)

Now I want so that it's grouped by a.str_column1 and b.str_column2, only getting the most recent one (order by c.dat_column3)

a.str_column1    b.str_column2    c.date_column3    c.guid_column4
------------------------------------------------------------------
a1               b1                07/08/2013       someID
a2               b2                05/06/2012       someID

知道如何使用 SQL 完成此操作吗?

Any idea how I can accomplish this with SQL?

推荐答案

您可以使用 ROW_NUMBER()，并且可能能够完全消除GROUP BY:

SELECT
    *
FROM (
  SELECT 
  a.str_column1, b.str_column2, c.date_column3, c.guid_column4,
  ROW_NUMBER() OVER (PARTITION BY a.str_column1, b.str_column2
                     ORDER BY c.date_column3 DESC) as rn
  FROM table
  ....
  joining the other tables here to get my columns
  ....
  --No longer needed GROUP BY a.str_column1, b.str_column2, c.date_column3, c.guid_column4
) t
WHERE t.rn = 1

为了能够查询 ROW_NUMBER() 函数的结果，您必须放置现有查询(在 SELECT 列表中使用新列) 到子查询(如上)或公共表表达式.

In order to be able to query on the result of the ROW_NUMBER() function, you have to place your existing query (with the new column in the SELECT list) into a subquery (as above) or a Common Table Expression.

这篇关于如何按列数少于所选列数进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！