select * from daily_spo t_0
where not exists(
select 1 from daily_spo t_1
where t_1.stockCode = t_0.StockCode and t_1.IssueDate > t_0.IssueDate)改成
SELECT a.*
FROM daily_spo a,
(
SELECT stockCode,MAX(IssueDate) as MaxIssueDate
FROM daily_spo
GROUP BY stockCode
) b
WHERE a.stockCode=b.stockCode AND a.IssueDate=b.IssueDate--看改後速度如何,看情况再建六索引
CREATE INDEX ix_stockCode_IssueDate ON daily_spo(stockCode,IssueDate)
GO
SELECT a.*
FROM daily_spo a,
(
SELECT stockCode,MAX(IssueDate) as MaxIssueDate
FROM daily_spo
GROUP BY stockCode
) b
WHERE a.stockCode=b.stockCode AND a.IssueDate=b.IssueDate会占多长时间? 另, 看一下io读数是多少.在stockCode、IssueDate上建立索引,再执行以上语句,再看io数是否有少.在
第一种情况是两个表做完笛卡尔积再选出有效数据
而第二种情况下数据库会做适当的优化提高效率
刚学完这部分内容,有点忘记了,有不对的lx的指正吧