2022-12-18 11:35:02
•
1614
mysql count 统计多表数据
mysql 统计多表
------------------------------------------------------------------------------------------- select * from ((select count(*) as countnum from a_admin_code where classid=6) a,(select count(*) as countnum from a_admin_code_class where fid=1) b,(select count(*) as countnum from a_admin_company)c ) countnum countnum countnum 3 5 1 select a.countnum+b.countnum+c.countnum as allnum from ((select count(*) as countnum from a_admin_code where classid=6) a,(select count(*) as countnum from a_admin_code_class where fid=1) b,(select count(*) as countnum from a_admin_company)c ) allnum 9 统计一张表------------------------------------------------------------------------------------------- SELECT a.classid,count(*) as tjnum,b.classname,b.id FROM `a_admin_code` as a left join a_admin_code_class as b on a.classid=b.id group by classid ------------------------------------------------------------------------------------------- union all统计多表 现在需要把它们汇总加起来,可以使用如下方法实现 SELECT COUNT(cnt) FROM ( SELECT COUNT(id) AS cnt FROM test_0 WHERE field1 = 1 UNION ALL SELECT COUNT(id) AS cnt FROM test_1 WHERE field1 = 1 . . . UNION ALL SELECT COUNT(id) AS cnt FROM test9 WHERE field1 = 1 ) AS countdata 注:为UNION ALL的所有表做一个别名是必须的,虽然这个别名并没有什么意义 但如果没有的话mysql会报出错误:Every derived table must have its own alias ------------------------------------------------------------------------------------------- mysql count多表数据总和 SELECT a.countNum + b.countNum + c.countNum FROM ( (SELECT COUNT(*) AS countNum FROM table_a) a, (SELECT COUNT(*) AS countNum FROM table_b) b, (SELECT COUNT(*) AS countNum FROM table_c) c )
mysql count 统计多表数据