22,300
社区成员




--sql 2000静态
select t1.* ,
max(case px when 1 then meno else '' end) date1,
max(case px when 2 then meno else '' end) date2,
max(case px when 3 then meno else '' end) date3
from t1 ,
(select t.* , px = (select count(1) from t2 where id = t.id and adate < t.adate) = 1 from t2 t) m
where t1.id = m.id
group by t1.id , t1.name , t1.meno
--sql 2000动态
declare @sql varchar(8000)
set @sql = 'select t1.* '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then meno else '''' end) [date' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = (select count(1) from t2 where id = t.id and adate < t.adate) = 1 from t2 t) m ) as a
set @sql = @sql + ' from t1 ,
(select t.* , px = (select count(1) from t2 where id = t.id and adate < t.adate) = 1 from t2 t) m
where t1.id = m.id
group by t1.id , t1.name , t1.meno'
exec(@sql)
<table width="381" border="1">
<tr>
<td width="14">id</td>
<td width="28">name</td>
<td width="21">age</td>
<td width="69">2002-1-1</td>
<td width="65">2002-1-1</td>
<td width="67">2003-1-1</td>
<td width="71">2004-1-1</td>
</tr>
<tr>
<td>1</td>
<td>aa</td>
<td>22</td>
<td>s1</td>
<td>s7</td>
<td>s2</td>
<td> </td>
</tr>
<tr>
<td>2</td>
<td>bb</td>
<td>33</td>
<td>s3</td>
<td> </td>
<td> </td>
<td>s5</td>
</tr>
<tr>
<td>3</td>
<td>cc</td>
<td>30</td>
<td> </td>
<td> </td>
<td>s4</td>
<td>s6</td>
</tr>
<tr>
<td>4</td>
<td>dd</td>
<td>31</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>