我有两张表,一个是应用信息表
appinfo
-----------
appid appver
1 1.0.0
2 1.0.0
3 1.0.0
还有一个是操作记录表
report
---------------
appid op time
1 2 20070101
1 3 20070102
1 4 20070102
2 2 20070104
2 3 20070104
1 3 20070105op的值是确定的,只有2、3、4,我要统计每一个appid,对应每一个op的数量
result
-----------------------------
appid op2 op3 op4
1 1 2 1
2 1 1 0
3 0 0 0
appinfo
-----------
appid appver
1 1.0.0
2 1.0.0
3 1.0.0
还有一个是操作记录表
report
---------------
appid op time
1 2 20070101
1 3 20070102
1 4 20070102
2 2 20070104
2 3 20070104
1 3 20070105op的值是确定的,只有2、3、4,我要统计每一个appid,对应每一个op的数量
result
-----------------------------
appid op2 op3 op4
1 1 2 1
2 1 1 0
3 0 0 0
appid int,op int, num int
)
insert table temp1 select appid,op,count(op) as num from report group by appid,opselect m.appid, a.num as op2, b.num as op3, c.num as op4from temp1 a, temp1 b, temp1 c, appinfo m
where a.op = 2 and b.op = 3 and c.op = 4
and m.appid = b.appid and m.appid = c.appid, and m.appid = m.appid如能用零时表和外连接更好
where a.op = 2 and b.op = 3 and c.op = 4
and m.appid = b.appid and m.appid = c.appid, and m.appid = m.appid
希望能有用一条SQL语句搞定的
可以的话OK
方案1
select m.appid, a.num as op2, b.num as op3, c.num as op4 from appinfo m
left join
(select appid,op,count(op) as num from report where op = 2 group by appid,op ) a on
m.appid = a.appid
left join
(select appid,op,count(op) as num from report where op = 3 group by appid,op ) b on
m.appid = b.appid
left join
(select appid,op,count(op) as num from report where op = 4 group by appid,op ) c on
m.appid = c.appid
方案2
select appid,
(select count(op) as num from report where op = 2 and appid =appinfo.appid) as op2,
(select count(op) as num from report where op = 3 and appid =appinfo.appid) as op3,
(select count(op) as num from report where op = 4 and appid =appinfo.appid) as op4
from appinfo