汇总问题！

[wzq855508]

数据库表WZRBB如下：
ID 违章人 所在工区 违章内容 计分 专业
1 张三 掘一 123 10 掘进
2 李四 掘二 321 11 掘进
3 王五 掘一 222 9 掘进
4 周六 掘一 133 19 掘进
5 孙七 采一 1221 9 采煤
6 王五 掘一 123 2 掘进
7
.。。。
如何用SQL实现以下查询？
序号 专业 在册人数 绿区人数 红区人数 专业所在安全度
1 掘进 4 0 4 红区
2 采煤 1 1 0 绿区

"SELECT 专业,
所在区队,
COUNT(DISTINCT 违章人) AS 在册人数,SUM(CASE WHEN 计分<=59 THEN 1 ELSE 0 END) AS 绿色区域人数,
SUM(CASE WHEN 60<计分 AND 计分<=79 THEN 1 ELSE 0 END) AS 蓝色区域人数,
SUM(CASE WHEN 80<=计分 AND 计分<99 THEN 1 ELSE 0 END) AS 黄色区域人数,
SUM(CASE WHEN 计分>=100 THEN 1 ELSE 0 END) AS 橙色区域人数,
(case when 1.0*SUM(CASE WHEN 计分>=100 THEN 1 ELSE 0 END)/SUM(CASE WHEN 0<=计分 AND 计分<=59 THEN 1 ELSE 0 END) <0.2 then '绿区'
when 1.0*SUM(CASE WHEN 计分>=100 THEN 1 ELSE 0 END)/SUM(CASE WHEN 0<=计分 AND 计分<=59 THEN 1 ELSE 0 END) between 0.4 and 0.5 then '黄区'
when 1.0*SUM(CASE WHEN 计分>=100 THEN 1 ELSE 0 END)/SUM(CASE WHEN 0<=计分 AND 计分<=59 THEN 1 ELSE 0 END)> 0.5 then '橙区' else 'unkown' end) as 区队安全度
FROM WZrbb where 时间 >= '" & Me.graqdDateTimePicker1.Value.Date & "' and 时间 <= '" & Me.graqdDateTimePicker2.Value.Date & "' GROUP BY 所在区队,专业 order by 专业,所在区队 desc”
SUM语句中相加的是表里的明细即看上表就可以明白，如果用上面代码运行的话查出来的掘进专业中红区人数为5人，实际应该为4人，也就是说有一个重复数据也算进去了，我实际想实现可以查人数，区域人数的数量的和应该等于在册人数，且‘计分’应该是同名的人在时间段内计分的合计。请教这段代码应该怎么改？

[wzq855508]

hehe，无枪狙击手，你也帮我下啊，代码怎么个写法啊，真不明白了。

[wzq855508]

不知道你们高手可以留下个QQ吗？这样太不方便了。

[-狙击手-]

create table wzrbb(ID int, 违章人 varchar(10), 所在工区 varchar(10), 违章内容 int ,计分 int, 专业 varchar(10))
insert into wzrbb values(1, '张三', '掘一', 123 , 10 , '掘进') 
insert into wzrbb values(2, '李四', '掘二', 321 , 11 , '掘进') 
insert into wzrbb values(3, '王五', '掘一', 222 , 9 , '掘进') 
insert into wzrbb values(4, '周六', '掘一', 133 , 19 , '掘进') 
insert into wzrbb values(5, '孙七', '采一', 1221, 9 , '采煤') 
insert into wzrbb values(6, '王五', '掘一', 123 , 2 , '掘进')
go



select * ,
专业所在安全度 = 
           case 
             when 绿区人数 = 0 then '红区' 
             when 红区人数*1.0 / 绿区人数 < 0.2 then '绿区' 
             when 红区人数*1.0 / 绿区人数 between 0.2 and 0.3 then '蓝区' 
             when 红区人数*1.0 / 绿区人数 between 0.3 and 0.5 then '黄区' 
             when 红区人数*1.0 / 绿区人数 >0.5 then '红区' 
           end 

from (
select isnull(t.专业,'合计') 专业 , 在册人数 = count(1), 
       绿区人数 = sum(case when 计分 < 10 then 1 else 0 end), 
       红区人数 = sum(case when 计分 >= 10 then 1 else 0 end)
  from
  (
    select 专业 , 违章人 ,计分 = sum(计分) from wzrbb group by 专业 , 违章人
  

  ) t

group by t.专业 with rollup

 ) ss

/*

专业         在册人数        绿区人数        红区人数        专业所在安全度 
---------- ----------- ----------- ----------- ------- 
采煤         1           1           0           绿区
掘进         4           0           4           红区
合计         5           1           4           红区

（所影响的行数为 3 行）

*/
drop table wzrbb

这个吗？？？

[-狙击手-]

查询出如下结果
序号 专业 在册人数 绿区人数 红区人数 专业所在安全度
1 掘进 5 3 2 红区
2 采煤 0 0 0 绿区


你后来给的数据中无 采煤这个专业呀

[-狙击手-]

set nocount on
create table AZYZL1(
ID int,安质员姓名 varchar(10),所在工区 varchar(4),区队长 varchar(10),班组长 varchar(10),
工作地点 varchar(16),违章人 varchar(10),专业 varchar(10))
insert AZYZL1 select 1,'吴佃秋','掘一','李明','张军','6196运输巷','张三','掘进' 
insert AZYZL1 select 2,'吴佃秋','掘一','李明','张军','6196运输巷','李四','掘进' 
insert AZYZL1 select 3,'吴佃秋','掘一','李明','张军','6196运输巷','王二','掘进' 
insert AZYZL1 select 4,'吴佃秋','掘一','李明','张军','6196运输巷','武大','掘进' 
insert AZYZL1 select 5,'吴佃秋','掘一','李明','张军','6196运输巷','吴五','掘进' 



create table wzrbb(ID int, 违章人 varchar(10), 所在工区 varchar(10), 违章内容 int ,计分 int, 专业 varchar(10))
insert into wzrbb values(1, '吴五', '掘一', 123 , 10 , '掘进') 
insert into wzrbb values(2, '王二', '掘二', 321 , 11 , '掘进') 
insert into wzrbb values(3, '吴五', '掘一', 222 , 9 , '掘进') 
insert into wzrbb values(4, '张三', '掘一', 133 , 19 , '采煤') 

go

select isnull(专业,'合计') 专业,在册人数 =max(在册人数) 
from (
	select a.专业,在册人数 =( select count(1) as count from AZYZL1 where 专业 = a.专业  )
	from wzrbb a
	left join AZYZL1 b on a.专业 = b.专业 and a.违章人 = b.违章人
	group by a.专业,b.专业) aa
group by aa.专业 with rollup



/*

专业         在册人数        
---------- ----------- 
采煤         0
掘进         5
合计         5


*/
drop table wzrbb,AZYZL1

[dawugui]

你把前次的那个地址帖出来.语句都在那里.

[dawugui]

好象我上次排除你这个重复人数的计算的.

[wzq855508]

没有的
等下我找那个贴子

[wzq855508]

http://topic.csdn.net/u/20071213/10/d6d61c78-cdc5-46f2-b6df-d1fdbfe0d643.html

[dawugui]

--这是我上次就已经写好的,你没仔细看.
create table tb(ID int, 违章人 varchar(10), 所在工区 varchar(10), 违章内容 int , 计分 int, 专业 varchar(10))
insert into tb values(1, '张三', '掘一', 123 , 10 , '掘进') 
insert into tb values(2, '李四', '掘二', 321 , 11 , '掘进') 
insert into tb values(3, '王五', '掘一', 222 , 9 , '掘进') 
insert into tb values(4, '周六', '掘一', 133 , 19 , '掘进') 
insert into tb values(5, '孙七', '采一', 1221, 9 , '采煤') 
insert into tb values(6, '王五', '掘一', 123 , 2 , '掘进')
go

select * , 专业所在安全度 = 
           case 
             when 绿区人数 = 0 then '红区' 
             when 红区人数*1.0 / 绿区人数 < 0.2 then '绿区' 
             when 红区人数*1.0 / 绿区人数 between 0.2 and 0.3 then '蓝区' 
             when 红区人数*1.0 / 绿区人数 between 0.3 and 0.5 then '黄区' 
             when 红区人数*1.0 / 绿区人数 >0.5 then '红区' 
           end 
from
(
  select isnull(专业,'合计') 专业 , 在册人数 = count(*) , 
       绿区人数 = sum(case when 计分 < 10 then 1 else 0 end), 
       红区人数 = sum(case when 计分 >= 10 then 1 else 0 end)
  from
  (
    select 专业 , 违章人 , 计分 = sum(计分) from tb group by 专业 , 违章人
  ) t
  group by 专业 with rollup
) m

drop table tb

/*
专业         在册人数      绿区人数      红区人数     专业所在安全度 
---------- ----------- ----------- ----------- ------- 
采煤         1           1           0           绿区
掘进         4           0           4           红区
合计         5           1           4           红区

（所影响的行数为 3 行）
*/

[wzq855508]

哦，我是个初学者，这段代码怎么个写法，我是用VB.NET写的一个小程序，只需用一个查询语句，你这段这么复杂，我真不太明白，老师能说的细一下吗？

[dawugui]

你中间那段拷贝过去,放进你net中的一个串中,然后动态执行即可.

至于net中如何搞,我就只有帮顶了.

[wzq855508]

为什么我将你这段代码复制到查询中，怎么提示GO附近有语法错误？

[wzq855508]

select * , 专业所在安全度 =
case
when 绿区人数 = 0 then '红区'
when 红区人数*1.0 / 绿区人数 < 0.2 then '绿区'
when 红区人数*1.0 / 绿区人数 between 0.2 and 0.3 then '蓝区'
when 红区人数*1.0 / 绿区人数 between 0.3 and 0.5 then '黄区'
when 红区人数*1.0 / 绿区人数 >0.5 then '红区'
end
from
(
select isnull(专业,'合计') 专业 , 在册人数 = count(*) ,
绿区人数 = sum(case when 计分 < 10 then 1 else 0 end),
红区人数 = sum(case when 计分 >= 10 then 1 else 0 end)
from
(
select 专业 , 违章人 , 计分 = sum(计分) from wzrbb group by 专业 , 违章人
) t
group by 专业 with rollup
) m

drop table wzrbb
是不是这一段？我请教t,m 是什么意思，如果复制这一段的话提示GROUP BY 附近有语法错误，不知道哪里错了，我初学，请教！

[dawugui]

只要这段:
select * , 专业所在安全度 = 
           case 
             when 绿区人数 = 0 then '红区' 
             when 红区人数*1.0 / 绿区人数 < 0.2 then '绿区' 
             when 红区人数*1.0 / 绿区人数 between 0.2 and 0.3 then '蓝区' 
             when 红区人数*1.0 / 绿区人数 between 0.3 and 0.5 then '黄区' 
             when 红区人数*1.0 / 绿区人数 >0.5 then '红区' 
           end 
from
(
  select isnull(专业,'合计') 专业 , 在册人数 = count(*) , 
       绿区人数 = sum(case when 计分 < 10 then 1 else 0 end), 
       红区人数 = sum(case when 计分 >= 10 then 1 else 0 end)
  from
  (
    select 专业 , 违章人 , 计分 = sum(计分) from tb group by 专业 , 违章人
  ) t
  group by 专业 with rollup
) m

[wzq855508]

SELECT *,
专业所在安全度 = CASE WHEN 绿区人数 = 0 THEN '红区' WHEN 红区人数 * 1.0 / 绿区人数
< 0.2 THEN '绿区' WHEN 红区人数 * 1.0 / 绿区人数 BETWEEN 0.2 AND
0.3 THEN '蓝区' WHEN 红区人数 * 1.0 / 绿区人数 BETWEEN 0.3 AND
0.5 THEN '黄区' WHEN 红区人数 * 1.0 / 绿区人数 > 0.5 THEN '红区' END
FROM (SELECT isnull(专业, '合计') 专业, 在册人数 = COUNT(*),
绿区人数 = SUM(CASE WHEN 计分 < 10 THEN 1 ELSE 0 END),
红区人数 = SUM(CASE WHEN 计分 >= 10 THEN 1 ELSE 0 END)
FROM (SELECT 专业, 违章人, 计分 = SUM(计分)
FROM wzrbb
GROUP BY 专业, 违章人) t
GROUP BY 专业 WITH rollup) m
真晕了，提示WITHROLLUP附近有语法错误

[wzq855508]

还有一个问题，其实我是要查询两个表
表一名为AZYZL1
ID 安质员姓名 所在工区 区队长 班组长 工作地点 违章人 专业
1 吴佃秋 掘一 李明 张军 6196运输巷 张三 掘进
2 吴佃秋 掘一 李明 张军 6196运输巷 李四 掘进
3 吴佃秋 掘一 李明 张军 6196运输巷 王二 掘进
4 吴佃秋 掘一 李明 张军 6196运输巷 武大 掘进
5 吴佃秋 掘一 李明 张军 6196运输巷 吴五 掘进
表二名为wzrbb
id 安质员姓名 所在工区 时间 班次 违章内容 考核人 违章人 计分 专业 区队长
1 吴佃秋 掘一 2007 早 123 123 吴五 1 掘进 李明
2 吴佃秋 掘一 2007 早 123 123 王二 15 掘进 李明
3 吴佃秋 掘一 2007 早 123 121 吴五 9 掘进 李明
4 吴佃秋 掘一 2007 早 123 121 张三 8 掘进 李明

查询出如下结果
序号 专业 在册人数 绿区人数 红区人数 专业所在安全度
1 掘进 5 3 2 红区
2 采煤 0 0 0 绿区

要求最上的。
现在一个问题就是我想按AZYZL1中的违章人来定在册人数，然后WZRBB中如果没有AZYZL1中违章人的话，其违章人默认为0分。即上表中李四和武大为0分应反映在绿区之中。首先谢谢你潇洒老乌龟，一会我单独开个贴给你分值。

[-狙击手-]

首先谢谢你潇洒老乌龟，一会我单独开个贴给你分值。

－－－
别给丫分，