SELECT A.A1, A.A2, A.A3 FROM A LEFT OUTER JOIN B ON A.A1 = B.A1 where B.A1='2' 有问题么~!?
改一下 SELECT A.A1, A.A2, A.A3 FROM A LEFT OUTER JOIN B ON A.A1 = B.A1 where B.类型='2'
SELECT A.A1, A.A2, A.A3 FROM A LEFT OUTER JOIN B ON A.A1 = B.A1 where B.A1='2' 为什么都是一样的!?
xinggg(无名风) : 先试了再说话不迟!用的LEFT JOIN
Yang_(扬帆破浪) 我用的是right join 因为实际使用涉及到多张表,但效果应该是一样的
ok,果然错了:select a.a1,a.a2,a.a3,isnull(count(c.类型),0) as cntType from a left join (select * from b where 类型=‘2’) as c on a.a1=c.a1 group by a.a1,a.a2,a.a3
你的字段里有A3,不要A2、A3可以:select a.a1,isnull(count(c.类型),0) as cntType from a left join (select * from b where 类型=‘2’) as c on a.a1=c.a1 group by a.a1
试一试: select A.a1, (select count(*) from B where B.A1= A.A1 and B.类型='2') as B中类型为2的数目 from A
MS SQL Server 7.0 和2000 中可以,因为它们支持在线视图。 其他数据库可以先将select * from b where 类型='2'生成临时表,再和临时表关联。也可以这样select a.a1,a.a2,a.a3,isnull(count(b.类型),0) as cntType from a left outer join b on a.a1=b.a1 and b.类型='2' group by a.a1,a.a2,a.a3MS SQL Server 中 旧的语法 select a.a1,a.a2,a.a3,isnull(count(b.类型),0) as cntType from a,b on a.a1*=b.a1 and b.类型='2' group by a.a1,a.a2,a.a3另外,Yang_(扬帆破浪)用了全角的单引号。
FROM A LEFT OUTER JOIN
B ON A.A1 = B.A1 where B.A1='2'
有问题么~!?
SELECT A.A1, A.A2, A.A3
FROM A LEFT OUTER JOIN
B ON A.A1 = B.A1 where B.类型='2'
FROM A LEFT OUTER JOIN
B ON A.A1 = B.A1 where B.A1='2'
为什么都是一样的!?
先试了再说话不迟!用的LEFT JOIN
我用的是right join 因为实际使用涉及到多张表,但效果应该是一样的
select
A.a1,
(select count(*) from B where B.A1= A.A1 and B.类型='2') as B中类型为2的数目
from A
其他数据库可以先将select * from b where 类型='2'生成临时表,再和临时表关联。也可以这样select a.a1,a.a2,a.a3,isnull(count(b.类型),0) as cntType
from a left outer join b on a.a1=b.a1 and b.类型='2'
group by a.a1,a.a2,a.a3MS SQL Server 中 旧的语法
select a.a1,a.a2,a.a3,isnull(count(b.类型),0) as cntType
from a,b on a.a1*=b.a1 and b.类型='2'
group by a.a1,a.a2,a.a3另外,Yang_(扬帆破浪)用了全角的单引号。