select a.freeze_logId,a.OD_ID,a.SC_ID,a.status
,b.AD_ID,b.buy_uid,b.buy_zid,b.create_time,b.oid,b.order_number,b.type
,c.ad_title,c.user_id
,d.S_DATETIME,e.nick as Buy_Nick
,f.nick as TeacheNick
,(select COUNT(status) from dbo.Trade_OrderDetail with(nolock) where status = '10') as waiting
,g.Time_zone_difference,g.CountriesOrRegions

,(select dateadd(HH,g.Time_zone_difference - 8,getdate())) as localTime


  from dbo.Trade_OrderDetail   as a with(nolock)
 
INNER JOIN dbo.Trade_Order as b with(nolock) on  a.OID = b.OID 

INNER JOIN dbo.pro_advertisement as c with(nolock) on b.AD_ID = c.ad_id 

INNER JOIN dbo.Trade_SchedulesClock as d with(nolock) on d.SC_ID = a.SC_ID

INNER JOIN dbo.uc_User as e with(nolock) on b.buy_uid = e.uid

INNER JOIN dbo.uc_User as f with(nolock) on c.user_id = f.uid

INNER JOIN dbo.st_zebraTime as g with(nolock) on b.buy_zid = g.ZID

DECLARE waiting INT
SET @waiting =(select COUNT(status) from dbo.Trade_OrderDetail with(nolock) where status = '10')

select a.freeze_logId,a.OD_ID,a.SC_ID,a.status
,b.AD_ID,b.buy_uid,b.buy_zid,b.create_time,b.oid,b.order_number,b.type
,c.ad_title,c.user_id
,d.S_DATETIME,e.nick as Buy_Nick
,f.nick as TeacheNick
,@waiting as waiting
,g.Time_zone_difference,g.CountriesOrRegions

,dateadd(HH,g.Time_zone_difference - 8,getdate()) as localTime


  from dbo.Trade_OrderDetail   as a with(nolock)
 
INNER JOIN dbo.Trade_Order as b with(nolock) on  a.OID = b.OID 

INNER JOIN dbo.pro_advertisement as c with(nolock) on b.AD_ID = c.ad_id 

INNER JOIN dbo.Trade_SchedulesClock as d with(nolock) on d.SC_ID = a.SC_ID

INNER JOIN dbo.uc_User as e with(nolock) on b.buy_uid = e.uid

INNER JOIN dbo.uc_User as f with(nolock) on c.user_id = f.uid

INNER JOIN dbo.st_zebraTime as g with(nolock) on b.buy_zid = g.ZID

------解决方案--------------------
楼主的脚本应该有点问题，
一楼已经帮忙改过来了，
脚本写法已经优化的差不多了，
接下来 考虑下索引及其他优化吧
给你个网址你看考下 ..
http://space.itpub.net/11320622/viewspace-675443

探讨


先试下

SQL code
DECLARE waiting INT
SET @waiting =(select COUNT(status) from dbo.Trade_OrderDetail with(nolock) where status = '10')

select a.freeze_logId,a.OD_ID,a.SC_ID,a.status
,b.AD_ID,b.buy_ui……