本文介绍了生成此报告的最有效方法是什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
给出一个表(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更多推荐
生成此报告的最有效方法是什么?
发布评论