求一条SQL数据

[智者潜行]

有两个表,一个是群组的,一个是群组里面的用户.

群组.Group
--------------------------
ID GRoupName

159 用户
160 户口
162 dsfds
---------------------------------

群成员.GroupMember
----------------------------------
GroupID Username power

160 test1 1
160 cai 1
160 wxh 0
160 admin 0
159 test1 0
159 cai 1
159 wxh 0
159 admin 1
------------------------------
我想查到的是群组(Group),然后加一列显示每个群组里面用户的数量,以前写过,但忘了,请问要怎么写?

[csdyyr]

select ID,GRoupName,COUNT(*) AS NUM
from [Group] g join GroupMember m on g.id=m.GroupID  
group by ID,GRoupName

[mugua604]

select a.GroupName,count(1) 
from GroupMember a
left join Group b on a.ID=b.GroupID
group by a.GroupName

[智者潜行]

终于被我研究出来了.\
SELECT U_ID,U_Name,(select count(*) from GroupMember where GroupID in(U_ID)) as No FROM [UserGroup]

[htl258_Tony]

select *,cnt=(select count(1) from GroupMember where groupid=t.id) from [group] t

[sdhdy]

--power是什么意思？
select ID , GRoupName ,用户数量=(select count(1) from GroupMember where GroupID=a.GroupID) from [group] a

[playwarcraft]

select A.GroupName, Count(B.GroupID) as cnt
from [Group] A
left join GroupMember B
on A.ID=B.GroupID
group by A.GroupName

[让你望见影子的墙]

select a.id,count(*) from [group] a join [GroupMember] b on a.id=b.GroupID group by a.id

[让你望见影子的墙]

-->> Author:  让你望见影子的墙(HEROWANG)生成测试数据  Date:2009-04-24 10:57:19
IF OBJECT_ID('[group]') IS NOT NULL 
    DROP TABLE [group]
Go
CREATE TABLE [group](ID INT,GRoupName NVARCHAR(5))
Go
INSERT INTO [group]
SELECT 159,'用户' UNION ALL
SELECT 160,'户口' UNION ALL
SELECT 162,'dsfds' 
GO

SELECT 	* FROM	[GROUP]


-->> Author:  让你望见影子的墙(HEROWANG)生成测试数据  Date:2009-04-24 10:58:07
IF OBJECT_ID('GroupMember') IS NOT NULL 
    DROP TABLE GroupMember
Go
CREATE TABLE GroupMember(GroupID INT,Username NVARCHAR(5),power INT)
Go
INSERT INTO GroupMember
SELECT 160,'test1',1 UNION ALL
SELECT 160,'cai',1 UNION ALL
SELECT 160,'wxh',0 UNION ALL
SELECT 160,'admin',0 UNION ALL
SELECT 159,'test1',0 UNION ALL
SELECT 159,'cai',1 UNION ALL
SELECT 159,'wxh',0 UNION ALL
SELECT 159,'admin',1 
GO

SELECT 	* FROM	GROUPMEMBER

select a.id,count(*) from [group] a join [GroupMember] b on a.id=b.GroupID group by a.id

id	(无列名)
159	4
160	4

[htl258_Tony]

if object_id('[group]') is not null drop table [group] 
 go 
create table [group]([ID] int,[GRoupName] varchar(10))
insert [group] select 159,'用户'
union all select 160,'户口'
union all select 162,'dsfds'
go
if object_id('GroupMember') is not null drop table GroupMember 
 go 
create table GroupMember([GroupID] int,[Username] varchar(10),[power] int)
insert GroupMember select 160,'test1',1
union all select 160,'cai',1
union all select 160,'wxh',0
union all select 160,'admin',0
union all select 159,'test1',0
union all select 159,'cai',1
union all select 159,'wxh',0
union all select 159,'admin',1
go
select *,cnt=(select count(1) from GroupMember where groupid=t.id) from [group] t
/*
ID          GRoupName  cnt
----------- ---------- -----------
159         用户         4
160         户口         4
162         dsfds      0

(3 行受影响)
*/

[sdhdy]

[Quote=引用 3 楼 lfoy112 的回复:]
终于被我研究出来了.\
SELECT U_ID,U_Name,(select count(*) from GroupMember where GroupID in(U_ID)) as No FROM [UserGroup]
[/Quote]

--不要用IN,会影响到速度的，这样
--power是什么意思？
select ID , GRoupName ,用户数量=(select count(1) from GroupMember where GroupID=a.GroupID) from [group] a

[mamoon]

恭喜恭喜，不过还是2楼的写法好点

更正一下

SELECT a.ID,a.GroupName,count(*)
FROM Group a
LEFT JOIN GroupMember b on a.ID=b.GroupID
GROUP BY a.ID,a.GroupName

[mugua604]

CREATE TABLE [Group] 
      ( 
      ID INT  , 
      GRoupName varchar(10) , 
      ) 
 
INSERT INTO [Group]  
SELECT 159,'用户' UNION ALL 
SELECT 160, '户口' UNION ALL 
SELECT 162, 'dsfds' 

CREATE TABLE GroupMember 
( 
GroupID INT, 
Username varchar(10), 
[power] INT, 
)      
           
INSERT INTO [GroupMember]  
SELECT 160,'test1',1 UNION ALL 
SELECT 160, 'cai',1 UNION ALL 
SELECT 160, 'wxh',0 UNION ALL 
SELECT 160, 'admin',0 UNION ALL 
SELECT 159, 'test1',0 UNION ALL 
SELECT 159, 'cai',1 UNION ALL 
SELECT 159, 'wxh',0 UNION ALL 
SELECT 159, 'admin',1

select b.GRoupName,count(1) 
from GroupMember a
left join [Group] b on b.ID=a.GroupID
group by b.GRoupName

/*
GRoupName  
---------- -----------
户口         4
用户         4

(2 行受影响)

*/

[taoistong]

select a.GroupName, isnull(count(1) ,0) '数量'
from GroupMember a
left join Group b on a.ID=b.GroupID
group by a.GroupName

这样

[lg3605119]

楼主可以结贴了