日期:2014-05-17 浏览次数:20654 次
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
只是格式化一下: 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
------解决方案--------------------