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

表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

---

引用

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?-

感觉要写的相当复杂的,没想你这么容易就搞定了.佩服
呵呵,就此结贴了

---

引用

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?-

简洁明了  顶

---

晕乎乎

---

精巧，支持。

引用

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