(有挑战性)--复杂的行记录变列，并进行统计.

bdcman 2008-11-27 07:11:20
要求建一个存储过程,参数4个
参数1: @region nvarchar(50) //部门范围 有两种1. 'All' 代表所有部门， 2.具体部门(如'部门一')
参数2: @timeregion varchar(20) //时间范围 分为 "年" 或 "月" 或 "日"
参数3: @time varchar(20) //具体时间 如 "2008" 或 "2008-1" 或 "2008-1-4"
参数4: @status varchar(20) //状态 分为"全部"(即不管同意或不同意) , "同意" , "不同意"


目的: 要求统计每个部门在时间范围内的记录数( count(记录) ) 即可.


例: 当 @region='All',@timeregion='年',@time='2008',@status='全部' 时

存储过程应得到下列2张表: (一张是本年所有月份的统计，一张是本年四季度的统计)

表1.

表2.



当 @region='All',@timeregion='月',@time='2008-1',@status='全部' 时 得到下面这张表(下面是同一张)


当 @region='All',@timeregion='日',@time='2008-1-4',@status='全部' 时 得到下面这张表



...全文
151 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
flairsky 2008-11-28
  • 打赏
  • 举报
 回复
精华说的很清楚了...
查一下也有挑战性么
dobear_0922 2008-11-28
  • 打赏
  • 举报
 回复
不说了，帮顶...
dawugui 2008-11-28
  • 打赏
  • 举报
 回复 


/*

标题：普通行列转换(version 2.0)

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)

时间：2008-03-09

地点：广东深圳

说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。



问题：假设有张学生成绩表(tb)如下:

姓名 课程 分数

张三 语文 74

张三 数学 83

张三 物理 93

李四 语文 74

李四 数学 84

李四 物理 94

想变成(得到如下结果)： 

姓名 语文 数学 物理 

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

李四 74   84   94

张三 74   83   93

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

*/



create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('张三' , '语文' , 74)

insert into tb values('张三' , '数学' , 83)

insert into tb values('张三' , '物理' , 93)

insert into tb values('李四' , '语文' , 74)

insert into tb values('李四' , '数学' , 84)

insert into tb values('李四' , '物理' , 94)

go



--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)

select 姓名 as 姓名 ,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理

from tb

group by 姓名



--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

declare @sql varchar(8000)

set @sql = 'select 姓名 '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) as a

set @sql = @sql + ' from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程

set @sql = '[' + @sql + ']'

exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')



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



/*

问题：在上述结果的基础上加平均分，总分，得到如下结果：

姓名 语文 数学 物理 平均分 总分 

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

李四 74   84   94   84.00  252

张三 74   83   93   83.33  250

*/



--SQL SERVER 2000 静态SQL。

select 姓名 姓名,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理,

  cast(avg(分数*1.0) as decimal(18,2)) 平均分,

  sum(分数) 总分

from tb

group by 姓名



--SQL SERVER 2000 动态SQL。

declare @sql varchar(8000)

set @sql = 'select 姓名 '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) as a

set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程

exec ('select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名')



drop table tb	



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

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



/*

问题：如果上述两表互相换一下：即表结构和数据为：

姓名 语文 数学 物理

张三 74　　83　　93

李四 74　　84　　94

想变成(得到如下结果)： 

姓名 课程 分数 

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

李四 语文 74

李四 数学 84

李四 物理 94

张三 语文 74

张三 数学 83

张三 物理 93

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

*/



create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)

insert into tb values('张三',74,83,93)

insert into tb values('李四',74,84,94)

go



--SQL SERVER 2000 静态SQL。

select * from

(

 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

 union all

 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb

 union all

 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end



--SQL SERVER 2000 动态SQL。

--调用系统表动态生态。

declare @sql varchar(8000)

select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'

from syscolumns 

where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列

order by colid asc

exec(@sql + ' order by 姓名 ')



--SQL SERVER 2005 动态SQL。

select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t



--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。



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

/*

问题：在上述的结果上加个平均分，总分，得到如下结果：

姓名 课程   分数

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

李四 语文   74.00

李四 数学   84.00

李四 物理   94.00

李四 平均分 84.00

李四 总分   252.00

张三 语文   74.00

张三 数学   83.00

张三 物理   93.00

张三 平均分 83.33

张三 总分   250.00

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

*/



select * from

(

 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

 union all

 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb

 union all

 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb

 union all

 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb

 union all

 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end



drop table tb
jinjazz 2008-11-28
  • 打赏
  • 举报
 回复
楼主太懒了...
wdwen1986 2008-11-28
  • 打赏
  • 举报
 回复
顶
you_tube 2008-11-27
  • 打赏
  • 举报
 回复
太有难度了，帮顶
lonlyhawk 2008-11-27
  • 打赏
  • 举报
 回复
高手不屑力气活，我来练手。
其实除开动态行列转换，还是有一些技术活的，那就是取月参数时，该月的每一天都要显示（即使没有数据），而且月的总天数不是固定的。

create proc [dbo].[wen_test] 

(@region  nvarchar(50) ,@timeregion    nvarchar(20) ,@time    varchar(20),@status  varchar(20))

as 

select top 50 fid=identity(int,1,1) into #t from Approved a,Approved b

declare @sql nvarchar(4000)



if @timeregion=N'年'

begin

	select @sql=N'select isnull(部门,N''总计'') [日期/部门], '

	select @sql=@sql+N'sum(case when year([审批日期])='+@time+N' and month([审批日期])='+

		cast(fid as varchar(2))+' then 1 else 0 end) ['+cast(fid as varchar(2))+N'月],'

	from #t where fid<=12

	set @sql=@sql+N'sum(case when year([审批日期])='+@time+N' then 1 else 0 end) 小计 from Approved   group by  部门 with rollup'

	EXEC(@sql)

	select isnull(部门,N'总计') [日期/部门],

		一季度=sum(case when year([审批日期])=@time and  month([审批日期])<=3 then 1 else 0 end),

		二季度=sum(case when year([审批日期])=@time and  month([审批日期]) between 4 and 6 then 1 else 0 end),

		三季度=sum(case when year([审批日期])=@time and  month([审批日期]) between 7 and 9  then 1 else 0 end),

		四季度=sum(case when year([审批日期])=@time and  month([审批日期])>=10 then 1 else 0 end),

		小计=sum(case when  year([审批日期])=@time then 1 else 0 end)

	from Approved   group by  部门 with rollup 

end



if @timeregion=N'月'

begin

	select @sql=N'select isnull(部门,N''总计'') [日期/部门], '

	select @sql=@sql+N'sum(case when ([审批日期])='''+@time+'-'+

		cast(fid as varchar(2))+''' then 1 else 0 end) ['++@time+'-'+cast(fid as varchar(2))+N'],'

	from #t where fid<=datediff(day,@time+'-1',dateadd(month,1,@time+'-1'))

	set @sql=@sql+N'sum(case when convert(varchar(7),[审批日期],20)=convert(varchar(7),cast('''+

		@time+N'-1'' as datetime),20) then 1 else 0 end) 小计 from Approved   group by  部门 with rollup'

	EXEC(@sql)

end



if @timeregion=N'日'

begin

	select @sql=N'select isnull(部门,N''总计'') [日期/部门],['+

		@time+N']=sum(case when [审批日期]='''+@time+N''' then 1 else 0 end),

		小计= sum(case when [审批日期]='''+@time+N''' then 1 else 0 end)

		from  Approved   group by  部门 with rollup'

	EXEC(@sql)	

end
CN_SQL 2008-11-27
  • 打赏
  • 举报
 回复
呵呵~
bdcman 2008-11-27
  • 打赏
  • 举报
 回复
感谢大家的关注，因为时间比较赶所以发贴请求帮助，也正在找相关的贴子，谢谢大家的帮忙。
kelph 2008-11-27
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 fcuandy 的回复:]
确实很有挑战性，留给其它人吧。


发贴前先找 "精华［推荐］" 里的行列转换合并贴。
[/Quote]
FC还算含蓄
不做是有原因的
这纯力气活，无挑战性可言，只要搜过以前的一些贴子，自己完全应该能做出来。
还有用100分找人代替自己完成任务是不可取的
水族杰纶 2008-11-27
  • 打赏
  • 举报
 回复
帮顶吧~~~~~~~
让你望见影子的墙 2008-11-27
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 fcuandy 的回复:]
确实很有挑战性，留给其它人吧。


发贴前先找 "精华［推荐］" 里的行列转换合并贴。
[/Quote]

工作量太大了。也和有挑战性啊，同意留给达人们
nalnait 2008-11-27
  • 打赏
  • 举报
 回复
好厉害
fcuandy 2008-11-27
  • 打赏
  • 举报
 回复
确实很有挑战性，留给其它人吧。


发贴前先找 "精华［推荐］" 里的行列转换合并贴。
bdcman 2008-11-27
  • 打赏
  • 举报
 回复
下面是表的数据结构及数据



表:Approved 结构

CREATE TABLE [dbo].[Approved](
[id] [int] IDENTITY(1,1) NOT NULL,
[部门] [nvarchar](50),
[审批编号] [varchar](50),
[审批结果] [nvarchar](50),
[审批日期] [datetime] NULL
)

-----------------数据----------------


insert into Approved values ('部门一','BA001','同意','2008-1-2')
insert into Approved values ('部门一','BA002','同意','2008-1-2')
insert into Approved values ('部门一','BA003','不同意','2008-1-4')
insert into Approved values ('部门一','BA004','同意','2008-1-4')
insert into Approved values ('部门一','BA005','同意','2008-2-4')

insert into Approved values ('部门二','BA006','同意','2008-1-2')
insert into Approved values ('部门二','BA007','同意','2008-1-4')
insert into Approved values ('部门二','BA008','同意','2008-1-4')
insert into Approved values ('部门二','BA009','同意','2008-2-5')
insert into Approved values ('部门二','BA010','不同意','2008-3-5')

insert into Approved values ('部门三','BA011','同意','2008-1-2')
insert into Approved values ('部门三','BA012','不同意','2008-1-4')
insert into Approved values ('部门三','BA013,'不同意','2008-10-4')
insert into Approved values ('部门三','BA014','不同意','2008-10-4')

insert into Approved values ('部门四','BA015','同意','2008-5-2')
insert into Approved values ('部门四','BA016','同意','2008-6-2')
insert into Approved values ('部门四','BA017','同意','2008-6-2')
insert into Approved values ('部门四','BA018','不同意','2008-7-4')

insert into Approved values ('部门五','BA019','同意','2008-8-2')
insert into Approved values ('部门五','BA020','同意','2008-8-2')
insert into Approved values ('部门五','BA021','同意','2008-10-2')
insert into Approved values ('部门五','BA022','不同意','2008-11-4')


------------------------------------
探索数据的力量：Elasticsearch中指定链表字段的统计查询记录
本文探讨了在Elasticsearch中对sell_product_order索引中sellingProducts字段进行基本统计操作的方法。通过一系列具体查询示例，介绍了如何计算产品类型的频率、检索指定文档的产品数量，以及对产品类型进行细分统计。这些统计方法不仅增强了数据分析能力，还为业务决策提供了支持。文章强调了Elasticsearch的灵活性和高效性，并鼓励读者进一步探索其更多功能，以提升数据处理和决策支持的能力。
京东物流-三维装箱（记录）
文章目录前言背景方法问题分析方法概述第一版本第二版本第三章 实验结果装箱结果分析与验证总结与展望.总结展望代码题目 前言 就只水了个初赛，复赛必须要用Java写，就放弃了，这里记录一下写的东西。 队友: 思禾 OuJiang2021 背景 随着国民经济的快速发展，我国物流行业的业务需求量越来越多。如图1-1所示，根据国家统计局官方数据表明，2016-2021上半年全国社会物流总费用都呈现了增长的趋势，这即是国家国力提升，国民生活素质的不断提高的表现，同时也体现了中国的物流业发展的日益强大。今天的中国的物流业
2021-07-02 swift大礼包
全面的Swift学习资料整理_walkerwqp的博客-CSDN博客 全面的Swift学习资料整理 ...
Deep Learning for UAV-based Object Detection andTracking: A Survey（论文翻译)
目录 摘要 1.引言 2.相关调查和简要统计 A.无人机飞机统计数据 B.挑战 C.贡献 3.无人机机载图像中的目标检测 A.数据处理 B.基于尺度多样性的目标检测 C.小目标上的目标检测 D.基于方向分集的目标检测 E.基于检测速度的目标检测 F.基于其他的目标检测 4.无人机载视频中的目标检测 A.基于光流的网络 B.基于记忆网的网络 C.基于跟踪的网络 5.基于无人机视频的多目标跟踪 A.Tracking-by-Detection B.单目标跟踪辅助多目...
GraphRAG：复杂查询的知识图谱新框架
微软最近发布了名为 GraphRAG(Graphs + Retrieval Augmented Generation)的创新 RAG 框架，这是一个将文本提取、语义网络分析，与大语言模型(LLM)的提示和总结功能结合在一起的端到端系统，用于深入理解文本数据集。在对私有数据中的复杂文本信息进行文档分析时，GrahRAG 使用...
疑难问题

22,298

社区成员

121,731

社区内容

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

加载中

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

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

+ 用AI写文章