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

Question

本帖最后由 happybuttom 于 2010-09-08 21:41:18 编辑

Answer 1

--> 测试数据: [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 aset @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 行受影响)

Answer 2

加上一列就是：
sum(case when SF='北京' and STATE=1 then 1 else 0 end) as 北京报到数

Answer 3

--> 测试数据：#
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
*/

Answer 4

先发个静态的create table tp(KSH varchar(10), XM  varchar(20),SF varchar(10), STATE int, ZY varchar(10))
insert into tpselect'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 行受影响)

Answer 5

create table tp(KSH varchar(10), XM  varchar(20),SF varchar(10), STATE int, ZY varchar(10))
insert into tpselect'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 aset @sql = @sql + ' from tp group by ZY with rollup'
exec(@sql)
 

Answer 6

实在是佩服，我开始自己试了，不行？？/我试了以下语句
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