在不清楚具体结构的情况下,只能做到这一步了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可以用自定义函数解决。最后,拜一下本语句的原创者,太强大了
;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
(
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可以用自定义函数解决。最后,拜一下本语句的原创者,太强大了
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