2 qq 28199467 qq_28199467 于 2016.09.23 17:56 提问

sql语句变成mysql...............................

select top 10 ,(select count() from (select * from (
select d.cardid,a.CompID,b.OrderType,b.RevokeState,b.IsResting,d.IsDelete,b.CreateTime,a.GoodsCode,a.number from
OrderDetail a left join OrderLog b on a.OrderCode=b.BillCode and a.CompID=b.CompID left join MemCard d on b.MemID=d.Id left join goods good on good.id=a.GoodsID and good.CompID=a.CompID)t
where CompID = 11187 AND OrderType = 2 AND RevokeState = 0 AND IsResting = 1 AND IsDelete = 0 AND CreateTime>='20160916053625' AND CreateTime<='20160923053625' and goodscode in (GoodsOrderHZ.GoodsCode)
)a where cardid='0000') as Fit,
(select count(*) from (

select * from (

select d.cardid,a.CompID,b.OrderType,b.RevokeState,b.IsResting,d.IsDelete,b.CreateTime,a.GoodsCode,a.number from
OrderDetail a left join OrderLog b on a.OrderCode=b.BillCode and a.CompID=b.CompID left join MemCard d on b.MemID=d.Id left join goods good on good.id=a.GoodsID and good.CompID=a.CompID)t
where CompID = 11187 AND OrderType = 2 AND RevokeState = 0 AND IsResting = 1 AND IsDelete = 0 AND CreateTime>='20160916053625' AND CreateTime<='20160923053625' and goodscode in (GoodsOrderHZ.GoodsCode)
) a where cardid<>'0000') as Members,
'' as AvgProfit from
(select * from (select GoodsID,MAX(GoodsName)GoodsName,MAX(GoodsCode)GoodsCode,SUM(Number) TotalNum ,SUM(TotalMoney)as TotalMoney,sum(profit) profit from
(select a.GoodsID,a.GoodsName,a.CompID,a.GoodsCode,a.Number,a.TotalMoney,a.profit,b.OrderType,b.RevokeState,b.IsResting,b.BillCode,b.CreateTime,b.Remark,d.CardID,d.IsDelete,d.CardName,good.price as GoodPrice from
OrderDetail a left join OrderLog b on a.OrderCode=b.BillCode and a.CompID=b.CompID left join MemCard d on b.MemID=d.Id left join goods good on good.id=a.GoodsID and good.CompID=a.CompID)o
where CompID = 11187 AND OrderType = 2 AND RevokeState = 0 AND IsResting = 1 AND IsDelete = 0 AND CreateTime>'20160916053625' AND CreateTime<='20160923053625 '
group by GoodsID )x left join (select GoodsID as GoodsStockID,SUM(StockNum)as tstocknum from GoodsStock where CompID=11187 AND ShopID in (select id from Shop where compid=11187)
group by GoodsID)y on x.GoodsID=y.GoodsStockID)as GoodsOrderHZ order by totalnum desc

1个回答

dabocaiqq
dabocaiqq   2016.09.24 23:25
Csdn user default icon
上传中...
上传图片
插入图片