求一条两表统计查询语句
表A：

CLASSID USERID CLASSNAME
1 10 AAAAA
2 11 BBBBB
3 12 CCCCC
4 13 DDDDD
5 13 EEEEE

表B：

FILEID CLASSID FILEPATH
1 1 xxx1
2 1 xxx2
3 1 xxx3
4 2 xxx4
5 2 xxx5
6 4 xxx6

要求查询结果：

CLASSID USERID CLASSNAME CON
1 10 AAAAA 3
2 11 BBBBB 2
4 13 DDDDD 1
把为Con为0的结果去掉。或者ORDER BY排到后面也行。

[解决办法]

create table A(CLASSID int, USERID int,CLASSNAME nvarchar(10))
insert A select 1, 10, 'AAAAA '
union all select 2, 11, 'BBBBB '
union all select 3, 12, 'CCCCC '
union all select 4, 13, 'DDDDD '
union all select 5, 13, 'EEEEE '


create table B(FILEID int, CLASSID int, FILEPATH nvarchar(10))
insert B select 1, 1, 'xxx1 '
union all select 2, 1, 'xxx2 '
union all select 3, 1, 'xxx3 '
union all select 4, 2, 'xxx4 '
union all select 5, 2 , 'xxx5 '
union all select 6, 4, 'xxx6 '

select *,FILEPATH=(select count(*) from B where CLASSID=A.CLASSID)
from A
order by 4 desc

--result
CLASSID USERID CLASSNAME FILEPATH
----------- ----------- ---------- -----------
1 10 AAAAA 3
2 11 BBBBB 2
4 13 DDDDD 1
3 12 CCCCC 0
5 13 EEEEE 0

(5 row(s) affected)

[解决办法]
select *, (select count(1) from 表B where CLASSID=a.CLASSID) CON from 表A a

------解决方案--------------------

select #A.*,b.n from
(select CLASSID,count(*) n from #b group by CLASSID)b,#A
where #A.CLASSID=b.CLASSID
可能简单的效率还会高一点