我在写个你就明白了select row_number()over(order by tb.curr_term_value desc) as rownum,tb.cell_cn_name from ( SELECT DISTINCT Top (100) Percent rd.cell_index,rd.freq_id,rd.data_range_id,rd.org_id,convert(float,rd.curr_term_value) AS curr_term_value, rd.cur_id ,rd.rept_year,rd.rept_month,rd.template_id,rd.version_id, rd.[status],rd.row_id, rd.col_id,dbo.ReplaceX( ci.cell_cn_name,' | 本外币合计') as cell_cn_name,ci.cell_cn_name as cell_cn_name2 FROM report_data rd INNER JOIN cell_info ci ON rd.cell_index=ci.cell_index AND rd.template_id='G0100' AND rd.version_id = (select max(version_id) from template_info where template_id='G0100' and convert(varchar(10),start_date,102)<=convert(varchar(10),dbo.getdatew(2011,1),102) and convert(varchar(10),dbo.getdatew(2011,1),102)<=convert(varchar(10),end_date,102)) AND ci.col_id = 'F' AND ci.row_id>=(SELECT MAX(row_id) FROM cell_info ci WHERE ci.cell_index='G01000690F6' AND ci.version_id=(select max(version_id) from template_info where template_id='G0100' and convert(varchar(10),start_date,102)<=convert(varchar(10),dbo.getdatew(2011,1),102) and convert(varchar(10),dbo.getdatew(2011,1),102)<=convert(varchar(10),end_date,102))) AND ci.row_id<(select max(row_id) from cell_info where cell_index='G01000690F62' and version_id= (select max(version_id) from template_info where template_id='G0100' and convert(varchar(10),start_date,102)<=convert(varchar(10),dbo.getdatew(2011,1),102) and convert(varchar(10),dbo.getdatew(2011,1),102)<=convert(varchar(10),end_date,102))) AND ci.cell_cn_name NOT LIKE '%[0-9].[0-9]%' AND rd.rept_year=2011 AND rd.rept_month=1 AND rd.data_range_id=1 AND rd.org_id='F104H101520301001') tb where ......
SELECT DISTINCT Top (100) Percent rd.cell_index,rd.freq_id,rd.data_range_id,rd.org_id,convert(float,rd.curr_term_value) AS curr_term_value, rd.cur_id ,rd.rept_year,rd.rept_month,rd.template_id,rd.version_id, rd.[status],rd.row_id, rd.col_id,dbo.ReplaceX( ci.cell_cn_name,' | 本外币合计') as cell_cn_name,ci.cell_cn_name as cell_cn_name2
FROM report_data rd INNER JOIN cell_info ci ON rd.cell_index=ci.cell_index AND rd.template_id='G0100' AND rd.version_id = (select max(version_id) from template_info where template_id='G0100' and convert(varchar(10),start_date,102)<=convert(varchar(10),dbo.getdatew(2011,1),102)
and convert(varchar(10),dbo.getdatew(2011,1),102)<=convert(varchar(10),end_date,102))
AND ci.col_id = 'F' AND ci.row_id>=(SELECT MAX(row_id)
FROM cell_info ci WHERE ci.cell_index='G01000690F6' AND ci.version_id=(select max(version_id) from template_info where template_id='G0100'
and convert(varchar(10),start_date,102)<=convert(varchar(10),dbo.getdatew(2011,1),102)
and convert(varchar(10),dbo.getdatew(2011,1),102)<=convert(varchar(10),end_date,102)))
AND ci.row_id<(select max(row_id) from cell_info where cell_index='G01000690F62' and version_id=
(select max(version_id) from template_info where template_id='G0100'
and convert(varchar(10),start_date,102)<=convert(varchar(10),dbo.getdatew(2011,1),102)
and convert(varchar(10),dbo.getdatew(2011,1),102)<=convert(varchar(10),end_date,102)))
AND ci.cell_cn_name NOT LIKE '%[0-9].[0-9]%'
AND rd.rept_year=2011 AND rd.rept_month=1 AND rd.data_range_id=1
AND rd.org_id='F104H101520301001') tb
where ......
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。(25 行受影响)
表 'cell_info'。扫描计数 248,逻辑读取 178184 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'report_data'。扫描计数 1,逻辑读取 9668 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'template_info'。扫描计数 3,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:
CPU 时间 = 13104 毫秒,占用时间 = 13191 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。