- 博客(1)
- 资源 (1)
- 收藏
- 关注
原创 全排列,递归。
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ConsoleApplication1 { class Program { static void Main(string[] args) {
2013-03-21 17:26:04 449
优化sql的测试数据
优化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;
2014-06-24
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人