sql代码书写规范 及子查询分析

--sql书写规范，注意行的缩进

select f.goodscode

, f.goodsname
, f.goodsspec
, n.orgname as ownername
, h.stornum
, m.whname
, f.manufacturer
, f.place
, g.dates 
#if (dbtype == "Microsoft SQL Server"){ 
,(case when g.dates is not null then datediff(dd,g.dates,getdate()) else 999 end)  as zxday//sql server中计算日期差
#}else if (dbtype == "Oracle" || dbtype.indexof("db2") >= 0){ 
,(case when g.dates is not null then trunc(sysdate-to_date(g.dates,'yyyy-mm-dd')) else 999 end) as zxday //oracle计算日期差
#}else if (dbtype == "MySQL"){
,(case when g.dates is not null then datediff(dd,g.dates,getdate()) else 999 end)  as zxday
#}
from
goodsdoc f
left join (
select c.goodsid
 , e.dates
from
goodacctdt c
join ( select max(a.serialnum) as serialnum
, a.goodsid
  from
  goodacctdt a
  join goodacctmt b
  on a.acctpageid = b.acctpageid and a.entid = b.entid
  where
  b.ruleid in ('4szcn61oa8b0tz79', '0pfridb711yiiomu')//查询最大编号，根据goodsid分组。
  group by

  a.goodsid) d //把这个子查询里面的的数据当作一个表，然后用明细表关联主表，然后把它当作一个表用join连接起来，子查询的作用是确定过滤后的最大

//编号，用过滤后的明细管理汇总，取得汇总中的日期，这样就求的了每种商品分组收的最大编号的记录和对象汇总表中的日期字段显示。

on c.serialnum = d.serialnum and c.goodsid = d.goodsid
join goodacctmt e

on c.acctpageid = e.acctpageid and c.entid = e.entid) g//从goodsdoc中取得所有商品，然后把g里面的作为一个表来管理。用left管理，如果有如果能关联出来

//则显示日期，如果没有则显示空。
on f.goodsid = g.goodsid and f.entid = f.entid
join storbalance h//取库存
on f.goodsid = h.goodsid and f.entid = h.entid
join storehouse m
on h.whid = m.whid and h.entid = m.entid
join orgdoc n
on h.ownerid = n.orgid and h.entid = n.entid

on f.goodsid = g.goodsid and f.entid = f.entid
join storbalance h//取库存
on f.goodsid = h.goodsid and f.entid = h.entid
join storehouse m
on h.whid = m.whid and h.entid = m.entid
join orgdoc n
on h.ownerid = n.orgid and h.entid = n.entid
where
1 = 1
and h.whid = :whid
#if(typeof goodsid != "undefined") {//js中给数据库中传值
#if(goodsid != ""){
#__sql += " and f.goodsid = '" + goodsid +"'"
#}
#}
order by
zxday,f.goodscode