求一句双表查询。

[netDust_cv]

表：smallClass
----------------------------------------------
id smallClassName bigClassName
1 s1 b2
2 s2 b2
3 s1 b1
4 s2 b1
5 s3 b1
-----------------------------------------------
表：product
----------------------------------------------
id smallClassName bigClassName title
1 s1 b1 1111
2 s1 b1 222222
3 s2 b1 33333
4 s1 b1 5555

----------------------------------------------


取出的结果为：
count smallClassName bigClassName
0 s1 b2
0 s2 b2
3 s1 b1
1 s2 b1
0 s3 b1






---------------------------------------------------
求SQL语句怎么样写。

[daijingjie2002]

select smallClassName,bigClassName,count=(select count(*) from product a where a.smallClassName=b.smallClassName and a.bigClassName=b.bigClassName) from smallClass b

[sdhdy]

select (select count(1) from product where smallClassName=a.smallClassName and bigClassName=a.bigClassName ),smallClassName,bigClassName from smallClass a

[sdhdy]

select (select count(1) from product where smallClassName=a.smallClassName and bigClassName=a.bigClassName ) count,smallClassName,bigClassName from smallClass a

[iswear428]

SELECT SUM(CASE B.TITLE IS NULL THEN 0 ELSE 1 ),A.smallClassName,A.bigClassName
FROM smallClass AS A LEFT JOIN product AS B
ON A.smallClassName = B.smallClassName AND A.bigClassName=B.bigClassName
GROUP BY A.smallClassName,A.bigClassName

[dxhdxh]

select smallClass.*,isnull(a.aa,0) aa from smallClass left join (select count(id) aa, product.smallClassName,product.bigClassName from product
group by product.smallClassName,product.bigClassName)a on a.smallClassName=smallClass.smallClassName and a.bigClassName=smallClass.bigClassName

[zhao1210]

select Count(1) as count,smallClassName,bigClassName from (select id,smallClassName,bigClassName from smallClass union select id,smallClassName,bigClassName from product) group by smallClassName,bigClassName