sql server/sql使用select查询总结

一、常用语句

select * from table

二、常用条件/字段

1.在语句最后面加上 这一句，即可隐藏结果为0的行

 having sum( SEOutStockEntry .FQty- SEOutStockEntry .FAuxStockQty) != 0  

2.通过主键联查对应单据

–联查出 即时库存数量
select FQty,FItemID from ICInventory where ICInventory.fqty !=0 and ICInventory.FItemID =(select FItemID from t_ICItem where t_ICItem.FName=’MT6261DA’ )

三、多表联查

—待出库数量（取自发货通知单上） –FClosed=0 是单据未关闭 （发货通知单上的单据数量-出库数量= 待出库数量） 单据保存就增减库存。。。。(加上t_Stock.FName is not null 隐藏仓库为null的值）

  select  t_Stock.FName as 仓库名称,t_ICItem.FName as 物料名称 ,t_ICItem.FModel as 规格型号,sum(SEOutStockEntry.FQty-SEOutStockEntry.FAuxStockQty)  as  待出库数量
  from SEOutStock
 left join  SEOutStockEntry  on  SEOutStock.FInterID=SEOutStockEntry.FInterID
 left join t_Stock on t_stock.FItemID=SEOutStockEntry.FStockID
 left join t_ICItem on t_ICItem.FItemID=SEOutStockEntry.FItemID
 where  SEOutStock.FClosed='0'     and  t_Stock.FName is not null  
 GROUP BY t_Stock.FName ,t_ICItem.FModel,t_ICItem.FName
 having sum( SEOutStockEntry .FQty- SEOutStockEntry .FAuxStockQty) != 0  

--- 查询待出入库数量和即时库存数量
 select * from
 (  
 select A.仓库代码,A.仓库名称, D.物料代码,D.物料名称,D.最小包装,D.规格型号,A.即时库存数量,isnull(B.待入库数量,0) as 待入库数量,isnull(C.待出库数量,0) as 待出库数量    
  from
    (   --物料表
    select t_ICItem.FItemID as 物料内码, t_ICItem.FName as 物料名称, t_ICItem.FNumber as 物料代码,t_ICItem.fbatchappendqty as 最小包装 ,t_ICItem.FModel as 规格型号
 from t_ICItem 
    )
 as D 
 left join
    (
 ---即时库存
    select t_ICItem.FItemID as 物料内码, t_Stock.FItemID as 仓库内码, t_stock.fname as 仓库名称,  FQty as 即时库存数量, t_Stock.FNumber 仓库代码
 from ICInventory 
 left join t_stock on ICInventory. FStockID=.t_stock.FItemID  
 left join t_ICItem on t_ICItem.FItemID=ICInventory.FItemID
    )
 as A   on A.物料内码=D.物料内码 
 left join
    (
 --待入库数量
 select t_Stock.FItemID as 仓库内码 ,t_ICItem.FItemID as 物料内码,sum(POInStockEntry.FQty-POInStockEntry.FAuxConCommitQty)  as  待入库数量
 from poinStock
 inner join  POInStockEntry  on  POInStock.FInterID=POInStockEntry.FInterID
 inner join t_Stock on t_stock.FItemID=POInStockEntry.FStockID
 inner join t_ICItem on t_ICItem.FItemID=POInStockEntry.FItemID
 where  POInStock.FClosed=0 and  POInStock.FStatus=1    and t_Stock.FName is not null 
 GROUP BY t_Stock.FItemID ,t_ICItem.FModel,t_ICItem.FItemID,t_ICItem.FName, t_ICItem.FNumber 
      ) 
 as B on D.物料内码=B.物料内码 and A.仓库内码=B.仓库内码
 left join 
 ( --待出库数量
 select  t_Stock.FItemID as 仓库内码,t_ICItem.FItemID as 物料内码 ,sum(SEOutStockEntry.FQty-SEOutStockEntry.FAuxStockQty)  as  待出库数量
  from SEOutStock
 inner join  SEOutStockEntry  on  SEOutStock.FInterID=SEOutStockEntry.FInterID
 left join t_Stock on t_stock.FItemID=SEOutStockEntry.FStockID
 left join t_ICItem on t_ICItem.FItemID=SEOutStockEntry.FItemID
 where  SEOutStock.FClosed='0'   and t_Stock.FName is not null  
 GROUP BY t_Stock.FItemID ,t_ICItem.FModel,t_ICItem.FName,t_ICItem.FItemID
 ) 
 as C
 on A.仓库内码=C.仓库内码 and D.物料内码=C.物料内码  
 GROUP BY A.仓库代码,A.仓库名称, D.物料代码,D.物料名称,D.最小包装,D.规格型号,A.即时库存数量,isnull(B.待入库数量,0) ,isnull(C.待出库数量,0) 
 )  as  table111 
 where  table111.即时库存数量 <>0  or table111.待出库数量<>0 or table111.待入库数量 <>0   
 GROUP BY table111.仓库代码,table111.即时库存数量,table111.仓库名称, table111.物料代码,table111.物料名称,table111.最小包装,table111.规格型号,table111.即时库存数量,table111.待入库数量,table111.待出库数量
 order by  table111.仓库代码,table111.物料代码