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