三个表的查询
a表username,score。其中username为关键字
-------------
username1 10
username2 12
username3 2
b表id,username,friendname。其中id为关键字
------------------------------
1 username1 f1
2 username1 f2
3 username2 f3
4 username1 f3
5 username3 f2
6 username2 f1
c表id,username,classmatename。其中id为关键字
--------------------------------
1 username1 c1
2 username2 c2
3 username2 c3
4 username1 c3
5 username1 c2要求
所有用户的username,score,b表中friendname的数目,c表中classname的数目
-------------------------------------
username1 10 3 3
username2 12 3 3
username3 2 1 0
不知道我说明白了没有,谢谢。
a表username,score。其中username为关键字
-------------
username1 10
username2 12
username3 2
b表id,username,friendname。其中id为关键字
------------------------------
1 username1 f1
2 username1 f2
3 username2 f3
4 username1 f3
5 username3 f2
6 username2 f1
c表id,username,classmatename。其中id为关键字
--------------------------------
1 username1 c1
2 username2 c2
3 username2 c3
4 username1 c3
5 username1 c2要求
所有用户的username,score,b表中friendname的数目,c表中classname的数目
-------------------------------------
username1 10 3 3
username2 12 3 3
username3 2 1 0
不知道我说明白了没有,谢谢。
a.username,count(b.friendname),count(c.classname)
from
a,b,c
where
a.username=b.username
and
a.username=c.username
group by
a.username
a.username,count(b.friendname),count(c.classname)
from
a,b,c
where
a.username=b.username
and
b.id=c.id
and
a.username=c.username
group by
a.username
a.username,count(b.friendname),isnull(count(c.classname),0)
from
a
join
b
on
a.username=b.username
join
c
on
b.id=c.id
group by
a.username
select a.username,a.score,count(b.friendname),count(c.classname)
from a inner join b on a.username=b.username
inner join c on a.username=c.username
group by a.username,a.score
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-04 14:49:50
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([username] varchar(9),[score] int)
insert [a]
select 'username1',10 union all
select 'username2',12 union all
select 'username3',2
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[username] varchar(9),[friendname] varchar(2))
insert [b]
select 1,'username1','f1' union all
select 2,'username1','f2' union all
select 3,'username2','f3' union all
select 4,'username1','f3' union all
select 5,'username3','f2' union all
select 6,'username2','f1'
--> 测试数据:[c]
if object_id('[c]') is not null drop table [c]
go
create table [c]([id] int,[username] varchar(9),[classmatename] varchar(2))
insert [c]
select 1,'username1','c1' union all
select 2,'username2','c2' union all
select 3,'username2','c3' union all
select 4,'username1','c3' union all
select 5,'username1','c2'
--------------开始查询--------------------------select
a.username,a.score,count(b.friendname),isnull(count(c.classmatename),0)
from
a
join
b
on
a.username=b.username
join
c
on
b.id=c.id
group by
a.username,a.score
----------------结果----------------------------
/* username score
--------- ----------- ----------- -----------
username1 10 3 3
username2 12 1 1
username3 2 1 1(3 行受影响)*/
select a.username, a.score, isnull(d.cntfriend,0) as cntfriend, isnull(e.cntclass,0) as cntclass from a
left join (select username, count(friendname) as cntfriend from b group by username) as d on a.username=d.username
left join (select username, count(classname) as cntclass from c group by username) as e on a.username=e.username