SQL 小技巧很久不搞sql了。整天就是java, java，也挺无聊的。其实想想，sql还是挺好玩的，尤其是把别人吭哧吭哧
SQL 小技巧
很久不搞sql了。整天就是java, java，也挺无聊的。
其实想想，sql还是挺好玩的，尤其是把别人吭哧吭哧几百行的stored procedure改写成一个简洁的query，或者一个本来要跑十分钟的stored proc改成几秒钟，感觉还是满爽的。
闲来无事，把一些小技巧回忆下来，省得以后考人的时候想不起来了。
1。一个基金performance表，记录着每个基金的每个月相比于前一个月的增长百分率，比如：
一月： 2.1（%）
二月： -0.5 (%)
三月：
...
十二月：15 (%)
基金用基金ticker来标识。
请列出每个基金从去年五月到今年五月的总共增长。
2。每个基金每个月都有打分，从一分到五分。请列出最近一年内每个基金被打五分的次数，四分的次数，三分的次数。如果一个基金被打至少三分的次数少于两次，就不要列出来了。
3。请针对上一年列出每个基金增长最好的那个月，包括基金ticker，当月的增长百分比，当月打分。如果两个月增长幅度相同，选择打分高的那个月；如果打分也相同，选择最近发生的那个月。
4。假设基金表不小心出现了重复数据，也就是ticker, 月份都相同，但是id是主键，唯一的。请删除重复记录。对重复记录，保留打分高的那条，如果打分也一样，保留其中任意一条。（其实，这个和三是一样的，不过算一个比较常见的dba要面临的问题）
（答案回头有时间写出来。提示：珍爱生命，远离存储过程！）
(I have discovered a truly marvelous proof of this, which this margin is too narrow to contain)
select ticker, (exp(sum(log(1 + percent/100))) - 1) / 100 as accumulated_percentfrom perf where date between startdate and enddate group by ticker
...
很不错的SQL 收藏了 
不过第一题有个小问题 计算增长率的部分应该是：
(exp(sum(log(1 + percent/100))) - 1) * 100
select ticker, (exp(sum(log(1 + percent/100))) - 1) * 100 as accumulated_percentfrom perf where date between startdate and enddate group by ticker
select x.ticker, x.star, x.percent from perf xleft join perf nothingon x.ticker = nothing.ticker and(x.percent < nothing.percent or (x.percent=nothing.percent and x.star < nothing.star or x.star=nothing.star and x.date < nothing.date))where nothing.ticker is null
在使用外链接的时候，where clause 跟left join on clause，数据库优化器会先使用哪个clause过滤数据啊？select x.ticker, x.star, x.percent from perf xleft join perf nothingon x.ticker = nothing.ticker and(x.percent < nothing.percent or (x.percent=nothing.percent and x.star < nothing.star or x.star=nothing.star and x.date < nothing.date))where nothing.ticker is null
在使用外链接的时候，where clause 跟left join on clause，数据库优化器会先使用哪个clause过滤数据啊？
这是根据索引来的，不是简单的谁先谁后。
咱们先假设ticker上有索引。
那么比如你的where clause如果有"and ticker in ('a','b','c')"，这个肯定就先index seek了。在seek出来的基础上，在怎么搞都无所谓了。
但是where nothing.ticker is null肯定不会是先被过滤的，因为做不到，只有join之后你才知道哪个ticker是null。
所以据我的经验，如果没有上面那种额外的过滤条件的话，根据数据量的大小，这里可能是一个merge join或者hash join配上table scan/index scan。select 基金表.PK , count(decode(基金表.分数, 5, 1, 0)) as count_5 , count(decode(基金表.分数, 4, 1, 0)) as count_4 , count(decode(基金表.分数, 3, 1, 0)) as count_3 , count(decode(基金表.分数, 5, 1, 4, 1, 3, 1, 0)) as count_543from 基金表group by 基金表.PKhaving count_543 > 2 真的不行的select 基金表.PK , count(decode(基金表.分数, 5, 1, 0)) as count_5 , count(decode(基金表.分数, 4, 1, 0)) as count_4 , count(decode(基金表.分数, 3, 1, 0)) as count_3 , count(decode(基金表.分数, 5, 1, 4, 1, 3, 1, 0)) as count_543from 基金表group by 基金表.PKhaving count_543 > 2
这里的
having count_543 > 2可以这么写吗？
没听说查询出来的别名可以作having过滤项。至少Oracle9i下这是不行的。select 基金表.PK , count(decode(基金表.分数, 5, 1, 0)) as count_5 , count(decode(基金表.分数, 4, 1, 0)) as count_4 , count(decode(基金表.分数, 3, 1, 0)) as count_3 , count(decode(基金表.分数, 5, 1, 4, 1, 3, 1, 0)) as count_543from 基金表group by 基金表.PKhaving count_543 > 2
这里的
having count_543 > 2可以这么写吗？
没听说查询出来的别名可以作having过滤项。至少Oracle9i下这是不行的。
的确，这是个笔误 36 楼 willmore 2008-09-23 不存在符合条件的记录,也就是唯一边界的记录,对于ON 只能用等值连接,所以WHERE里补空件. 37 楼 xiogxiog 2008-09-25 脱离SQL就是脱离本质， 搞那么多虚的东西是没用的
碰上专业的数据仓库比如TERRADATA，你就是得老老实实用他的专用查询语句
搞技术的也不要太执着，见人说人话，见鬼说鬼话即可！ 38 楼 gwinthis 2008-09-26 楼主举得例子有些极端，简单的SQL可以实现当然不用存储过程了。SQL语句和存储过程应该是没有矛盾的，我想大家遇到问题都是首先考虑使用SQL语句，如果SQL实现太复杂，会再考虑存储过程。特别是一些报表，字段来自很多表，并且需要复杂的计算。比如SQL语句包含6、7个表，左套一层右套一层，这样的语句让数据库来解析和执行很难想象会是什么样的结果，性能可想而知，编写难度也比较大，可读性当然也不会太好。在这种情况我觉得可以使用存储过程来代替一个复杂的SQL语句，把问题分散解决，对数据库，开发人员，维护人员都是个不错的选择。 39 楼 xushengcheng1985 2008-10-04 我想请教一下打分的标准是什么？
