SQL - 聚合函数 - GROUP BY子句

I have the following query:

我有以下查询：

select vkbr.vkID, vkbr.bid, vkbr.Date, vkbr.agID 
FROM camp c (NOLOCK)
JOIN ag (NOLOCK) ON ag.campID = c.id
JOIN vkbr WITH (NOLOCK) ON vkbr.agID = ag.id
where c.id = 648322
order by vkbr.vkID;

Which has the following results:

其结果如下：

vkID        bid     Date                    agID
1072845175  NULL    2012-12-04 20:20:12.390 16074852
1072845177  0.74    2012-12-01 23:36:11.280 16074852
1072845177  0.18    2012-12-02 23:01:26.123 16074852
1072845177  0.72    2012-12-09 23:38:52.503 16074852
1072845177  0.62    2012-12-14 15:26:49.643 16074852
1072845178  2.91    2012-12-08 19:37:00.877 16074852
1072845178  0.73    2012-12-13 17:54:11.240 16074852
1072845178  2.70    2012-12-14 15:26:49.643 16074852

I need to group by vkID, get the max(Date) and the bid on the max(Date) to get this result:

我需要按vkID分组，获取max（Date）和max（Date）的出价以获得此结果：

1072845175  NULL    2012-12-04 20:20:12.390 16074852
1072845177  0.62    2012-12-14 15:26:49.643 16074852
1072845178  2.70    2012-12-14 15:26:49.643 16074852


select vkbr.vkID, MAX(vkbr.Date) as Date, MIN(vkbr.agID) as agID
FROM camp c (NOLOCK)
JOIN    ag (NOLOCK) ON ag.campID = c.id
JOIN    vkbr WITH (NOLOCK) ON vkbr.agID = ag.id
where c.id=648322
group by vkbr.vkID
having Max(vkbr.Date) is not null
and Max(Date) <= '2012-12-18';

Since I cannot add the bid column in SELECT statement - receive this error: Column 'bid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

由于我无法在SELECT语句中添加bid列 - 收到此错误：列'bid'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

So I tried to do this with temp tables:

所以我尝试用临时表做到这一点：

create table #getBids ( 
vkID bigint not null,
Date datetime null,
agID bigint null);

insert into #getBids (vkID, Date, agID) 
select vkbr.vkID, MAX(vkbr.Date) as Date, MIN(vkbr.agID) as agID
FROM camp c (NOLOCK)
JOIN    ag (NOLOCK) ON ag.campID = c.id
JOIN    vkbr WITH (NOLOCK) ON vkbr.agID = ag.id
where c.id = 648322
group by vkbr.vkID
having Max(vkbr.Date) is not null
and Max(Date) <= '2012-12-18';

Now this gives me the result I want:

select vkbr.vkID, vkbr.bid from vkbr
inner join #getBids on vkbr.Date = #getBids.Date
and vkbr.agID = #getBids.agID
and vkbr.vkID = #getBids.vkID

I was wondering is there anyway to accomplish the same result in one query w/o creating the temp table? Any help is greatly appreciated.

我想知道无论如何要在一个查询中创建相同的结果而不创建临时表？任何帮助是极大的赞赏。

3 个解决方案

#1

If you're using SQL Server 2005 or higher you can use the ROW_NUMBER() function to select the row with MAX(Date) for each vkID.

如果您使用的是SQL Server 2005或更高版本，则可以使用ROW_NUMBER（）函数为每个vkID选择MAX（Date）行。

I haven't been able to test this with your schema but I think it would work something like this:

我无法使用您的架构测试它，但我认为它可以像这样工作：

First, declare a Common Table Expression (CTE) to add a row number to your query results

首先，声明公用表表达式（CTE）以向查询结果添加行号

with cte as
(    
    select vkbr.vkID, vkbr.bid, vkbr.Date, vkbr.agID, ROW_NUMBER() OVER (PARTITION BY vkbr.vkID ORDER BY vkbr.Date DESC) AS RowNumber
    FROM camp c (NOLOCK)
    JOIN ag (NOLOCK) ON ag.campID = c.id
    JOIN vkbr WITH (NOLOCK) ON vkbr.agID = ag.id
    where c.id = 648322
)

Then, SELECT only where RowNumber = 1. This will give you the latest record for each vkbr.vkID

然后，只选择RowNumber = 1的位置。这将为您提供每个vkbr.vkID的最新记录

select vkID, bid, Date, agID
from cte
where RowNumber = 1

#2

Query:

查询：

SELECT vkbr.vkID,
       vkbr.bid,
       vkbr.Date,
       vkbr.agID
FROM camp c (NOLOCK)
JOIN ag (NOLOCK) ON ag.campID = c.id
JOIN vkbr WITH (NOLOCK) ON vkbr.agID = ag.id
WHERE c.id = 648322
AND vkbr.Date = (SELECT MAX(v.Date)
                 FROM vkbr v2
                 WHERE v2.vkID = vkbr.vkID
                 AND v2.agID = vkbr.agID)
ORDER BY vkbr.vkID

Example Query:

示例查询：

SQLFIDDLEExample

SQLFIDDLEExample

SELECT t1.*
FROM Table1 t1
WHERE t1.Date = (SELECT MAX(t2.Date)
                 FROM Table1 t2
                 WHERE t1.vkID = t2.vkID
                 AND t1.agID = t2.agID)

Result:

结果：

|       VKID |    BID |                            DATE |     AGID |
--------------------------------------------------------------------
| 1072845178 |   2.70 | December, 14 2012 15:26:49+0000 | 16074852 |
| 1072845177 |   0.62 | December, 14 2012 15:26:49+0000 | 16074852 |
| 1072845175 | (null) | December, 04 2012 20:20:12+0000 | 16074852 |

#3

You can do this with one query. First, the query must gather the max date by vkID. Next, it must join back to vkbr using the date to get the actual bid. You could use a sub-query, i.e. something lIke this:

您可以使用一个查询执行此操作。首先，查询必须通过vkID收集最大日期。接下来，它必须使用日期加入vkbr以获得实际出价。你可以使用一个子查询，即点击这个：

select vkID, bid, Date, agID
from
(
    select vkbr.vkID, MAX(vkbr.Date) as Date, MIN(vkbr.agID) as agID
    from camp c with (nolock)
    inner join ag with (nolock)
        on ag.campID = c.id
    inner join vkbr with (nolock)
        on vkbr.agID = ag.id
    where c.id = 648322
    group by vkbr.vkID
    having Max(vkbr.Date) is not null
    and Max(vkbr.Date) <= '2012-12-18';
) MaxDate
    inner join vkbr with (nolock)
        on vkbr.agID = MaxDate.agID
        and vkbr.Date = MaxDate.Date

I would recommend using a common table expression (CTE) instead of a sub-query. The CTE approach would look something like this:

我建议使用公用表表达式（CTE）而不是子查询。 CTE方法看起来像这样：

;with MaxDate (vkID, Date, agID) as
(
    select vkbr.vkID, MAX(vkbr.Date) as Date, MIN(vkbr.agID) as agID
    from ...
)
select ...
from MaxDate
inner join vkbr
    on vkbr.agID = MaxDate.agID
    and vkbr.Date = MaxDate.Date

#1