select userid, table1总数=sum(case tb when 't1' then 1 else 0 end), table2总数=sum(case tb when 't1' then 2 else 0 end), table3总数=sum(case tb when 't1' then 3 else 0 end), table4总数=sum(case tb when 't1' then 4 else 0 end) from ( select tb='t1',* from t1 union all select tb='t2',* from t1 union all select tb='t3',* from t1 union all select tb='t4',* from t1 ) t group by userid
我已经做出来了,不过非常感谢你的回帖 select userid, (select count(userid) from t_test1 where t_test1.userid=t.userid) as 't1总数', (select count(userid) from t_test2 where t_test2.userid=t.userid) as 't2总数', (select count(userid) from t_test3 where t_test3.userid=t.userid) as 't3总数', (select count(userid) from t_test4 where t_test4.userid=t.userid) as 't4总数'from( select userid from t_test1 group by userid union select userid from t_test2 group by userid union select userid from t_test3 group by userid union select userid from t_test4 group by userid ) as t在网上找了很久,终于找到了 union这个好方法。
table1总数=sum(case tb when 't1' then 1 else 0 end),
table2总数=sum(case tb when 't1' then 2 else 0 end),
table3总数=sum(case tb when 't1' then 3 else 0 end),
table4总数=sum(case tb when 't1' then 4 else 0 end)
from (
select tb='t1',* from t1
union all
select tb='t2',* from t1
union all
select tb='t3',* from t1
union all
select tb='t4',* from t1
) t
group by userid
select
userid,
(select count(userid) from t_test1 where t_test1.userid=t.userid) as 't1总数',
(select count(userid) from t_test2 where t_test2.userid=t.userid) as 't2总数',
(select count(userid) from t_test3 where t_test3.userid=t.userid) as 't3总数',
(select count(userid) from t_test4 where t_test4.userid=t.userid) as 't4总数'from(
select userid from t_test1 group by userid
union
select userid from t_test2 group by userid
union
select userid from t_test3 group by userid
union
select userid from t_test4 group by userid
) as t在网上找了很久,终于找到了 union这个好方法。