mysql - SQl 查询 - 每组有 2 个最高值，第二个必须是 "younger"，然后是第一个

Question

我有 MySQL 表。我需要为每组选择两个最高值，但第二高值不可能是“较新的”。如果项目 1 的最高值是日期“2015-12-01”，则第二高值必须早于“2015-12-01”。该日期是用户在应用程序中输入的日期，例如:用户输入日期“2014-01-03”并按日期获取 2 个最高值。

1. '2013-12-05' - 500 次访问

2. '2013-10-11' - 480 人访问

   + --------- + ----------- + ---------+
   | project   | visits      | date     |
   + --------- + ----------- + ---------+
   | 1         | 750         |2015-12-01|
   | 1         | 582         |2014-11-01|        
   | 1         | 423         |2013-10-02|
   | 2         | 666         |2016-01-01|
   | 2         | 812         |2012-02-02|
   | 2         | 450         |2014-03-05|
   + --------- + ----------- + -------- +
   

编辑:我尝试这个(它比上面的示例更复杂)+由于ProjectName而连接表

set @num := 0, @group := '';

select ProjectName,visits, date
                    from 
                    (
                    select date, Projects_ID, visits,
                    @num := if(@group = `Projects_ID`, @num + 1, 1) as row_number,
                    @group := Projects_ID as col
                    from Hits
                    where date < '2016-01-11' and visits > 0
                    order by Projects_ID,date, visits desc
                    ) as x inner join Projects as p on x.Projects_ID = 
                    p.ID
                    where x.row_number <= 2
                    group by ProjectName,Projects_ID, date

我得到了最高值的表格，但在某些情况下，第二高值是“最新”然后是最高值。

Answer 1

SELECT a.visits, a.project, a.date 
FROM temper1 AS a 
WHERE (SELECT COUNT(*) 
       FROM temper1 AS b 
       WHERE b.project = a.project AND b.date >= a.date
       ) <= 2 
ORDER BY a.visits ASC, a.date DESC