- SQL code
select top 10 Pid,(select top 1 packageName from [File] where [File].pid=Product.pid) as packagename,dappitemname,pName,potherName,img,(select top 1 version from [File] where [File].pid=Product.pid) as version,Itemid,product.OrderNum,pv,downcount,(select count(commid) as commcount from comm where pid=product.pid) as commcoun,(select top 1 xingji from xingji where pid=product.pid group by xingji order by count(xjid) desc) as xingji from product left join dapp_item on dapp_item.ditemid=product.ditemid where ((cid=37 or pid in (851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838)) and cid=37) and cstate=0 and (select top 1 COUNT(fid) from [File] where Pid=Product.Pid and (Fenbianlv='0' or Fenbianlv=',0,' or Fenbianlv like '%,0,%') and [online]=0 and ((ms=0 or ms=2) or (ms=1 and tid='1001') or (ms=1 and tid='0')))>0 order by CASE when(charindex(','+ltrim(pid)+',',',851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838,'))=0 then pid+100 else charindex(','+ltrim(pid)+',',',851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838,') end 这条语句为什么有的时候会造成死锁？
------解决方案--------------------
不要用太多的子查询，会引起性能问题，把该语句用“数据顾问”运行一下，会有建议的。
------解决方案--------------------
先把子查询改掉再说。。
------解决方案--------------------
SQL 写的太复杂了
((cid=37 or pid in (851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838)) and cid=37)
像这个 不是37 的 永远是返回false
先优化下SQL看看自己要什么吧
------解决方案--------------------
- SQL code
只是格式化一下：select top 10 Pid ,(select top 1 packageName from [File] where [File].pid = Product.pid) as packagename ,dappitemname ,pName ,potherName ,img ,(select top 1 version from [File] where [File].pid = Product.pid) as version ,Itemid ,product.OrderNum ,pv ,downcount ,(select count(commid) as commcount from comm where pid = product.pid) as commcoun ,(select top 1 xingji from xingji where pid = product.pid group by xingji order by count(xjid) desc) as xingjifrom product left join dapp_item on dapp_item.ditemid = product.ditemidwhere ((cid = 37 or pid in (851,402,826,968,849,830,702,964,31423,841, 843,841,3107,1434,2781,833,1222,3087,3656, 845,847,1203,1210,3107,838)) and cid = 37)and cstate = 0and (select top 1 COUNT(fid) from [File] where Pid = Product.Pid and (Fenbianlv = '0' or Fenbianlv = ',0,' or Fenbianlv like '%,0,%') and [online] = 0 and ((ms = 0 or ms = 2) or (ms = 1 and tid = '1001') or (ms = 1 and tid = '0'))) > 0order by CASE when (charindex(',' + ltrim(pid) + ',',',851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838,')) = 0 then pid + 100 else charindex(',' + ltrim(pid) + ',',',851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838,') end
------解决方案--------------------