资源描述:
《常用sql语句 工作两年总结 个个经典 不断更新》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、//相同日期,相同商品的sum查询SELECTcounts.GoodsID,tbg.NameAsName,counts.OutStorageDateAsTime,counts.abcFROM(selectsum(Quantity)asabc,GoodsID,OutStorageDatefromtbOhterOutStoragesto,tbOtherOutStorageDetailstdwhere(sto.OutStorageNo=std.OutStorageNo)groupbyOutStorageDate,GoodsID)AScountsLEFTJOINtbGood
2、stbgONtbg.ID=counts.GoodsID//相同日期,相同商品的sum查询selectt.ID,tbg.NameAsName,t.Time,t.QuantityfromtbGoodstbgleftjoin(selectstd.GoodsIdAsID,sum(Quantity)asQuantity,sto.InStorageDateasTimefromtbInStorageDetailstdleftjointbInStoragestoonsto.InStorageNo=std.InStorageNogroupbyInStorageDate,GoodsId
3、)astont.ID=tbg.IDorderbyt.Timedesc//库存sql增强版selectvehcName,vehcModel,ISNULL(a.id1,0)asVehicleID,ISNULL(b.snum2,0)asInQuantity,ISNULL(a.snum1,0)asOutQuantity,ISNULL((b.snum2-ISNULL(a.snum1,0)),0)asQuantityfromVehicleFULLJOIN(selectexdeGoodsIDasid1,sum(exdeQuantity)assnum1fromExportDetai
4、lgroupbyexdeGoodsID)asaONVehicle.ID=a.id1FULLJOIN(selectimdeGoodsIdasid2,sum(imdeQuantity)assnum2fromimportDetailgroupbyimdeGoodsID)asbONa.id1=b.id2//经销商注:selectsaleVehID,count(*)assnum1fromSaleRecordwheresaleSellerID=1001groupbysaleVehID重点selectvehcName,vehcModel,ISNULL(a.saleVehID,0)
5、asVehicleID,ISNULL(b.snum2,0)asInQuantity,ISNULL(a.snum1,0)asOutQuantity,ISNULL((b.snum2-ISNULL(a.snum1,0)),0)asQuantityfromVehicleLEFTJOIN(selectsaleVehID,count(*)assnum1fromSaleRecordwheresaleSellerID=1001groupbysaleVehID)asaONVehicle.ID=a.saleVehIDLEFTJOIN(selectexdeGoodsIDasid2,sum
6、(exdeQuantity)assnum2fromExportDetailwhereexdeSupplierID=1001groupbyexdeGoodsID)asbONa.saleVehID=b.id2orderbyQuantityasc入库-出库=库存(供货商的算法)selectexdeGoodsIDas编号,innumas入库,outnumas出库,a.innum-b.outnumas库存from(selectsum(imdeQuantity)asinnum,imdeGoodsIDfromImportDetailGroupByimdeGoodsID)asaLE
7、FTJOIN(selectsum(exdeQuantity)asoutnum,exdeGoodsIDfromExportDetailGroupByexdeGoodsID)asbONimdeGoodsID=exdeGoodsID入库-出库=库存(门店的算法)selecta.imdeGoodsIDas编号,a.innumas入库,b.outnumas出库,a.innum-b.outnumas库存from(selectsum(imdeQuantity)asinnum,imdeGoodsIDfromImportDetailGroupByimdeGoodsID)asaLE