现有两张表:
dbo.bargaindate:BargainDate,DateType,UpdateTime,id
dbo.tIndexReport:ID,IndexCode,IndexName,BargainDate,CloseIndex简单来说:表一记录了所有交易日期,表二记录了每个指数每个交易日的收盘价,但有些指数缺少部分交易日的数据,需要找出来。小弟初学SQL,写了以下代码,只能查一个指数的缺少情况,求高人改下,可以把所有指数一次都查出来,结果显示出indexname,bargaindate(缺失的交易日)。拜谢。
select *
from (
select dbo.bargaindate.BargainDate,a.closeindex
from dbo.bargaindate
left join (select * from dbo.tIndexReport where IndexName='有色金属' ) as a
on dbo.bargaindate.BargainDate=a.BargainDate
where dbo.bargaindate.bargaindate>(select min(bargaindate) from dbo.tindexreport where indexname='有色金属')-1 and dbo.bargaindate.bargaindate<'2011-12-31'
) as b
where b.closeindex is null
order by b.bargaindate
dbo.bargaindate:BargainDate,DateType,UpdateTime,id
dbo.tIndexReport:ID,IndexCode,IndexName,BargainDate,CloseIndex简单来说:表一记录了所有交易日期,表二记录了每个指数每个交易日的收盘价,但有些指数缺少部分交易日的数据,需要找出来。小弟初学SQL,写了以下代码,只能查一个指数的缺少情况,求高人改下,可以把所有指数一次都查出来,结果显示出indexname,bargaindate(缺失的交易日)。拜谢。
select *
from (
select dbo.bargaindate.BargainDate,a.closeindex
from dbo.bargaindate
left join (select * from dbo.tIndexReport where IndexName='有色金属' ) as a
on dbo.bargaindate.BargainDate=a.BargainDate
where dbo.bargaindate.bargaindate>(select min(bargaindate) from dbo.tindexreport where indexname='有色金属')-1 and dbo.bargaindate.bargaindate<'2011-12-31'
) as b
where b.closeindex is null
order by b.bargaindate
(select indexcode, max(indexname)name, min(bargaindate)md from tindexreport group by indexcode) a
inner join bargaindate b on a.md<=b.bargaindate and b.bargaindate<'2011-12-31'
left join tindexreport c on a.indexcode=b.indexcode and b.bargaindate=c.bargaindate
where c.indexcode is null
列名 'indexcode' 无效。
手误手误:left join tindexreport c on a.indexcode=c.indexcode and b.bargaindate=c.bargaindate