select * from (select id, count(*) as total from contentTable group by id) as t1, (select id, count(*) as monthlytotal from contentTable where year(`datetime`) = year(now()) and month(`datetime`) = month(now())) as t2 where t1.id = t2.id
select * from (select id, count(*) as total from contentTable group by id) as t1, (select id, count(*) as monthlytotal from contentTable where year(`datetime`) = year(now()) and month(`datetime`) = month(now()) group by id) as t2 where t1.id = t2.id
怎么 classname 能出来?
实现方法如下: 1.a表用于统计总的分类总数。 2.b表用于统计每月的分类总数。 3.c表用于查统最终结果:classname,total,monthTotalSQL如下:Select a.classname, a.total, b.monthTotal from ( ( Select c1.classid, c1.classname, count(*) as total from classTable c1, ContentTable c2 where c1.classid=c2.classid group by c1.classid, c1.classname ) a, ( Select c1.classid, c1.classname, count(*) as monthTotal from classTable c1, ContentTable c2 where c1.classid=c2.classid and extract(year_month from c2.dateime)=extract(year_month from '2008-10-01') group by c1.classid, c1.classname ) b) c;
Select a.classname, a.total, b.monthTotal from (
( Select c1.classid, c1.classname, count(*) as total from classTable c1, ContentTable c2 where c1.classid=c2.classid group by c1.classid, c1.classname ) a, ( Select c1.classid, c1.classname, count(*) as monthTotal from classTable c1, ContentTable c2 where c1.classid=c2.classid and extract(year_Month from c2.dateime)=extract(year_month from '2008-10-01') group by c1.classid, c1.classname ) b
(select id, count(*) as monthlytotal from contentTable where year(`datetime`) = year(now()) and month(`datetime`) = month(now())) as t2 where t1.id = t2.id
(select id, count(*) as monthlytotal from contentTable where year(`datetime`) = year(now()) and month(`datetime`) = month(now()) group by id) as t2 where t1.id = t2.id
实现方法如下:
1.a表用于统计总的分类总数。
2.b表用于统计每月的分类总数。
3.c表用于查统最终结果:classname,total,monthTotalSQL如下:Select a.classname, a.total, b.monthTotal from (
(
Select c1.classid, c1.classname, count(*) as total from classTable c1, ContentTable c2
where c1.classid=c2.classid
group by c1.classid, c1.classname
) a,
(
Select c1.classid, c1.classname, count(*) as monthTotal from classTable c1, ContentTable c2
where c1.classid=c2.classid and extract(year_month from c2.dateime)=extract(year_month from '2008-10-01')
group by c1.classid, c1.classname
) b) c;
(
Select c1.classid, c1.classname, count(*) as total from classTable c1, ContentTable c2
where c1.classid=c2.classid
group by c1.classid, c1.classname
) a, (
Select c1.classid, c1.classname, count(*) as monthTotal from classTable c1, ContentTable c2
where c1.classid=c2.classid and extract(year_Month from c2.dateime)=extract(year_month from '2008-10-01')
group by c1.classid, c1.classname
) b
where a.classid=b.classid) c;