主页
基础类
应用实例
新技术前沿

汇总问题！

wzq855508 2007-12-17 09:37:30

数据库表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人，也就是说有一个重复数据也算进去了，我实际想实现可以查人数，区域人数的数量的和应该等于在册人数，且‘计分’应该是同名的人在时间段内计分的合计。请教这段代码应该怎么改？

...全文

18 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

-狙击手- 2007-12-17

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

－－－
别给丫分，

回复赞

wzq855508 2007-12-17

还有一个问题，其实我是要查询两个表
表一名为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分应反映在绿区之中。首先谢谢你潇洒老乌龟，一会我单独开个贴给你分值。

回复赞

wzq855508 2007-12-17

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附近有语法错误

回复赞

dawugui 2007-12-17

只要这段:

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 2007-12-17

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 附近有语法错误，不知道哪里错了，我初学，请教！

回复赞

wzq855508 2007-12-17

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

回复赞

dawugui 2007-12-17

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

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

回复赞

wzq855508 2007-12-17

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

回复赞

dawugui 2007-12-17



--这是我上次就已经写好的,你没仔细看.

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 2007-12-17

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

回复赞

wzq855508 2007-12-17

没有的
等下我找那个贴子

回复赞

dawugui 2007-12-17

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

回复赞

dawugui 2007-12-17

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

回复赞

-狙击手- 2007-12-17

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

回复赞

-狙击手- 2007-12-17

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

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

回复赞

-狙击手- 2007-12-17

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

这个吗？？？

回复赞

wzq855508 2007-12-17

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

回复赞

wzq855508 2007-12-17

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

回复赞

发动态

发帖子

MS-SQL Server

创建于2007-09-28

加入

3.2w+

社区成员

25.4w+

社区内容

MS-SQL Server相关内容讨论专区

申请成为版主

社区公告

暂无公告