生成此报告的最有效方法是什么? [英] What's the most efficient way to generate this report?

问题描述

给出一个表(daily_sales)，其中包含以下数据/列的10万行:

Given a table (daily_sales) with say 100k rows of the following data/columns:

id    rep   sales  date  
1     a     123    12/15/2011  
2     b     153    12/15/2011  
3     a      11    12/14/2011  
4     a     300    12/13/2011  
5     a     120    12/12/2011  
6     b     161    11/15/2011  
7     a       3    11/14/2011  
8     c      13    11/14/2011  
9     c      44    11/13/2011  

写报告(使用SQL完全显示每个名称的两个最新条目(代表，销售，日期))的最有效方法是什么，因此输出将是:

What would be the most efficient way to write a report (completely in SQL) showing the two most recent entries (rep, sales, date) for each name, so the output would be:

a  123  12/15/2011  
a   11  12/14/2011  
b  153  12/15/2011  
b  161  11/15/2011  
c   13  11/14/2011  
c   44  11/13/2011  

谢谢！

推荐答案

对于MySQL，在 @ Quassnoi的博客 ，它是(name, date)上的索引，并使用此索引:

For MySQL, explained in @Quassnoi's blog, an index on (name, date) and using this:

SELECT  t.*
FROM    (
        SELECT  name,
                COALESCE(
                (
                SELECT  date
                FROM    tableX ti
                WHERE   ti.name = dto.name
                ORDER BY
                        ti.name, ti.date DESC
                LIMIT 1 
                OFFSET 1                      --- this is set to 2-1 
                ), CAST('1000-01-01' AS DATE)) AS mdate
        FROM    (
                SELECT  DISTINCT name
                FROM    tableX dt
                ) dto
        ) tg
        , tableX t
WHERE   t.name >= tg.name
  AND   t.name <= tg.name
  AND   t.date >= tg.mdate

这篇关于生成此报告的最有效方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！