mimrc7993的专栏

优化sql的数据。 select * from (select PI.ID AS ProductID,PI.SynapsID,PI.NameCode,PI.Name_cn,PI.Name_en,PP.PicName,PI.BarCode, PI.NewLevel,PI.`Status`,PI.HighPrice,CPM.SalesPlaceID, psi.WIID,psi.stockNum,psi.OccupiedStockNum,psi.AirStockNum, GROUP_CONCAT(CONCAT('\r\n#',tpm.ID,' ',tpm.Title,' $',if(tpm.ReservePrice>0,tpm.ReservePrice,tpm.BuyNowPrice),' ',tmi.`Name`)) Auctions, GROUP_CONCAT(CONCAT('\r\n',tmi.`Name`,' ',tml.OnLineNum)) Listings, GROUP_CONCAT(CONCAT('\r\n',tmi.`Name`,' ',tml.OfferNum)) Offers FROM ProductInfo PI LEFT JOIN (select a.ProductID,GROUP_CONCAT(CONCAT('\r\n',b.ShowName) ORDER BY b.ShowName) as WIID, GROUP_CONCAT(CONCAT('\r\n',a.StrockNum) ORDER BY b.ShowName) as stockNum, GROUP_CONCAT(CONCAT('\r\n',a.OccupiedStockNum) ORDER BY b.ShowName) as OccupiedStockNum, GROUP_CONCAT(CONCAT('\r\n',a.AirStockNum) ORDER BY b.ShowName) as AirStockNum FROM ProductStockInfo a LEFT JOIN WareHouseAreaInfo b ON b.WIID = a.WIID GROUP BY a.ProductID ) psi ON psi.ProductID = PI.ID left JOIN (SELECT ProductID,PicName FROM ProductPic where Sort = 1) as PP ON PI.ID = PP.ProductID left join (select ProductID,SalesPlaceID from Channel_Product_Mapping WHERE SalesPlaceID = 2 GROUP BY ProductID) CPM on PI.ID = CPM.ProductID LEFT JOIN TM_Product_Mapping tpm ON PI.ID = tpm.ProductID and tpm.Status <> 2 LEFT JOIN TM_Account_Info tmi ON tpm.AccountID = tmi.ID LEFT JOIN (select sum(if(`Status` =1,1,0))as OnLineNum,sum(if(`Status` =3,1,0))as OfferNum, TPM from TM_Listing GROUP BY TPM) AS tml ON tpm.ID = tml.TPM where 1 = 1 GROUP BY PI.ID ORDER BY PI.ID DESC) as newtb LIMIT 0,25;