高手请进,关于mysql的复杂统计汇总

[pipi521520]

表A
字段名 id flag pid
1 1 111
2 3 222
3 1 111
4 1 333
5 2 222

表B
字段名 id opention pid
1 1 111
2 1 111
3 2 111
4 1 222
5 2 222
表C
id name
111 A
222 B
333 C
请问如何统计A表中flag为1和flag为2以及B表中opention为1的数据量并以pid聚类.表A和表B中的pid关联到表C的id
结果如下所示

pid flag1 flag2 opantion1
111 2 0 2
222 0 1 1
333 1 0 0

[ACMAIN_CHM]

select c.pid,t1.flag1,t1.flag2,t2.opantion1
from c left join (
	select pid,
		sum(if(flag=1,1,0)) as flag1 ,
		sum(if(flag=2,1,0)) as flag2 
	from a where flag=1 or flag=2 group by pid
	) t1 on c.pid=t1.pid
	left join (select pid,count(*) as opantion1 from b where opention=1 group by pid) t2
	on c.pid=t2.pid

[pipi521520]

[Quote=引用 1 楼 acmain_chm 的回复:]
SQL codeselect c.pid,t1.flag1,t1.flag2,t2.opantion1from cleftjoin (select pid,sum(if(flag=1,1,0))as flag1 ,sum(if(flag=2,1,0))as flag2from awhere flag=1or flag=2groupby pid
) t1on c.pid=t1.pidleft?-
[/Quote]
感觉要写的相当复杂的,没想你这么容易就搞定了.佩服
呵呵,就此结贴了

[lovebin_bin]

[Quote=引用 1 楼 acmain_chm 的回复:]
SQL codeselect c.pid,t1.flag1,t1.flag2,t2.opantion1from cleftjoin (select pid,sum(if(flag=1,1,0))as flag1 ,sum(if(flag=2,1,0))as flag2from awhere flag=1or flag=2groupby pid
) t1on c.pid=t1.pidleft?-
[/Quote]

简洁明了 顶

[willhome28]

晕乎乎

[平凡的思想者]

精巧，支持。

[Quote=引用 1 楼 acmain_chm 的回复:]
SQL codeselect c.pid,t1.flag1,t1.flag2,t2.opantion1from cleftjoin (select pid,sum(if(flag=1,1,0))as flag1 ,sum(if(flag=2,1,0))as flag2from awhere flag=1or flag=2groupby pid
) t1on c.pid=t1.pidleftjoin (select pid,count(*)as opantion1from bwhere opention=1groupby pid) t2on c.pid=t2.pid
[/Quote]