create table jb
(
  ID int,
  name nvarchar(3)
)

create table sy
(
  PERSONID int,
  XHDATE varchar(8)
)

create table jc
(
  PERSONID int,
  JCDATE varchar(8),
  JCJG nvarchar(2)
)

insert into jb 
select 1,'张三' union all
select 2,'李四'

insert into sy 
select 1,'20100101' union all
select 1,'20110101' union all
select 2,'20030101'

insert into jc 
select 1,'20111201','良好' union all
select 1,'20111225', '一般' union all
select 1,'20110105', '好'   union all 
select 2,'20110101', '较差' union all
select 2,'20110505', '良好'


select t1.ID,t1.name,SYCOUNT=(select COUNT(1) from sy where PERSONID=t1.ID),t2.JCDATE,t2.JCJG From jb t1 left join
(
select * from jc a where not exists (select 1 from jc where PERSONID=a.PERSONID and JCDATE>a.JCDATE)
) t2 on t1.ID=t2.PERSONID


ID          name SYCOUNT     JCDATE   JCJG
----------- ---- ----------- -------- ----
1           张三   2           20111225 一般
2           李四   1           20110505 良好

(2 行受影响)

------解决方案--------------------
楼上已经解决

------解决方案--------------------
SQL code
create table jb
(
  ID int,
  name nvarchar(3)
)

create table sy
(
  PERSONID int,
  XHDATE varchar(8)
)

create table jc
(
  PERSONID int,
  JCDATE varchar(8),
  JCJG nvarchar(2)
)

insert into jb 
select 1,'张三' union all
select 2,'李四'

insert into sy 
select 1,'20100101' union all
select 1,'20110101' union all
select 2,'20030101'

insert into jc 
select 1,'20111201','良好' union all
select 1,'20111225', '一般' union all
select 1,'20110105', '好'   union all 
select 2,'20110101', '较差' union all
select 2,'20110505', '良好'


select a.ID,max(a.name) name ,max(c.JCDATE) JCDATE,max(c.JCJG) JCJG from  jb a left join  sy b on  a.ID=b.PERSONID left join jc c on b.PERSONID=c.PERSONID
group by a.ID

------解决方案--------------------
探讨

left join 的意思是不管右边的条件符不符合，都参照左边，所以原本张三原本2个孩子结果为6个
select JB.ID, JB.NAME,COUNT(SY.PERSONID),MAX(JC.JCDATE),MAX(JC.JCJG)
from jb,sy,jc where jb.id=sy.personid and jb.id=jc.personid 应该可以的，不行的话，再Hi我 追问首……