02-如何优化数据库查询脚本(4)

exam_arrange_uid varchar(36) not null

primary key (exam_arrange_uid) WITH (IGNORE_DUP_KEY = ON) )

declare @T3 table (

owner_uid varchar(36) not null

primary key (owner_uid) WITH (IGNORE_DUP_KEY = ON) )

insert @T1 select exam_uid,count(exam_grade_uid) as attend_times from exam_grade with (nolock) where is_examination = 'N' and

user_uid=N'C6F2C02E-77B5-4866-8630-8DD05F1BB127' group by exam_uid

insert @T3 values(N'C6F2C02E-77B5-4866-8630-8DD05F1BB127') insert @T3 values(N'd04f4bd2-b5ba-44fb-a7db-29b657f10073') insert @T3 values(N'71eebecc-0129-4697-999b-79fa1bbc5fd6') insert @T3 values(N'68639fa3-fd11-4831-a8a4-9fae4c2d95f6') insert @T3 values(N'4216eb73-1375-4165-9441-3776050ff4a6') insert @T3 values(N'4fd9916c-66c2-4955-8d39-8c033185bf59') insert @T3 values(N'81cdbef2-6804-4ba6-ac1d-d8e5b3ac0b9f') insert @T3 values(N'bbae8e98-33f4-4464-9ee3-c4947021a7f8') insert @T3 values(N'd04f4bd2-b5ba-44fb-a7db-29b657f10073') insert @T3 values(N'A2B1B540-08C2-4130-BFF1-2CD9211CBC26') insert @T3 values(N'71eebecc-0129-4697-999b-79fa1bbc5f19')

insert @T2 select exam_user.exam_arrange_uid from exam_user with (nolock),@T3 exam_user2 where exam_user.owner_uid = exam_user2.owner_uid

select * from (

select row_number() over (order by exam_code desc) as rowId, exam_exam.*,exam_arrange.exam_arrange_uid,exam_arrange.begin_time,exam_arrange.end_time,exam_grade_account.attend_times,exam_arrange.is_examination

from exam_exam with (nolock)

inner join exam_arrange with (nolock) on inner join nv_folder with (nolock) on inner join @T2 exam_user on

left join @T1 exam_grade_account on where

exam_exam.exam_uid=exam_arrange.exam_uid exam_exam.folder_uid=nv_folder.folder_uid

exam_arrange.exam_arrange_uid=exam_user.exam_arrange_uid exam_exam.exam_uid=exam_grade_account.exam_uid

exam_arrange.begin_time

_publish_exam_time,getdate()),120)

and (exam_arrange.end_time>N'2012-05-09 11:04:44' or and (exam_exam.exam_class_code = N'exam') and (exam_exam.exam_status_code = N'normal') and (exam_arrange.is_examination <> N'Y')

exam_arrange.end_time is null)

) as t where rowId between 1 and 10