SQL:获取GroupBY子句的第一个值

Question

USE AQOA_Core
SELECT TOP 10 
    p.Title, SUM(r.SalesVolume) AS Sales, c.WeekId AS ReleaseWeek
FROM 
    Product p
INNER JOIN 
    RawData r ON p.ProductId = r.ProductId
INNER JOIN 
    Calendar c ON r.DayId = c.DayId
WHERE 
    c.WeekId BETWEEN 20145227 AND 20145228
GROUP BY 
    p.Title, c.WeekId
ORDER BY 
    Sales DESC, ReleaseWeek DESC

上述查询的结果集为:

Title   Sales   ReleaseWeek
Movie1  10      20145228
Movie1  10      20145227
Movie2  10      20145228
Movie2  10      20145227
Movie3  10      20145228
Movie3  10      20145227
Movie4  10      20145228
Movie4  10      20145227
Movie5  10      20145228
Movie5  10      20145227

我基本上只需要ReleaseWeek列的第一周，但仍然需要groupby Title列

理想的结果集是这样的:

Title   Sales   ReleaseWeek
Movie1  20      20145228
Movie2  20      20145228
Movie3  20      20145228
Movie4  20      20145228
Movie5  20      20145228

我该怎么做呢?可能通过子查询?

这里使用的数据集只是为了简单的表示。实际数据集中的数据要大得多。

Answer 1

你试过了吗:

SELECT TOP 10 
    p.Title, SUM(r.SalesVolume) AS Sales, Min(c.WeekId) AS ReleaseWeek
FROM 
    Product p
INNER JOIN 
    RawData r ON p.ProductId = r.ProductId
INNER JOIN 
    Calendar c ON r.DayId = c.DayId
WHERE 
    c.WeekId BETWEEN 20145227 AND 20145228
GROUP BY 
    p.Title
ORDER BY 
    Sales DESC, ReleaseWeek DESC

你的周编号是字母或数字，所以据我所知，你将得到第一个星期的最小值。

我有一段时间没有使用这种技术了，我很确定它不适用于所有的db

另外，正如评论者提到的，你可能需要Max而不是Min，因为看看你的例子，你取的是最后一个日期，而不是第一个。

Answer 2

需要在子选择中选择前10行。然后从子选择中，使用聚合。这将选择像您的测试数据一样的最新的ReleaseWeek，而不是您的文本所描述的第一个ReleaseWeek。如果你是这个意思，你可以把它改成MIN:

;WITH CTE as
(
SELECT TOP 10 
    p.Title, r.SalesVolume, c.WeekId
FROM 
    Product p
INNER JOIN 
    RawData r ON p.ProductId = r.ProductId
INNER JOIN 
    Calendar c ON r.DayId = c.DayId
WHERE 
    c.WeekId BETWEEN 20145227 AND 20145228
ORDER BY 
    Sales DESC, ReleaseWeek DESC
)
SELECT 
  Title, SUM(SalesVolume) AS Sales, MAX(WeekId) ReleaseWeek
FROM CTE
GROUP BY Title

由于要聚合WeekId，因此不能将其包含在GROUP BY

Answer 3

您只需要第二列的最小值，因此您可以使用MIN():

SELECT TOP 10 p.Title, SUM(r.SalesVolume) AS Sales,
       MIN(c.WeekId) AS ReleaseWeek
FROM Product p INNER JOIN 
     RawData r
     ON p.ProductId = r.ProductId INNER JOIN 
     Calendar c
     ON r.DayId = c.DayId
WHERE c.WeekId BETWEEN 20145227 AND 20145228
GROUP BY p.Title
ORDER BY Sales DESC, ReleaseWeek DESC;

请注意，这需要从GROUP BY中删除c.weekid，并将MIN()添加到SELECT中。