请高手看看一下如何用SQL语句实现以下查询

happybuttom 2010-09-08 09:35:31
问题描述：实现统计新生报到率统计。
表结构：
KSH 字符 考生号，
XM 字符 姓名,
SF 字符 省份,
STATE 整形 状态（说明“1”和“0”，“1”表示已经报到，“0”表示未报到。）
ZY 字符 专业。

以下为样表：(student)

KSH XM SF STATE ZY
101 王一 江西 0 化学
102 李二 江西 1 物理学
103 刘三 北京 1 化学
104 张四 北京 1 物理学
105 王二 江西 0 化学
106 李三 江西 0 物理学
107 刘四 北京 1 化学
108 张五 北京 1 物理学

应用行列转换可以实现分省分专业人数统计表

语句如下：
1、静态SQL

Select zy 专业,sum(case sf when '北京' then 1 else 0 end)北京,sum(case sf when '江西' then 1 else 0 end)江西 from student group by zy with rollup

结果如下：

专业 北京 江西
1 化学 2 2
2 物理学 2 2
4 4


2、动态SQL

declare @sql varchar(8000)
set @sql = 'select zy as 专业 '
select @sql = @sql + ' , sum(case sf when ''' + sf + ''' then 1 else 0 end) [' + sf + ']'
from (select distinct sf from student) as a
set @sql = @sql + ' from student group by zy with rollup'
exec(@sql)

如果同上。

现我要提出的问题是，根据STATE 的值，（“1”表示已经报到，“0”表示未报到）实现统计报到人数：即得出如下结果

专业 北京人数 北京报到数 江西 江西报到数
1 化学 2 2 2 0
2 物理学 2 2 2 1
3 4 4 4 1

请高手写出静态和动态SQL语句。（如果能同时写出算报到率更好）

及结果如下：

专业 北京人数 北京报到数 北京报到率 江西 江西报到数 江西报到率
1 化学 2 2 100% 2 0 0%
2 物理学 2 2 100% 2 1 50%
3 4 4 100% 4 1 25%
...全文
127 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
happybuttom 2010-09-08
  • 打赏
  • 举报
 回复
实在是佩服，我开始自己试了，不行？？/

我试了以下语句
if object_id('[abcd]') is not null drop table [abcd]

go

create table [abcd] (KSH int,XM varchar(4),SF varchar(4),STATE int,ZY varchar(6))

insert into [abcd]

select 101,'王一','江西',0,'化学' union all

select 102,'李二','江西',1,'物理学' union all

select 103,'刘三','北京',1,'化学' union all

select 104,'张四','北京',1,'物理学' union all

select 105,'王二','江西',0,'化学' union all

select 106,'李三','江西',0,'物理学' union all

select 107,'刘四','北京',1,'化学' union all

select 108,'张五','北京',1,'物理学'



--1

select 专业=ZY,

     北京人数=sum(case when SF='北京' then 1 else 0 end),

     北京报到数=sum((case when SF='北京' then 1 else 0 end)&(case when state='1' then 1 else 0 end)),

     江西=sum(case when SF='江西' then 1 else 0 end),

     江西报到数=sum((case when SF='江西' then 1 else 0 end)&(case when state='1' then 1 else 0 end))

from [abcd]

group by ZY

with rollup

化学 2 2 2 0
物理学 2 2 2 1
NULL 4 4 4 1
duanzhi1984 2010-09-08
  • 打赏
  • 举报
 回复 
create table tp(KSH varchar(10), XM  varchar(20),SF varchar(10), STATE int, ZY varchar(10))

insert into tp



select'101','王一','江西',0,'化学'union all

select'102','李二','江西',1,'物理学'union all

select'103','刘三','北京',1,'化学'union all

select'104','张四','北京',1,'物理学'union all

select'105','王二','江西',0,'化学' union all

select'106','李三','江西',0,'物理学'union all

select'107','刘四','北京',1,'化学'union all

select'108','张五','北京',1,'物理学' 

 



--静态

Select ZY 专业,

sum(case SF when '北京' then 1 else 0 end)北京,

sum(case  when SF='北京' and STATE=1 then 1 else 0 end)北京报到数,

RTRIM(cast(sum(case  when SF='北京' and STATE=1 then 1 else 0 end) as numeric(10,2))/sum(case SF when '北京' then 1 else 0 end)*100.00)+'%' 北京报到率, 

sum(case SF when '江西' then 1 else 0 end)江西,

sum(case  when SF='江西' AND STATE=1 then 1 else 0 end)江西报到数,

RTRIM(cast(sum(case  when SF='江西' and STATE=1 then 1 else 0 end) as numeric(10,2))/sum(case SF when '江西' then 1 else 0 end)*100.00)+'%'江西报到率

from tp group by ZY with rollup 



 





--动态

declare @sql varchar(8000)



set @sql = 'select ZY as 专业 '

select @sql = @sql + ' , sum(case when SF =''' + SF + ''' then 1 else 0 end) [' + SF + '人数],

    sum(case when SF =''' + SF + ''' and STATE=1 then 1 else 0 end )['+SF+'报到数],

    rtrim(cast(sum(case when SF =''' + SF + ''' and STATE=1 then 1 else 0 end ) as numeric(10,2))/sum(case when SF =''' + SF + ''' then 1 else 0 end)*100)+''%''   ['+SF+'报到率]'

from (select distinct SF from tp) as a



set @sql = @sql + ' from tp group by ZY with rollup'

exec(@sql)
duanzhi1984 2010-09-08
  • 打赏
  • 举报
 回复
先发个静态的

create table tp(KSH varchar(10), XM  varchar(20),SF varchar(10), STATE int, ZY varchar(10))

insert into tp



select'101','王一','江西',0,'化学'union all

select'102','李二','江西',1,'物理学'union all

select'103','刘三','北京',1,'化学'union all

select'104','张四','北京',1,'物理学'union all

select'105','王二','江西',0,'化学' union all

select'106','李三','江西',0,'物理学'union all

select'107','刘四','北京',1,'化学'union all

select'108','张五','北京',1,'物理学' 

 





Select ZY 专业,

sum(case SF when '北京' then 1 else 0 end)北京,

sum(case  when SF='北京' and STATE=1 then 1 else 0 end)北京报到数,

RTRIM(cast(sum(case  when SF='北京' and STATE=1 then 1 else 0 end) as numeric(10,2))/sum(case SF when '北京' then 1 else 0 end)*100.00)+'%' 北京报到率,





sum(case SF when '江西' then 1 else 0 end)江西,

sum(case  when SF='江西' AND STATE=1 then 1 else 0 end)江西报到数,



RTRIM(cast(sum(case  when SF='江西' and STATE=1 then 1 else 0 end) as numeric(10,2))/sum(case SF when '江西' then 1 else 0 end)*100.00)+'%'江西报到率

from tp group by ZY with rollup 

 



专业         北京          北京报到数       北京报到率                                      江西          江西报到数       江西报到率

---------- ----------- ----------- ------------------------------------------ ----------- ----------- ------------------------------------------

化学         2           2           100.000000000000000%                       2           0           0.000000000000000%

物理学        2           2           100.000000000000000%                       2           1           50.000000000000000%

NULL       4           4           100.000000000000000%                       4           1           25.000000000000000%



(3 行受影响)
y_dong119 2010-09-08
  • 打赏
  • 举报
 回复
我头大，咋那么多人喜欢问行列转换呢
SQLCenter 2010-09-08
  • 打赏
  • 举报
 回复 
--> 测试数据：#

if object_id('tempdb.dbo.#') is not null drop table #

create table #(KSH int, XM varchar(8), SF varchar(8), STATE int, ZY varchar(8))

insert into #

select 101, '王一', '江西', 0, '化学' union all

select 102, '李二', '江西', 1, '物理学' union all

select 103, '刘三', '北京', 1, '化学' union all

select 104, '张四', '北京', 1, '物理学' union all

select 105, '王二', '江西', 0, '化学' union all

select 106, '李三', '江西', 0, '物理学' union all

select 107, '刘四', '北京', 1, '化学' union all

select 108, '张五', '北京', 1, '物理学'



Select zy 专业,

	sum(case sf when '北京' then 1 else 0 end)北京人数,

	sum(case when sf='北京' and STATE=1 then 1 else 0 end)北京报到数,

	sum(case sf when '江西' then 1 else 0 end)江西人数,

	sum(case when sf='江西' and STATE=1 then 1 else 0 end)江西报到数

from # group by zy with rollup





declare @sql varchar(8000)

set @sql = 'select zy as 专业 '

select @sql = @sql + ' , sum(case sf when ''' + sf + ''' then 1 else 0 end) [' + sf + '人数]'+

',sum(case when sf='''+sf+''' and STATE=1 then 1 else 0 end)['+sf+'报到数]'

from (select distinct sf from #) as a

set @sql = @sql + ' from # group by zy with rollup'

exec(@sql)



/*

专业       北京人数        北京报到数       江西人数        江西报到数

-------- ----------- ----------- ----------- -----------

化学       2           2           2           0

物理学      2           2           2           1

NULL     4           4           4           1

*/
Oraclers 2010-09-08
  • 打赏
  • 举报
 回复
加上一列就是：
sum(case when SF='北京' and STATE=1 then 1 else 0 end) as 北京报到数
华夏小卒 2010-09-08
  • 打赏
  • 举报
 回复 
--> 测试数据: [student]

if object_id('[student]') is not null drop table [student]

go

create table [student] (KSH int,XM varchar(4),SF varchar(4),STATE int,ZY varchar(6))

insert into [student]

select 101,'王一','江西',0,'化学' union all

select 102,'李二','江西',1,'物理学' union all

select 103,'刘三','北京',1,'化学' union all

select 104,'张四','北京',1,'物理学' union all

select 105,'王二','江西',0,'化学' union all

select 106,'李三','江西',0,'物理学' union all

select 107,'刘四','北京',1,'化学' union all

select 108,'张五','北京',1,'物理学'



--1

select 专业=ZY,

	 北京人数=sum(case when SF='北京' then 1 else 0 end),

	 北京报到数=sum(case when SF='北京' and STATE=1 then 1 else 0 end),

	 江西=sum(case when SF='江西' then 1 else 0 end),

	 江西报到数=sum(case when SF='江西' and STATE=1 then 1 else 0 end)

from [student]

group by ZY

with rollup



专业     北京人数        北京报到数       江西          江西报到数

------ ----------- ----------- ----------- -----------

化学     2           2           2           0

物理学    2           2           2           1

NULL   4           4           4           1



(3 行受影响)



--2

declare @sql varchar(8000)



set @sql = 'select zy as 专业 '

select @sql = @sql + ' , sum(case when sf =''' + sf + ''' then 1 else 0 end) [' + sf + '人数],

	sum(case when sf =''' + sf + ''' and state=1 then 1 else 0 end )['+sf+'报到数]'

from (select distinct sf from student) as a



set @sql = @sql + ' from student group by zy with rollup'

exec(@sql)





 







专业     北京人数        北京报到数       江西人数        江西报到数

------ ----------- ----------- ----------- -----------

化学     2           2           2           0

物理学    2           2           2           1

NULL   4           4           4           1



(3 行受影响)
happybuttom 2010-09-08
  • 打赏
  • 举报
 回复
请高手光临，不胜感激呀。
经典SQL语句大全
最最经典的SQL语句大全。菜鸟看了就懂了；高手看了就惊了。
ORACLE__SQL语句教学（附有sql最常用经典语句）
数据库编程高手必看经典！！！！！！！！！！！！！！！！！！！！！
生成数据库值为插入语句
在做数据库安装程序时，发现数据库中有许多初始值若要添加，但如果手动...使用很简单，看看界面就行，使用的是sql。如果有什么错误，请联系fwjindream@yahoo.com.cn我是小鸟，如果各位高手有什么好的想法，请多多指教。
Oracle公司内部数据库培训资料
想成为oracle高手就看下吧，很系统的资料。希望对你有帮助 内容摘要： 基本SQL SELECT语句 目标 SQL SELECT 语句的功能 基本 SELECT 语句 选择全部列 选择特定的列 Writing SQL Statements 过滤和排序数据 目标 在...
翻页存储过程
写了个简单的存储过程分页，喜欢的朋友，或者刚刚学习存储过程的朋友可以看看，高手勿看！！ 下面是分页存储过程，直接复制到sql2005，查询语句执行 一下存储过程格式可以通过vs2005，新建项目里面的添加存储过程来...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章