Question

请教一段SQL语句的含义

select a.splanno,a.addid,a.itemno,b.itemname,b.descript,c.msname,a.planqty,a.plansum,a.rcvqty,a.rcvsum,a.planqty - a.rcvqty celqty,
a.plansum - a.rcvsum celsum from
(select a.splanno,a.addid,a.itemno,sum(isnull(a.planqty,0)) planqty,sum(isnull(a.plansum,0)) plansum,sum(isnull(a.rcvqty,0)) rcvqty,
sum(isnull(a.untaxrcvsum,0)) rcvsum from
(select a.splanno,case when a.addid = 1 then 1 when a.addid > 1 then 2 end addid,a.itemno,a.planqty,a.plansum,a.spurno,a.lineid,b.rcvqty,
b.untaxrcvsum from
(select a.splanno,a.addid,a.itemno,a.planqty,a.plansum,b.spurno,b.lineid from
(select splanno,addid,itemno,planqty,plansum plansum from purplandet where splanno in (select distinct splanno from purplanmst where cyc_code='0004')
) a
left outer join purdec b
on a.splanno=b.refsysno and a.addid=b.refaddsysno and a.itemno=b.itemno) a
left outer join rcvdet b
on a.spurno=b.purno and a.lineid=b.refrow and a.itemno=b.itemno) a
group by a.splanno,a.addid,a.itemno) a,itemdata b,msunit c
where a.itemno=b.itemno and b.msunit=c.msunit
order by a.splanno,a.addid,a.itemno
请教哪位有耐心的老师能详细的分析下此语句的含义和不常用语法的用法

Answer 1

select
a.splanno,a.addid,a.itemno,
b.itemname,b.descript,
c.msname,
a.planqty,a.plansum,a.rcvqty,a.rcvsum,
a.planqty - a.rcvqty celqty,
a.plansum - a.rcvsum celsum
from
(
select
a.splanno,a.addid,a.itemno,
sum(isnull(a.planqty,0)) planqty,sum(isnull(a.plansum,0)) plansum,sum(isnull(a.rcvqty,0)) rcvqty,
sum(isnull(a.untaxrcvsum,0)) rcvsum
from
(select
a.splanno,case when a.addid = 1 then 1 when a.addid > 1 then 2 end addid,
a.itemno,a.planqty,a.plansum,a.spurno,a.lineid,b.rcvqty,
b.untaxrcvsum from
(select a.splanno,a.addid,a.itemno,a.planqty,a.plansum,b.spurno,b.lineid from
(select splanno,addid,itemno,planqty,plansum plansum from purplandet
where splanno in (select distinct splanno from purplanmst where cyc_code='0004')) a
left outer join
purdec b
on a.splanno=b.refsysno and a.addid=b.refaddsysno and a.itemno=b.itemno
) a
left outer join
rcvdet b
on a.spurno=b.purno and a.lineid=b.refrow and a.itemno=b.itemno
) a
group by a.splanno,a.addid,a.itemno
) a,
itemdata b,msunit c
where a.itemno=b.itemno and b.msunit=c.msunit
order by a.splanno,a.addid,a.itemno

1.先对SQl语句进行整理提高可读性
2.针对几个简单的概念和简单函数先了解
(a).别名（表的别名 以及栏位的别名）：
上述例子中 如itemdata b 即定义表itemdata 为名称b 这样在第一个select中b.itemname即是查询的为itemdata.itemname
a.planqty - a.rcvqty celqty就是对前面的计算结果 以celqty栏位名称作为显示
(b).isnull
如果第一个参数值为空，默认显示第二个栏位值
(c).sum
栏位加总
(d).left join
表关联，配合on条件
(e).group by
分组栏位
(f).case when
SQL中的if else
3.SQL解析（因为不知道你的表具体含义只能通过SQL单纯的看）
(a). 一层一层来看的话，最外层查询的含义是
查询第一层子查询中的splanno,addid,itemno,
planqty,plansum,arcvqty,rcvsum,planqty - rcvqty，
plansum - rcvsum
以及itemdata 中的itemname,descript,和msunit表中的msname,

a表是通过itemno和itemdata 表关联，itemdata 表再通过msunit和msunit表关联
最后按照a表中splanno,addid,itemno进行升序排序
(b).from里层主要是查询最外层所定义的a表
(b1).直接看最里层的SQL查询
select a.splanno,a.addid,a.itemno,a.planqty,a.plansum,b.spurno,b.lineid from
(select splanno,addid,itemno,planqty,plansum plansum from purplandet
where splanno in (select distinct splanno from purplanmst where cyc_code='0004')) a
left outer join
purdec b
on a.splanno=b.refsysno and a.addid=b.refaddsysno and a.itemno=b.itemno) a

查询purplandet 中splanno 对应的cyc_code为0004的相关资料并关联purdec 查询对应栏位
(b2).以上查询出后会在关联rcvdet 查出需要栏位
(b3).通过对a.splanno,a.addid,a.itemno的分组，统计对应栏位的加总信息

不知道这样说明是否OK? 另外 建议尽量避免子查询 会影响效能
~~排版有点乱 凑合着看吧

Answer 2

联表查询啊，好多表啊