有两个表,一个是群组的,一个是群组里面的用户.群组.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),然后加一列显示每个群组里面用户的数量,以前写过,但忘了,请问要怎么写?
--------------------------
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),然后加一列显示每个群组里面用户的数量,以前写过,但忘了,请问要怎么写?
from [Group] g join GroupMember m on g.id=m.GroupID
group by ID,GRoupName
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]
--power是什么意思?
select ID , GRoupName ,用户数量=(select count(1) from GroupMember where GroupID=a.GroupID) from [group] a
select A.GroupName, Count(B.GroupID) as cnt
from [Group] A
left join GroupMember B
on A.ID=B.GroupID
group by A.GroupName
-->> 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'
GOSELECT * 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
GOSELECT * FROM GROUPMEMBERselect a.id,count(*) from [group] a join [GroupMember] b on a.id=b.GroupID group by a.idid (无列名)
159 4
160 4
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 行受影响)
*/
--power是什么意思?
select ID , GRoupName ,用户数量=(select count(1) from GroupMember where GroupID=a.GroupID) from [group] a
SELECT a.ID,a.GroupName,count(*)
FROM Group a
LEFT JOIN GroupMember b on a.ID=b.GroupID
GROUP BY a.ID,a.GroupName
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',1select b.GRoupName,count(1)
from GroupMember a
left join [Group] b on b.ID=a.GroupID
group by b.GRoupName/*
GRoupName
---------- -----------
户口 4
用户 4(2 行受影响)*/
select a.GroupName, isnull(count(1) ,0) '数量'
from GroupMember a
left join Group b on a.ID=b.GroupID
group by a.GroupName
这样