下面这种情况中,不使用Group By 如何实现.A和B是一对多的关系:下面这句话中,我不想使用Group by
因为结果集中需要的A的字段非常多。
如果不使用Goup by实现呢?
SELECT distinct
A.Field1,
A.Field2,
A.Field3,
A....
Count(B.Field1) as BFiled1,
Count(B.Field2) as BFiled2
FROM A INNER JOIN B ON
A.Id=B.Id
因为结果集中需要的A的字段非常多。
如果不使用Goup by实现呢?
SELECT distinct
A.Field1,
A.Field2,
A.Field3,
A....
Count(B.Field1) as BFiled1,
Count(B.Field2) as BFiled2
FROM A INNER JOIN B ON
A.Id=B.Id
from a
LEFT JOIN
(
select id, field1 = count(field1), field2 = count(field2)
from b
group by id
) b on b.id = a.id
A.Field1,
A.Field2,
A.Field3,
A....
(select Count(B.Field1) from b where A.Id=B.Id)as BFiled1,
(select Count(B.Field2) from b where A.Id=B.Id)as BFiled2
FROM A INNER JOIN B ON
A.Id=B.Id