每天如何获取前两个最高的总金额

Question

我的表格数据如下所示。

declare @t table (name varchar(10), amt int, dt datetime)

insert into @t (name, amt, dt)
values 
     ('meeseva',100,'06-17-2015'), ('meeseva',200,'06-17-2015'),
     ('meeseva',200,'06-17-2015'), ('meeseva',100,'06-16-2015'),
     ('meeseva',100,'06-15-2015'), ('meeseva',100,'06-14-2015'),
     ('fish',100,'06-17-2015'), ('fish',200,'06-17-2015'),
     ('fish',100,'06-16-2015'), ('fish',200,'06-16-2015'),
     ('fish',100,'06-15-2015'), ('fish',100,'06-14-2015'),
     ('raju',100,'06-17-2015'), ('raju',200,'06-17-2015'),
     ('raju',100,'06-16-2015'), ('raju',100,'06-15-2015'),   
     ('raju',100,'06-14-2015'), ('raju',500,'06-14-2015')

到目前为止，我已经尝试过：

select 
    name,
    SUM(amt),
    dt,
    ROW_NUMBER() OVER (PARTITION BY name order by dt) 
from
    @t
where 
    dt >= (SELECT CONVERT (VARCHAR(10), Getdate() - 4, 101)) 
    and dt <= (SELECT CONVERT (VARCHAR(10), Getdate(), 101))      
GROUP BY 
    name, dt
ORDER BY 
    name, dt desc

我有一些数据，需要基于金额之和获取数据，每天最高的两个总额应该被返回，如果金额相同，则根据记录ID和日期返回前2条记录。

我的期望输出：

name    sum dt  
-----------------------------------
fish    300 2015-06-17 00:00:00.000 
meeseva 500 2015-06-17 00:00:00.000 
fish    300 2015-06-16 00:00:00.000 
raju    200 2015-06-16 00:00:00.000 
fish    100 2015-06-15 00:00:00.000
meeseva 100 2015-06-15 00:00:00.000
raju    600 2015-06-14 00:00:00.000 
meeseva 100 2015-06-14 00:00:00.000 

- mohan111

---

我认为你想要按[name]，[dt]进行分区，并按SUM([amt])排序并获取前2行的行号。 - Tab Alleman

搜索谷歌排名函数 ;) - Maciej Los

是的，我已经使用了排名函数，但是我无法找到适当的解决方案。 - mohan111

Answer 1

您可以尝试这个。

;WITH ranking AS(
SELECT  name,
        SUM(amt) [sum],
        dt,
        RANK() OVER (PARTITION BY dt ORDER BY SUM(amt) DESC, name -- use recordid here to order by sum then recordnumber
        ) AS rnk
FROM    @t
WHERE   dt >= DATEADD(DAY, -4, CONVERT(DATE, GETDATE()))
GROUP BY name,
        dt
)
SELECT  name,
        [sum],
        dt
FROM    ranking
WHERE   rnk <= 2
ORDER BY dt DESC,
        name

- JamieD77

Answer 2

请阅读我的对问题的评论。这里是一个样例查询：

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY dt ORDER BY amt DESC) AS RowNo
    FROM @t
) T 
WHERE RowNo IN (1, 2)
ORDER BY dt DESC

[编辑]

更符合您描述问题的样例：

SELECT t.*
FROM (
    SELECT name, dt, SUM(amt) AS amt, ROW_NUMBER() OVER(PARTITION BY dt ORDER BY SUM(amt) DESC) AS RowNo
    FROM @t
    WHERE dt BETWEEN ... AND ...
    GROUP BY name, dt 
) T
WHERE RowNo IN (1, 2) 
ORDER BY dt DESC

- Maciej Los

---

你能看到我想要的输出吗？尽管我已经采用了你的代码并实现了它，但它并没有给出正确的结果集。 - mohan111

Answer 3

您有一些正确的想法。但是，row_number()需要放入子查询或CTE中。您的日期算术也可以简化 - 将日期/时间转换为varchar以与日期进行比较没有意义。而且，您有一个存储日期的datetime列。该值允许具有时间组件（这是暗示），否则应将该列更改为date。

因此：

select t.*
from (select name, CAST(dt as DATE) as dt, sum(amt),
             row_number() over (partition by name order by sum(amt) desc) as seqnum
      from @t t
      where dt >= CAST(Getdate() - 4 as date) and
            dt <= CAST(Getdate() as date)      
      group by name, CAST(dt as DATE)
     ) t
where seqnum <= 2
order by name, dt desc;

- Gordon Linoff

Answer 4

尝试这个

DECLARE @t TABLE (
  name varchar(10),
  amt int,
  dt datetime
)
DECLARE @topRows int = 2

INSERT INTO @t (name, amt, dt)
  VALUES ('meeseva', 100, '06-17-2015'),
  ('meeseva', 200, '06-17-2015'),
  ('meeseva', 200, '06-17-2015'),
  ('meeseva', 100, '06-16-2015'),
  ('meeseva', 100, '06-15-2015'),
  ('meeseva', 100, '06-14-2015'),
  ('fish', 100, '06-17-2015'),
  ('fish', 200, '06-17-2015'),
  ('fish', 100, '06-16-2015'), ('fish', 200, '06-16-2015'), ('fish', 100, '06-15-2015'), ('fish', 100, '06-14-2015'),
  ('raju', 100, '06-17-2015'), ('raju', 200, '06-17-2015'),
  ('raju', 100, '06-16-2015'), ('raju', 100, '06-15-2015'), ('raju', 100, '06-14-2015'), ('raju', 500, '06-14-2015')

SELECT
  name,
  SUM(amt) [totalsum],
  dt,
  ROW_NUMBER() OVER (PARTITION BY dt ORDER BY SUM(amt) DESC, name) rw INTO #temp
FROM @t
WHERE dt >= (SELECT
  CONVERT(varchar(10), GETDATE() - 4, 101))
AND dt <= (SELECT
  CONVERT(varchar(10), GETDATE(), 101))
GROUP BY name,
         dt
ORDER BY dt DESC

SELECT
  *
FROM #temp
WHERE rw <= @topRows
ORDER BY dt DESC

DROP TABLE #temp

- Maddy