select d.name,count(u.*) from User u inner join dept d on u. deptids=d.ID group by d.name
首先 deptids里面存的dept的ID用“;”或者别的不要混淆的符号隔开 SELECT dept.name,CountValue=(SELECT COUNT(*) FROM User WHERE User.depdids LIKE '%dept.ID%') FROM dept
dept表数据 ID Name user表数据: id name deptIDs 1 行政 1 张七 2,3, 2 生产部 2 李四 1,3, 3 XX部 3 随便 1, 4 随便1 1,2,这是测试数据,我给出、了
修正一下,LIKE后面改为 ('%'+dept.ID+'%')
是哦,deptids 这个字段是怎么存储的都不说怎么搞
create table [User] ( id int, [name] varchar(10), deptid int )create table dept ( id int, [name] varchar(10) ) insert into [user] select 1,'user1',1 union select 2,'user2',1 union select 3,'user3',1 union select 4,'user4',2 union select 5,'user5',2insert into dept select 1,'dept1' union select 2,'dept2'select count([user].id),dept.[name] from [User] inner join dept on [user].deptid = dept.id group by dept.id,dept.[name]
select b.name,count(a.name) as 人数 from user a left join dept b on a.deptids=b.id group by b.name
declare @User table( useid int , userName varchar(20) , deptids varchar(50)) declare @dept table( deptid varchar(20) , deptname varchar(20))insert into @User values( 1 , 'name1' , '1,2,3') insert into @User values( 2 , 'name1' , '1')insert into @dept values( 1 , 'dept1') insert into @dept values( 2 , 'dept2') insert into @dept values( 3 , 'dept3')select *, (select Count( distinct useid) from @User where PATINDEX( '%'+deptid+',%',deptids+',')<>0) as useCount from @dept
User表 ID name deptids /////一个人可属于多个部门 dept表 ID name select dept.name,count(user.id) as '人数' from user left join dept on user.deptids=dept.id group by dept.name
或者select a.deptid,min(a.deptname) as deptname , count( distinct b.useid) as nameCount from @dept a inner join @User b on PATINDEX( '%'+a.deptid+',%',b.deptids+',')<>0 group by a.deptid
User表我给你改了个表名UserTable SELECT dept.name,CountValue=(SELECT COUNT(*) FROM UserTable WHERE UserTable.deptids LIKE '%'+Convert(varchar,dept.ID)+'%') FROM dept
select u.count(*),d.name from User as u join dept as d on User.deptids=dept.ID group by d.name
你们写user.deptids=dept.id 的人都是错的
SELELCT B.name,A.countname from ( selelct deptids, count(name ) as countname from user group by deptids )A LEFT JOIN Dept B on A.deptids=B.id
create table [User] ( id int, [name] varchar(10), deptid varchar(100) )create table dept ( id int, [name] varchar(10) )insert into [user] select 1,'张七','2,3,' union select 2,'李四','1,3,' union select 3,'随便','1,' union select 4,' 随便1','1,2, 'insert into dept select 1,'行政' union select 2,'生产部 ' union select 2,'XX部 'set ANSI_NULLS ON set QUOTED_IDENTIFIER ON gocreate function [dbo].[f_split](@c varchar(2000),@split varchar(2)) returns @t table(col varchar(20)) as begin
while(charindex(@split,@c)<>0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end insert @t(col) values (@c) return end select count([user].id),dept.[name] from [user] inner join dept on dept.id in (select * from [dbo].f_split(substring([user].deptid,1,len([user].deptid)-1),',')) group by dept.[name]
select name,count(*) from User group by deptids
select count(*) AS COUNTNUM from USER where charindex(',+@dep+,',',+'1'+,')>0 AS 部门1人数 select count(*) AS COUNTNUM from USER where charindex(',+@dep+,',',+'2'+,')>0 AS 部门2人数 select count(*) AS COUNTNUM from USER where charindex(',+@dep+,',',+'3'+,')>0 AS 部门3人数
SELECT 表1.Name,人数 = (SELECT COUNT(*) FROM Users WHERE "表1.DeptID Like '" + 表2.DeptID.ToString() + ",%' or 表1.DeptID Like '%," + 表2.DeptID.ToString() + ",%'")FROM dept where deptID = 表2.DeptID.ToString();
SELECT dept.Name,人数= (SELECT COUNT(*) FROM Users WHERE Users.DeptID LIKE '1,%' or Users.DeptID LIKE '%,1,%' ) FROM dept where deptID=1
group by d.name
SELECT dept.name,CountValue=(SELECT COUNT(*) FROM User WHERE User.depdids LIKE '%dept.ID%') FROM dept
1 行政 1 张七 2,3,
2 生产部 2 李四 1,3,
3 XX部 3 随便 1,
4 随便1 1,2,这是测试数据,我给出、了
create table [User]
(
id int,
[name] varchar(10),
deptid int
)create table dept
(
id int,
[name] varchar(10)
)
insert into [user] select 1,'user1',1
union select 2,'user2',1
union select 3,'user3',1
union select 4,'user4',2
union select 5,'user5',2insert into dept select 1,'dept1'
union select 2,'dept2'select count([user].id),dept.[name]
from [User]
inner join dept on [user].deptid = dept.id
group by dept.id,dept.[name]
declare @User table( useid int , userName varchar(20) , deptids varchar(50))
declare @dept table( deptid varchar(20) , deptname varchar(20))insert into @User values( 1 , 'name1' , '1,2,3')
insert into @User values( 2 , 'name1' , '1')insert into @dept values( 1 , 'dept1')
insert into @dept values( 2 , 'dept2')
insert into @dept values( 3 , 'dept3')select *,
(select Count( distinct useid) from @User where PATINDEX( '%'+deptid+',%',deptids+',')<>0) as useCount
from @dept
select dept.name,count(user.id) as '人数' from user left join dept on user.deptids=dept.id group by dept.name
inner join @User b on PATINDEX( '%'+a.deptid+',%',b.deptids+',')<>0
group by a.deptid
SELECT dept.name,CountValue=(SELECT COUNT(*) FROM UserTable WHERE UserTable.deptids LIKE '%'+Convert(varchar,dept.ID)+'%') FROM dept
from User as u
join dept as d on User.deptids=dept.ID
group by d.name
(
selelct deptids, count(name ) as countname from user group by deptids
)A
LEFT JOIN Dept B on A.deptids=B.id
(
id int,
[name] varchar(10),
deptid varchar(100)
)create table dept
(
id int,
[name] varchar(10)
)insert into [user] select 1,'张七','2,3,'
union select 2,'李四','1,3,'
union select 3,'随便','1,'
union select 4,' 随便1','1,2, 'insert into dept select 1,'行政'
union select 2,'生产部 '
union select 2,'XX部 'set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
gocreate function [dbo].[f_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end select count([user].id),dept.[name]
from [user]
inner join dept on dept.id in
(select * from [dbo].f_split(substring([user].deptid,1,len([user].deptid)-1),','))
group by dept.[name]
select count(*) AS COUNTNUM from USER where charindex(',+@dep+,',',+'2'+,')>0 AS 部门2人数
select count(*) AS COUNTNUM from USER where charindex(',+@dep+,',',+'3'+,')>0 AS 部门3人数