常用SQL语句 工作两年总结 个个经典 不断更新.docVIP

//相同日期，相同商品的sum查询 SELECT counts.GoodsID, tbg.Name As Name, counts.OutStorageDate As Time, counts.abc FROM (select sum(Quantity) as abc,GoodsID,OutStorageDate from tbOhterOutStorage sto ,tbOtherOutStorageDetail std where(sto.OutStorageNo = std.OutStorageNo) group by OutStorageDate,GoodsID) AS counts LEFT JOIN tbGoods tbg ON tbg.ID= counts.GoodsID //相同日期，相同商品的sum查询 select t.ID,tbg.Name As Name,t.Time,t.Quantity from tbGoods tbg left join (select std.GoodsId As ID,sum(Quantity) as Quantity,sto.InStorageDate as Time from tbInStorageDetail std left join tbInStorage sto on sto.InStorageNo=std.InStorageNo group by InStorageDate,GoodsId) as t on t.ID=tbg.ID order by t.Time desc //库存sql 增强版 select vehcName,vehcModel, ISNULL(a.id1,0) as VehicleID , ISNULL(b.snum2,0) as InQuantity, ISNULL(a.snum1,0) as OutQuantity, ISNULL((b.snum2-ISNULL(a.snum1,0)),0) as Quantity from Vehicle FULL JOIN (select exdeGoodsID as id1,sum(exdeQuantity) as snum1 from ExportDetail group by exdeGoodsID)as a ON Vehicle.ID=a.id1 FULL JOIN (select imdeGoodsId as id2,sum(imdeQuantity) as snum2 from importDetail group by imdeGoodsID)as b ON a.id1=b.id2 //经销商 注：select saleVehID, count(*) as snum1 from SaleRecord where saleSellerID=1001 group by saleVehID 重点 select vehcName,vehcModel, ISNULL(a.saleVehID,0) as VehicleID , ISNULL(b.snum2,0) as InQuantity, ISNULL(a.snum1,0) as OutQuantity, ISNULL((b.snum2-ISNULL(a.snum1,0)),0) as Quantity from Vehicle LEFT JOIN (select saleVehID, count(*) as snum1 from SaleRecord where saleSellerID=1001 group by saleVehID)as a ON Vehicle.ID=a.saleVehID LEFT JOIN (select exdeGoodsID as id2,sum(exdeQuantity) as snum2 from ExportDetail where exdeSupplierID=1001 group by exdeGoodsID)as b ON a.saleVehID=b.id2 order by Quantity asc 入库-出库=库存（供货商的算法） select exdeGoodsID as 编号,innum as 入库,outnum as 出库,a.innum-b.outnum as 库存 from (select sum(imdeQuantity) as innum,imdeGoodsID from ImportDetail Group By imdeGoodsID) as a LEFT JOIN