求一条SQL语句,用于算计人数

Question

我要计算出行政部、门警队及生产部的人数。当然，这只是一个例子，部门有很多的，而且一个部门最多可分4级。谢谢！
departmentcode departemntname qty
00001 行政部
00001000001 财务部 10
00001000002 电脑部 20
00001000003 门警队
0000100000300001 门警一班 5
0000100000300002 门警二班 5
0000100000300003 门警三班 5
00002 生产部
00002000001 1号车间 50
00002000002 2号车间 50
即是:行政部(一级部门):30人,门警队(二级部门):15人,生产部(一级部门):100人

Answer 1

select count(qty)
from 表
group by departemntname.

count 是用来专门计算总数的，你查的就是各部门人数，最后你用部门分下组，就可以查询出来各个组对应的人数了。

Answer 2

不能确定您的意思，只能猜测两种情况：
1．您要求出指定部门的人数汇总，这样
SELECT a.departmentcode,a.departemntname,
qty=(select sum(b.qty) from tablename b where b.departmentcode like a.departmentcode+'%')
FROM TABLENAME a WHERE a.departemntname in(行政部,门警队,生产部)
2.求所有未给出人数的（即非最下层部门的人数汇总）
SELECT a.departmentcode,a.departemntname,
qty=(select sum(b.qty) from tablename b where b.departmentcode like a.departmentcode+'%')
FROM TABLENAME a WHERE a.qty=0本回答被提问者采纳

Answer 3

这样即可：
select left(departmentcode,5) as dept,sum(qty)
from [tablename] group by left(departmentcode,5)
union
select left(departmentcode,11) as dept,sum(qty)
from [tablename] where len(departmentcode)>=11 group by left(departmentcode,11)
union
……

如果要加上部门名称，则可以这样：
select a.dept, b.departemntname, a.num from
(select left(departmentcode,5) as dept,sum(qty)
from [tablename] group by left(departmentcode,5)
union
select left(departmentcode,11) as dept,sum(qty)
from [tablename] where len(departmentcode)>=11 group by left(departmentcode,11)
union
……
) a join [tablename] b on a.dept=b.departmentcode

Answer 4

DECLARE @tempcode varchar(20), @tempname varchar(20), @num int, @i int, @tempsum int

SELECT departmentcode,departmentname INTO #temp FROM table1
WHERE qty IS NULL
SELECT departmentname,qty INTO #ans FROM table1 WHERE qty <0
SET @num = (SELECT count(*) FROM table1 WHERE qty IS NULL)
SET @i = 0

WHILE @i < @num
BEGIN
SELECT TOP 1 @tempcode = departmentcode, @tempname = departmentname FROM #temp
SET @tempsum = (SELECT SUM(qty) Table1
WHERE (departmentcode LIKE @tempcode + '%'))
DELETE #temp WHERE departmentcode = @tempcode
INSERT INTO #ans VALUES (@tempcode,@tempname ,@qty )
SET @i = @i + 1
END

SELECT * FROM #ans

sql2005下测试正确

Answer 5

select mytest.departmentname,summary from (
select left(departmentcode,5) departmentcode ,sum(qty)summary
from mytest group by left(departmentcode,5) ) a inner join mytest on a.departmentcode=mytest.departmentcode

把你的表名替换 mytest即可

经过测试的。