select concat("新闻,",t1.用户ID),t1.c from (select 用户ID,count(ID) as c from 新闻表 group by 用户ID) as t1 union select concat("图片,",t2.用户ID),t2.c from (select 用户ID,count(ID) as c from 图片表 group by 用户ID) as t2 union select concat("视频,",t3.用户ID),t3.c from (select 用户ID,count(ID) as c from 视频表 group by 用户ID) as t3
select a.id as 人员编号,a.name as 人员名称,b.count as 新闻,c.count as 图片,d.count as 视频 from (select distinct id,name from 人员) a left join (select id ,count(id) as count from 新闻 group by id) b on a.id =b.id left join (select id ,count(id) as count from 图片 group by id) c on a.id =c.id left join (select id ,count(id) as count from 视频 group by id) d on a.id =d.id
create table personinfo ( id int not null primary key ); create table news1 ( id int not null auto_increment primary key, uid int default null, key `fk_news1` (uid), constraint `fk_news1` foreign key (uid) references personinfo(id) ); create table picture ( id int not null auto_increment primary key, uid int default null, key `fk_pic` (uid), constraint `fk_pic` foreign key (uid) references personinfo(id) ); create table video ( id int not null auto_increment primary key, uid int default null, key `fk_video` (uid), constraint `fk_video` foreign key (uid) references personinfo(id) );insert into personinfo values (1),(2),(3); insert into news1(uid) values (1),(2),(3),(3),(2),(1),(3),(2); insert into picture(uid) values (1),(2),(3),(2),(1),(1),(2),(2); insert into video (uid) values (1),(2),(3),(3),(3),(3),(1),(1);查询语句: select id, sum(news_count) as 'news count', sum(picture_count) as 'picture count', sum(video_count) as 'video count' from ( select a.id,count(b.uid) as 'news_count',0 as 'picture_count',0 as 'video_count' from personinfo a,news1 b where a.id = b.uid group by a.id union all select a.id,0 as 'news_count',count(b.uid) as 'picture_count',0 as 'video_count' from personinfo a,picture b where a.id = b.uid group by a.id union all select a.id,0 as 'news_count',0 as 'picture_count',count(b.uid) as 'video_count' from personinfo a,video b where a.id = b.uid group by a.id ) T group by id order by id asc; 结果:query result(3 records) id news count picture count video count 1 2 3 6 2 3 4 2 3 3 1 8
union
select concat("图片,",t2.用户ID),t2.c from (select 用户ID,count(ID) as c from 图片表 group by 用户ID) as t2
union
select concat("视频,",t3.用户ID),t3.c from (select 用户ID,count(ID) as c from 视频表 group by 用户ID) as t3
from (select distinct id,name from 人员) a left join
(select id ,count(id) as count from 新闻 group by id) b on a.id =b.id
left join
(select id ,count(id) as count from 图片 group by id) c on a.id =c.id
left join
(select id ,count(id) as count from 视频 group by id) d on a.id =d.id
骑着上帝看戏
等 级:
发表于:2007-10-25 13:50:404楼 得分:0
简单的多表连接问题。
---------------------------
有没有简单一点的方法?
create table personinfo (
id int not null primary key
);
create table news1 (
id int not null auto_increment primary key,
uid int default null,
key `fk_news1` (uid),
constraint `fk_news1` foreign key (uid) references personinfo(id)
);
create table picture (
id int not null auto_increment primary key,
uid int default null,
key `fk_pic` (uid),
constraint `fk_pic` foreign key (uid) references personinfo(id)
);
create table video (
id int not null auto_increment primary key,
uid int default null,
key `fk_video` (uid),
constraint `fk_video` foreign key (uid) references personinfo(id)
);insert into personinfo values (1),(2),(3);
insert into news1(uid) values
(1),(2),(3),(3),(2),(1),(3),(2);
insert into picture(uid) values
(1),(2),(3),(2),(1),(1),(2),(2);
insert into video (uid) values
(1),(2),(3),(3),(3),(3),(1),(1);查询语句:
select id,
sum(news_count) as 'news count',
sum(picture_count) as 'picture count',
sum(video_count) as 'video count'
from (
select a.id,count(b.uid) as 'news_count',0 as 'picture_count',0 as 'video_count' from personinfo a,news1 b where a.id = b.uid group by a.id
union all
select a.id,0 as 'news_count',count(b.uid) as 'picture_count',0 as 'video_count' from personinfo a,picture b where a.id = b.uid group by a.id
union all
select a.id,0 as 'news_count',0 as 'picture_count',count(b.uid) as 'video_count' from personinfo a,video b where a.id = b.uid group by a.id
) T group by id order by id asc;
结果:query result(3 records)
id news count picture count video count
1 2 3 6
2 3 4 2
3 3 1 8