需要写一条查询语句排遣寂寞:MSSQL
select distinct * from TableXXX a
where exists(select * from (select SecId, max(EffectiveDate) as LatestDate from TableXXX group by SecId) x
where a.SecId=x.SecId and a.EffectiveDate = x.LatestDate ) and a.SecId in ('E0GBR00MUG',
'E0GBR017E9',
'E0GBR005M3',
'E0GBR00KQG',
'E0GBR017PJ',
'E0GBR04DXC',
'E0GBR00D6A',
'E0GBR003OI',
'E0GBR00NGD',
'E0GBR00BAG',
'E0GBR01NRL',
'E0GBR010IO',
'E0GBR017FJ',
'F00000H0QA',
'E0GBR004NL',
'E0GBR00SKM',
'E0GBR003I5',
'E0GBR04E6V',
'E0GBR006J6',
'E0GBR017P8',
'F00000SY0G',
'E0GBR01AHE',
'E0GBR00CS6',
'E0GBR01ACI',
'E0GBR0059V',
'E0GBR00EHK',
'E0GBR00BV3',
'E0GBR01JX0',
'E0GBR006D5',
'F000000BD9',
'E0GBR006GM',
'F00000TORD',
'E0GBR04BLL',
'E0GBR0081V')order by SecId
效果:
mysql 的写法:
select h.SecuCode,g.* from LC_ShareStru g
inner join SecuMain h
on g.CompanyCode = h.CompanyCode
inner join
(
select a.CompanyCode,max(a.EndDate) as Ending from LC_ShareStru a
inner join SecuMain b
on a.CompanyCode = b.CompanyCode
where a.EndDate <= '2018-05-02'
and b.SecuCode in ('600000',
'600016',
'600019',
'600028',
'600029',
'600030',
'600036',
'600048',
'600050',
'600104',
'600111',
'600309',
'600340',
'600518',
'600519',
'600547',
'600606',
'600837',
'600887',
'600919',
'600958',
'600999',
'601006',
'601088',
'601166',
'601169',
'601186',
'601211',
'601229',
'601288',
'601318',
'601328',
'601336',
'601390',
'601398',
'601601',
'601628',
'601668',
'601669',
'601688',
'601766',
'601800',
'601818',
'601857',
'601878',
'601881',
'601985',
'601988',
'601989',
'603993')
group by a.CompanyCode
) as d on
g.CompanyCode = d.CompanyCode and g.EndDate = d.Ending