两表统计查询

[hfsross]

两个表都有一个字段class，想统计两个表里以class分类的总共有多少个
select class,count( *) as total from table1 group by class

如果我两个表分别为table1,table2，我想分别统计出来，如何做呢

[wwwwb]

select class,count( *) as total,1 from table1 group by class
UNION ALL
select class,count( *) as total,2 from table2 group by class

select class,count( *) as total from (SELECT * FROM table1
UNION ALL
SELECT * FROM table2
) group by class

[purple3196]

select a.class ,count(*) from (select class from table1 union all select class from table2) a group a.class

[wwwwb]

select class,count( *) as total from (SELECT * FROM table1
UNION ALL
SELECT * FROM table2
) T group by class

[ACMAIN_CHM]

select class,sum(total) from (
	select class,count(*) as total  from table1 group by class
	union all
	select class,count(*) as total  from table2 group by class
)
group by class

[平凡的思想者]

正解。
[Quote=引用 4 楼 acmain_chm 的回复:]
SQL codeselect class,sum(total)from (select class,count(*)as totalfrom table1groupby classunionallselect class,count(*)as totalfrom table2groupby class
)groupby class
[/Quote]

[ACMAIN_CHM]

哦，忘了加个别名。　先ＧＲＯＵＰ然后再ＳＵＭ效率上比较高。

select class,sum(total) from (
    select class,count(*) as total  from table1 group by class
    union all
    select class,count(*) as total  from table2 group by class
) t 
group by class

[军爷_010]

学习。。。