谁来帮忙精简下此条SQL，长度超过4000了

huyou1983218 2010-11-20 06:28:17 


 Select Top 11 cell_cn_name FROM (select top 11 Row_Number() 

over(order by incr_value desc) as rownum1 ,* from ( select top 5 

Row_Number() over(order by incr_value desc) as 

rownum,replace(replace(replace(replace(replace

(replace(replace(replace(replace(replace(replace

(replace(cell_cn_name,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'.',''),' | 各项贷款','') as cell_cn_name, round(incr_value/10000,2) as 

incr_value,rept_year,rept_month,org_id,data_range_id,cur_id,freq_id 

from (select cell_cn_name,cast(isnull(curr_term_value,0) as float)

 - cast(isnull(prev_term_value,0)as float) as incr_value, 

rept_year,rept_month,org_id,data_range_id,cur_id,freq_id from

 ( select t1.cell_cn_name,rd.curr_term_value, 

rd.rept_year,rd.rept_month,rd.org_id,rd.data_range_id,rd.cur_id,

rd.freq_id ,(select curr_term_value from

 report_data d where d.cell_index=rd.cell_index and d.rept_year=dbo.getlastyear(2,9,2010,1) and 

d.rept_month=dbo.getlastmonth(2,9,1) and d.cur_id=rd.cur_id and 

d.freq_id=rd.freq_id and (d.status='1' or d.status is null) ) as 

prev_term_value from report_data rd, (select 

cell_index,cell_cn_name,version_id from cell_info where 

template_id='G1101' and col_id='C' and ((row_id>=10 and row_id<=29) 

or row_id=35 or row_id=36)) t1 where rd.cell_index=t1.cell_index 

and rd.version_id=t1.version_id and rd.rept_year=2010 and rd.rept_month=9 and rd.org_id='F093H101510101001' and 

rd.data_range_id=1 and rd.cur_id=1 and (rd.status='1'

 or rd.status is null) ) t2) as tmpDR union all select top 5 Row_Number() over(order by incr_value asc) as rownum,replace(replace(replace(replace(replace(replace

(replace(replace(replace(replace(replace(replace

(cell_cn_name,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6','')

,'7',''),'8',''),'9',''),'.',''),' | 各项贷款','') as cell_cn_name, 

round(incr_value/10000,2) as 

incr_value,rept_year,rept_month,org_id,data_range_id,

cur_id,freq_id from (select cell_cn_name,cast(isnull(curr_term_value,0) as float) - cast(isnull(prev_term_value,0)as float) as incr_value,

 rept_year,rept_month,org_id,data_range_id,

cur_id,freq_id from ( select t1.cell_cn_name,rd.curr_term_value, rd.rept_year,rd.rept_month,rd.org_id,rd.data_range_id,

rd.cur_id,rd.freq_id ,(select curr_term_value from report_data d where d.cell_index=rd.cell_index and d.rept_year=dbo.getlastyear(2,9,2010,1) and d.rept_month=dbo.getlastmonth(2,9,1) and 

d.cur_id=rd.cur_id and d.freq_id=rd.freq_id and (d.status='1' or d.status is null) ) as prev_term_value 

from report_data rd,

 (select cell_index,cell_cn_name,version_id from cell_info where template_id='G1101' and col_id='C' and ((row_id>=10 and row_id<=29) or row_id=35 or row_id=36)) t1

 where rd.cell_index=t1.cell_index and rd.version_id=t1.version_id and rd.rept_year=2010 and rd.rept_month=9 and rd.org_id='F093H101510101001' 

and rd.data_range_id=1 and rd.cur_id=1 and (rd.status='1' or rd.status is null) ) t2) as tmpDR 

union all 

select 6,'行业平均' as cell_cn_name , 

avg(incr_value),rept_year,rept_month,org_id,data_range_id,cur_id,freq_id from (select cell_cn_name,cast(isnull(curr_term_value,0) as float) - cast(isnull(prev_term_value,0)as float) as incr_value, 

rept_year,rept_month,org_id,data_range_id,cur_id,

freq_id from ( select t1.cell_cn_name,rd.curr_term_value, rd.rept_year,rd.rept_month,rd.org_id,rd.data_range_id,

rd.cur_id,rd.freq_id ,(select curr_term_value from report_data d where d.cell_index=rd.cell_index and 

d.rept_year=dbo.getlastyear(2,9,2010,1) and 

d.rept_month=dbo.getlastmonth(2,9,1) and d.cur_id=rd.cur_id and

 d.freq_id=rd.freq_id and (d.status='1' or d.status is null) ) as 

prev_term_value from report_data rd, (select 

cell_index,cell_cn_name,version_id from cell_info where 

template_id='G1101' and col_id='C' and ((row_id>=10 

and row_id<=29) or row_id=35 or row_id=36)) t1 where rd.cell_index=t1.cell_index and rd.version_id=t1.version_id and

 rd.rept_year=2010 and rd.rept_month=9 and rd.org_id='F093H101510101001' and rd.data_range_id=1 

and rd.cur_id=1 and (rd.status='1' or rd.status is null) ) t2)as 

tmpDR group by rept_year,rept_month,org_id,data_range_id,cur_id,freq_id ) 

temp order by rownum1 ) as t  where Org_ID='F093H101510101001' AND Rept_Year=2010 AND Rept_Month=9 AND 

Data_Range_ID=1 AND Cur_ID=1 AND Freq_ID<=2
...全文
106 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
王向飞 2010-11-20
  • 打赏
  • 举报
 回复
-晴天 2010-11-20
  • 打赏
  • 举报
 回复 
;with cte as(

      select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(cell_cn_name,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'.',''),' | 各项贷款','') as cell_cn_name, 

        cast(isnull(curr_term_value,0) as float) - cast(isnull(prev_term_value,0)as float) as incr_value, 

        rept_year,rept_month,org_id,data_range_id,cur_id,freq_id 

      from (

        select t1.cell_cn_name,rd.curr_term_value, rd.rept_year,rd.rept_month,rd.org_id,rd.data_range_id,rd.cur_id,rd.freq_id ,

          (select curr_term_value from report_data d where d.cell_index=rd.cell_index and d.rept_year=dbo.getlastyear(2,9,2010,1) and 

             d.rept_month=dbo.getlastmonth(2,9,1) and d.cur_id=rd.cur_id and d.freq_id=rd.freq_id and (d.status='1' or d.status is null) ) as prev_term_value

        from report_data rd, (

          select cell_index,cell_cn_name,version_id 

          from cell_info where template_id='G1101' and col_id='C' and ((row_id>=10 and row_id<=29) or row_id=35 or row_id=36)

        ) t1

        where rd.cell_index=t1.cell_index and rd.version_id=t1.version_id and rd.rept_year=2010 and rd.rept_month=9 

          and rd.org_id='F093H101510101001' and rd.data_range_id=1 and rd.cur_id=1 and (rd.status='1' or rd.status is null) and Freq_ID<=2

      ) t2

)

Select cell_cn_name FROM (

  select top 11 Row_Number() over(order by incr_value desc) as rownum1 ,* from ( 

    select top 5 Row_Number() over(order by incr_value desc) as rownum,cell_cn_name,round(incr_value/10000,2) as incr_value,rept_year,rept_month,org_id,data_range_id,cur_id,freq_id 

      from cte

    union all 

    select top 5 Row_Number() over(order by incr_value asc) as rownum,cell_cn_name,round(incr_value/10000,2) as incr_value,rept_year,rept_month,org_id,data_range_id,cur_id,freq_id

      from cte

    union all

    select 6,'行业平均' as cell_cn_name ,avg(incr_value),rept_year,rept_month,org_id,data_range_id,cur_id,freq_id

      from cte

    group by rept_year,rept_month,org_id,data_range_id,cur_id,freq_id 

  ) temp order by rownum1 

) as t  

--http://topic.csdn.net/u/20101120/18/c3863270-c969-47f9-b772-a2b7fb2ecc7d.html?56446
xyytuo 2010-11-20
  • 打赏
  • 举报
 回复
coleling 2010-11-20
  • 打赏
  • 举报
 回复
在不清楚具体结构的情况下，只能做到这一步了


With t1 as

(

	select cell_index,cell_cn_name,version_id 

	from cell_info 

	where template_id='G1101' and col_id='C' and ((row_id>=10 and row_id<=29) or row_id=35 or row_id=36)

)



,t2 as

(

	select t1.cell_cn_name,rd.curr_term_value, rd.rept_year,rd.rept_month,

		rd.org_id,rd.data_range_id,rd.cur_id,rd.freq_id ,(

		select curr_term_value from report_data d 

		where d.cell_index=rd.cell_index and d.rept_year=dbo.getlastyear(2,9,2010,1) and 

			d.rept_month=dbo.getlastmonth(2,9,1) and d.cur_id=rd.cur_id and

			d.freq_id=rd.freq_id and (d.status='1' or d.status is null) 

		) as prev_term_value 

	from report_data rd, t1 

	where rd.cell_index=t1.cell_index and rd.version_id=t1.version_id and

		rd.rept_year=2010 and rd.rept_month=9 and rd.org_id='F093H101510101001' and 

		rd.data_range_id=1 and rd.cur_id=1 and (rd.status='1' or rd.status is null) 

)



,tmpDR as

(

	select cell_cn_name,

		cast(isnull(curr_term_value,0) as float) - cast(isnull(prev_term_value,0)as float) as incr_value, 

		rept_year,rept_month,org_id,data_range_id,cur_id,freq_id 

	from t2

)



--将内外的Top 11都去掉了，因为5+5+1最多就11条，不需要再加

Select cell_cn_name 

FROM (

	select Row_Number() over(order by incr_value desc) as rownum1 ,* 

	from ( 

		select top 5 Row_Number() over(order by incr_value desc) as rownum,

			replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(

			replace(replace(cell_cn_name,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6','')

			,'7',''),'8',''),'9',''),'.',''),' | 各项贷款','') as cell_cn_name, 

			round(incr_value/10000,2) as incr_value,rept_year,rept_month,org_id,data_range_id,cur_id,freq_id 

		from tmpDR 



		union all 



		select top 5 Row_Number() over(order by incr_value asc) as rownum,

			replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(

			replace(replace(cell_cn_name,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6','')

			,'7',''),'8',''),'9',''),'.',''),' | 各项贷款','') as cell_cn_name, 

			round(incr_value/10000,2) as incr_value,rept_year,rept_month,org_id,data_range_id,cur_id,freq_id 

		from tmpDR 



		union all 



		select 6,'行业平均' as cell_cn_name , 

			avg(incr_value),rept_year,rept_month,org_id,data_range_id,cur_id,freq_id 

		from tmpDR 



		group by rept_year,rept_month,org_id,data_range_id,cur_id,freq_id 

	) temp 

) as t  

where Org_ID='F093H101510101001' AND Rept_Year=2010 AND Rept_Month=9 AND Data_Range_ID=1 AND Cur_ID=1 AND Freq_ID<=2

order by rownum1

其中的Replace可以用自定义函数解决。

最后，拜一下本语句的原创者，太强大了
叶子 2010-11-20
  • 打赏
  • 举报
 回复
替换0123456789.可以用正则，可以用自定义函数。
-晴天 2010-11-20
  • 打赏
  • 举报
 回复
第二级查询中已经有了 top 11,主查询就不需要用了.
-晴天 2010-11-20
  • 打赏
  • 举报
 回复
最简单的:每个联合的子查询里都有Org_ID='F093H101510101001',那主查询语句里还要它干嘛?
-晴天 2010-11-20
  • 打赏
  • 举报
 回复
有好多内容都是重复的.
chen8410 2010-11-20
  • 打赏
  • 举报
 回复
这么长的代码，至少先把格式调一下也好读
最好提供点测试数据
飘零一叶 2010-11-20
  • 打赏
  • 举报
 回复
子查询太多了....
飘零一叶 2010-11-20
  • 打赏
  • 举报
 回复

飘零一叶 2010-11-20
  • 打赏
  • 举报
 回复

DataBox-MDX 2010-11-20
  • 打赏
  • 举报
 回复
神啊！牛人
极简之上 : jfinal beetlsql 缺点 优点 ORM篇 卷I
1.前言 不夸张的说你可以花上一天时间来看本文. 因为文中的内容给你将来节约的时间或许不止一天. 我并不喜欢写博客(高质量的). 毕竟花费的时间精力太大了, 也不知道写的东西对阅读者是否有用. 如果没用这样往往浪费阅读者的时间. 在写完一篇博客我时常需要...
Mysql注入小tips --持续更新中
　　学习Web安全好几年了，接触最多的是Sql注入，一直最不熟悉的也是Sql注入。OWASP中，Sql注入危害绝对是Top1。花了一点时间研究了下Mysql类型的注入。 　　　　文章中的tips将会持续更新，先说说这些天研究的 　　　　这里博主以数字类型注入类型进行讲解，字符类型同理，这里不在敖述。 　　　　我们的环境:phpstudy+mysql+php 　　　　　　我们的测试代码如下: &...
mysql mod div_Mysql注入小tips --持续更新中
学习Web安全好几年了，接触最多的是Sql注入，一直最不熟悉的也是Sql注入。OWASP中，Sql注入危害绝对是Top1。花了一点时间研究了下Mysql类型的注入。文章中的tips将会持续更新，先说说这些天研究的这里博主以数字类型注入类型进行讲解，字符类型同理，这里不在敖述。我们的环境:phpstudy+mysql+php我们的测试代码如下:$conn = mysql_connect('127.0...
SSM优秀宿舍评选系统毕业设计-附源码221511
优秀宿舍评选系统 使用Java语言，采用基于 MVVM模式的SSM技术进行开发，使用 Eclipse 2017 CI 10 编译器编写，数据方面主要采用的是微软的SQLserver数据库来作为数据存储媒介，配合前台HTML+CSS 技术完成系统的开发。 本设计实现集人性化、高效率、便捷等优点于一身的优秀宿舍评选系统 ，完成首页、用户管理（管理员、宿管人员、学生）更多管理（宿舍信息、宿舍分配、违纪信息、公告栏、活动信息、维修信息、意见管理）等功能模块。 关键词：优秀宿舍评选；SQLserver数据库；SSM.
SSM计算机基础自学系统毕业设计-附源码221509
本设计主要实现集人性化、高效率、便捷等优点于一身的计算机基础自学系统 ，完成首页、站点管理（公告栏、轮播图）用户管理（管理员、教师、学生）内容管理（课程资讯、资讯分类）更多管理（课程管理、学习资料、学习进度、学习成绩）等功能模块。 计算机基础自学系统使用Java语言，采用基于 MVVM模式的SSM技术进行开发，使用 Eclipse 2017 CI 10 编译器编写，数据方面主要采用的是微软的SQLserver数据库来作为数据存储媒介，配合前台HTML+CSS 技术完成系统的开发。...
疑难问题

22,209

社区成员

121,730

社区内容

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

加载中

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

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

+ 用AI写文章