统计一个各部门有多少人的SQL语句，解决马上给分？

[Lakers_24号程序员]

User表 ID name deptids /////一个人可属于多个部门

dept表 ID name

写出统计各部门的人数的SQL语句！！

[cyrix_wxm]

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',2

insert 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]

[zzxap]

select b.name,count(a.name) as 人数

from user a left join dept b on a.deptids=b.id group by b.name

[mbh0210]

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

[randomfeel]

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

[mbh0210]

或者

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

[robin521]

[Quote=引用 14 楼 mbh0210 的回复:]
或者
SQL codeselect a.deptid,min(a.deptname)as deptname ,count(distinct b.useid)as nameCountfrom@dept ainnerjoin@User bonPATINDEX('%'+a.deptid+',%',b.deptids+',')<>0groupby a.deptid
[/Quote]
我喜欢这个,瓷实!

[mbh0210]

我写的两个随便一个都可以

[testtestett]

User表我给你改了个表名UserTable
SELECT dept.name,CountValue=(SELECT COUNT(*) FROM UserTable WHERE UserTable.deptids LIKE '%'+Convert(varchar,dept.ID)+'%') FROM dept

[chen_ya_ping]

select u.count(*),d.name
from User as u
join dept as d on User.deptids=dept.ID
group by d.name

[Lakers_24号程序员]

呵呵，。你们太有才了，统计方法这么多

[mbh0210]

很多都是错的，楼主注意检验

[Lakers_24号程序员]

你们写user.deptids=dept.id 的人都是错的

[Lakers_24号程序员]

我知道错的很多，6楼有点小错没转换类型，结帐

[zzxap]

[code=SQL]

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

[/CODE]

[zzxap]

靠。。。没看清楚 这样的id

[cyrix_wxm]

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
go

create 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

[zzxap]

[code=SQL]

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人数


[/CODE]

[xwyxwyujj]

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();

[xwyxwyujj]

SELECT dept.Name,人数= (SELECT COUNT(*) FROM Users WHERE Users.DeptID LIKE '1,%' or Users.DeptID LIKE '%,1,%' ) FROM dept where deptID=1