sql代码怎么个写法能实现以下内容？

Question

数据库表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         绿区
即为：违章人总分合计超过10分时安全度进入红区，小于10分时进入绿区，专业所在安全度的意思是当专业红区人数超过绿区人数20%时，专业进入红区。
这段代码我只写了人数汇总，专业所在安全度不会写，请高手帮我下！
"SELECT 专业,COUNT(*) AS 在册人数,SUM(CASE WHEN 计分>=10 THEN 1 ELSE 0 END) AS 红色区域人数,SUM(CASE WHEN 0<=计分 AND 计分<=10 THEN 1 ELSE 0 END) AS 绿色区域人数, FROM WZrbb where 时间 >= '" & Me.graqdDateTimePicker1.Value.Date & "' and 时间 <= '" & Me.graqdDateTimePicker2.Value.Date & "' GROUP BY 专业 后面专业所在安全度应该怎么写？

Answer 1

SELECT 专业,COUNT(*) AS 在册人数,
SUM(CASE WHEN 计分>=10 THEN 1 ELSE 0 END) AS 红色区域人数,
SUM(CASE WHEN 0 <=计分 AND 计分 <=10 THEN 1 ELSE 0 END) AS 绿色区域人数,
max(case when 1.0 *SUM(CASE WHEN 计分>=10 THEN 1 ELSE 0 END)/
SUM(CASE WHEN 0 <=计分 AND 计分 <=10 THEN 1 ELSE 0 END) >0.2 then '红区' else '绿区') as 专业所在安全度
FROM   WZrbb   
where   时间   > =   '"   &   Me.graqdDateTimePicker1.Value.Date   &   "'   and   时间   <=   '"   &   Me.graqdDateTimePicker2.Value.Date   &   "'   GROUP   BY   专业 

Answer 2

"SELECT 专业,
COUNT(*) 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 橙色区域人数,
max(case when 1.0*SUM(CASE WHEN 计分>=10 THEN 1 ELSE 0 END)/SUM(CASE WHEN 0<=计分 AND 计分<=10 THEN 1 ELSE 0 END)>0.2 then 1 else 0 end) as 专业安全度
FROM WZrbb where 时间 >= '" & Me.graqdDateTimePicker1.Value.Date & "' and 时间 <= '" & Me.graqdDateTimePicker2.Value.Date & "' GROUP BY 专业"
为什么提示    “不能对包含聚合或子查询的表达式执行聚合函数。”

Answer 3

sorry ,try:
"SELECT   专业, 
COUNT(*)   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   计分> =10   THEN   1   ELSE   0   END)/SUM(CASE   WHEN   0 <=计分   AND   计分 <=10   THEN   1   ELSE   0   END)> 0.2   then   1   else   0   end)   as   专业安全度 
FROM   WZrbb   where   时间   > =   '"   &   Me.graqdDateTimePicker1.Value.Date   &   "'   and   时间   <=   '"   &   Me.graqdDateTimePicker2.Value.Date   &   "'   GROUP   BY   专业" 

Answer 4

可以了，但还一个问题，刚才我只是问了你一个红色和绿色，我想如果是如果两项的比值>0.2且<0.3为蓝区，>0.3且<0.5为黄区，>0.5才为红区的话，怎么改？答完了给你加分，谢谢你了！

Answer 5

我想如果是如果两项的比值> 0.2且 <0.3为蓝区，> 0.3且 <0.5为黄区，> 0.5才为红区的话，
把上面的语句加个case when 判断.

Answer 6

合计加group by with rollup

Answer 7

加合计：
"SELECT 专业,   
COUNT(*) 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 计分>   =10 THEN 1 ELSE 0 END)/
SUM(CASE WHEN 0   <=计分 AND 计分   <=10 THEN 1  ELSE 0 END)>   0.2 then 1 else 0 end) as 专业安全度   
FROM WZrbb where 时间 >   = '" & Me.graqdDateTimePicker1.Value.Date & "' and 时间 <= '" & Me.graqdDateTimePicker2.Value.Date & "' GROUP BY 专业 with rollup"   

Answer 8

不好意思我是初学者，再请教下，case when 加到哪个地方？7楼说的那个。

Answer 9

"SELECT 专业,   
COUNT(*) 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 计分>   =10 THEN 1 ELSE 0 END)/
SUM(CASE WHEN 0   <=计分 AND 计分   <=10 THEN 1  ELSE 0 END) between 0.2 and 0.3 then '蓝区' 
      when 1.0*SUM(CASE WHEN 计分>   =10 THEN 1 ELSE 0 END)/
SUM(CASE WHEN 0   <=计分 AND 计分   <=10 THEN 1  ELSE 0 END) between 0.4 and 0.5 then '黄区'
      when 1.0*SUM(CASE WHEN 计分>   =10 THEN 1 ELSE 0 END)/
SUM(CASE WHEN 0   <=计分 AND 计分   <=10 THEN 1  ELSE 0 END) > 0.5 then '红区'
else 'unknown' end) as 专业安全度   
FROM WZrbb where 时间 >   = '" & Me.graqdDateTimePicker1.Value.Date & "' and 时间 <= '" & Me.graqdDateTimePicker2.Value.Date & "' GROUP BY 专业"   

Answer 10

完整的"SELECT 专业,   
COUNT(*) 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 计分>   =10 THEN 1 ELSE 0 END)/
SUM(CASE WHEN 0   <=计分 AND 计分   <=10 THEN 1  ELSE 0 END) between 0.2 and 0.3 then '蓝区' 
      when 1.0*SUM(CASE WHEN 计分>   =10 THEN 1 ELSE 0 END)/
SUM(CASE WHEN 0   <=计分 AND 计分   <=10 THEN 1  ELSE 0 END) between 0.4 and 0.5 then '黄区'
      when 1.0*SUM(CASE WHEN 计分>   =10 THEN 1 ELSE 0 END)/
SUM(CASE WHEN 0   <=计分 AND 计分   <=10 THEN 1  ELSE 0 END) > 0.5 then '红区'
else 'unknown' end) as 专业安全度   
FROM WZrbb where 时间 >   = '" & Me.graqdDateTimePicker1.Value.Date & "' 
and 时间 <= '" & Me.graqdDateTimePicker2.Value.Date & "' GROUP BY 专业  with rollup"   

Answer 11

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 , '掘进')
goselect * , 专业所在安全度 = case when 红区人数*1.0 / 绿区人数 > 0.2 then '红区' else '绿区' 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
) mdrop table tb/*
专业         在册人数        绿区人数        红区人数        专业所在安全度 
---------- ----------- ----------- ----------- ------- 
采煤         1           1           0           绿区
掘进         4           1           3           红区
合计         5           2           3           红区（所影响的行数为 3 行）
*/

Answer 12

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 , '掘进')
goselect * , 专业所在安全度 = 
           case 
             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
) mdrop table tb/*
专业         在册人数      绿区人数      红区人数     专业所在安全度 
---------- ----------- ----------- ----------- ------- 
采煤         1           1           0           绿区
掘进         4           1           3           红区
合计         5           2           3           红区（所影响的行数为 3 行）
*/

Answer 13

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 , '掘进')
goselect * , 专业所在安全度 = 
           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
) mdrop table tb/*
专业         在册人数      绿区人数      红区人数     专业所在安全度 
---------- ----------- ----------- ----------- ------- 
采煤         1           1           0           绿区
掘进         4           0           4           红区
合计         5           1           4           红区（所影响的行数为 3 行）
*/

Answer 14

龟，BS
这点分还抢
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 , '掘进')
goselect 专业,在册人数, 绿色区域人数,蓝色区域人数, 黄色区域人数, 橙色区域人数,
case when 1.0 * a1 /a2 between 0.2 and 0.3 then '蓝区'
when 1.0 * a1 /a2 between 0.3 and 0.5 then '黄区'
when 1.0 * a1 /a2 > 0.5  then '红区'
else 'unknown' end as 专业安全度
from 
(
SELECT 专业,   
COUNT(*) 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 橙色区域人数,
SUM(CASE WHEN 计分>   =10 THEN 1 ELSE 0 END) as a1,
SUM(CASE WHEN 0   <=计分 AND 计分   <=10 THEN 1  ELSE 0 END) as a2
FROM tb  group by 专业 with rollup  
) aa
/*
专业         在册人数        绿色区域人数      蓝色区域人数      黄色区域人数      橙色区域人数      专业安全度   
---------- ----------- ----------- ----------- ----------- ----------- ------- 
采煤         1           1           0           0           0           unknown
掘进         5           5           0           0           0           红区
NULL       6           6           0           0           0           红区（所影响的行数为 3 行）
*/
drop table tb

Answer 15

我用的SQL2000+VB.NET，代码如下：
myad = New SqlClient.SqlDataAdapter("select * from wzrbb where 1=2", myconn)
        myad.Fill(myset, "wzrbb")
        myset.Tables("wzrbb").Rows.Clear()
        myset.Tables("wzrbb").Columns.Clear()
        Dim mycol As New DataColumn
        Dim myint As Int16
        mycol.DataType = myint.GetType
        mycol.ColumnName = "序号"
        myset.Tables("wzrbb").Columns.Add(mycol)
        mystr = "SELECT 专业,COUNT(*) 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 计分>   =10 THEN 1 ELSE 0 END)/SUM(CASE WHEN 0   <=计分 AND 计分   <=10 THEN 1  ELSE 0 END) between 0.2 and 0.3 then '蓝区' when 1.0*SUM(CASE WHEN 计分>   =10 THEN 1 ELSE 0 END)/SUM(CASE WHEN 0<=计分 AND 计分<=10 THEN 1 ELSE 0 END) between 0.4 and 0.5 then '黄区' when 1.0*SUM(CASE WHEN 计分>=10 THEN 1 ELSE 0 END)/SUM(CASE WHEN 0<=计分 AND 计分<=10 THEN 1 ELSE 0 END)> 0.5 then '红区' else 'unknown' end) as 专业安全度 FROM WZrbb where 时间 >= '" & Me.graqdDateTimePicker1.Value.Date & "' and 时间 <= '" & Me.graqdDateTimePicker2.Value.Date & "' GROUP BY 专业 with rollup"
        myad = New SqlClient.SqlDataAdapter(mystr, myconn)
        myad.Fill(myset, "wzrbb")
        Me.DataGridView1.DataSource = myset.Tables("wzrbb")不知道用这段代码应该怎么个加法，把最后一行加个合计两个字   安全度按那个公式。