救急！奇怪的join和聚合函数的应用
<p>先看看sql 语句：</p><p> select *<br/> from<br/> (<br/> select CompanyID, Max(NewsDate) as LastestNewsDate<br/> from news<br/> group by CompanyID<br/> )c<br/> LEFT JOIN<br/> (<br/> select SecurityID, Max(PriceDate) as LastestPriceDate<br/> from stock_data<br/> group by SecurityID<br/> )b<br/> ON b.SecurityID = c.CompanyID<br/>=========================================================<br/>现象如下：执行的结果中LastestPriceDate 总是和LastestNewsDate完全一样（当然实际table中的数据不一样），感觉就像是copy的。感觉极其想不通，跟踪了一下query plan(DBArtisan 8.1)，结果：</p><p>QUERY PLAN FOR STATEMENT 1 (at line 1).<br/><br/><br/> STEP 1<br/> The type of query is SELECT (into Worktable1).<br/> GROUP BY<br/> Evaluate Grouped MAXIMUM AGGREGATE.<br/><br/> FROM TABLE<br/> news<br/> Nested iteration.<br/> Table Scan.<br/> Forward scan.<br/> Positioning at start of table.<br/> Using I/O Size 8 Kbytes for data pages.<br/> With LRU Buffer Replacement Strategy for data pages.<br/> TO TABLE<br/> Worktable1.<br/><br/> STEP 2<br/> The type of query is SELECT.<br/><br/> FROM TABLE<br/> Worktable1.<br/> Nested iteration.<br/> Table Scan.<br/> Forward scan.<br/> Positioning at start of table.<br/> Using I/O Size 8 Kbytes for data pages.<br/> With MRU Buffer Replacement Strategy for data pages.<br/><br/> FROM TABLE<br/> Worktable1.<br/> Nested iteration.<br/> Using Clustered Index.<br/> Forward scan.<br/> Positioning by key.<br/> Using I/O Size 8 Kbytes for data pages.<br/> With MRU Buffer Replacement Strategy for data pages.<br/>感觉<br/> select SecurityID, Max(PriceDate) as LastestPriceDate<br/> from stock_data<br/> group by SecurityID中的聚合函数根本就没有执行。</p><p>请高手指教，</p>
页:
[1]