|
数据库中表的设计往往与用户需要看到的结果存在差异， 用户为了对比分析数据往往需要将不同的行信息放在同一行的不同列进行比较（该比较较为直观），所以就遇到了将将一组行转为列的问题。 1.物理表 create table ISSUESALESORDER
(
id number(16) not null,
stanid number(8),
ruleid number(8),
realtypeid number(8),
districtorganizeno number(8),
organizeno number(8),
designusage varchar2(40),
num number(18,2),
buildarea number(18,2),
amount number(18,2),
commitdatetime date,
issue number(1) default 0 not null,
isvalid number(1) default -1 not null
); 2.统计结果sql: select (select fullname from orgorganize where organizeno=tb1.districtorganizeno) f0sum(num) f1,sum(buildarea) f2,sum(amount) f3
from issuesalesorder
where isvalid=-1 and to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' group by districtorganizeno,ruleid,issue 这样同一个districtorganizeno下边有一组行（这组行中ruleid、issue是不同的），为了方便比较我们需要将这一组行转换为一行，即将group by districtorganizeno,ruleid,issue 改为 group by districtorganizeno。（因业务的不同、用户关注的不同ruleid、issue是有取舍的，所以我这里提供的不是一个通用的模式，而是一种普通方法的示例）。 3.构造sql: 我的需求构造的原始sql如下： select (select fullname from orgorganize where organizeno=tb1.districtorganizeno) f0,f1,f2,f3,f4||'/'||f7 f4,f5||'/'||f8 f5,f6||'/'||f9 f6 from
(select districtorganizeno,sum(num) f1,sum(buildarea) f2,sum(amount) f3 from issuesalesorder where ruleid=164601 and isvalid=-1 to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' group by districtorganizeno) tb1,
(select districtorganizeno,sum(num) f4,sum(buildarea) f5,sum(amount) f6 from issuesalesorder where ruleid=164802 and isvalid=-1 to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' and issue=-1 group by districtorganizeno) tb2,
(select districtorganizeno,sum(num) f7,sum(buildarea) f8,sum(amount) f9 from issuesalesorder where ruleid=164802 and isvalid=-1 to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' group by districtorganizeno) tb3
where tb1.districtorganizeno=tb3.districtorganizeno and tb2.districtorganizeno=tb3.districtorganizeno 这只是一个理想的sql,实际上并非如此，from 下有3个子表tb1、tb2、tb3 ，如果这3个集合是完全重合的上述sql就对了，否则上述sqlj将丢失3个集合中不重合的部分，很显然，为了方便查看而导致数据丢失是不对的，所以我们需要分析这3个集合的关系。 我对（我的）需求分析（分析业务系统的逻辑）后等到了这样的关系tb1、tb3相交的部分是tb2,且tb1、tb3还存在各自不相交的部分，我们现在要做的是取出这3个集合的全集。 有人会说 i=tb1 U tb3, 错，这样tb2的属性（f4\f5\f6）就丢失了，我是用i=tb1 U (tb2 U tb3) 来处理的， 因为tb2被tb3包含，所以tb2 U tb3 是tb2与tb3 的 一个左链接 select tb3.districtorganizeno,f4||'/'||f7 f4,f5||'/'||f8 f5,f6||'/'||f9 f6 from
(select nvl(districtorganizeno,-1) districtorganizeno,sum(num) f4,sum(buildarea) f5,sum(amount) f6 from issuesalesorder where ruleid=164802 and isvalid=-1 and to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' and issue=-1 group by districtorganizeno) tb2,
(select nvl(districtorganizeno,-1) districtorganizeno,sum(num) f7,sum(buildarea) f8,sum(amount) f9 from issuesalesorder where ruleid=164802 and isvalid=-1 and to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' group by districtorganizeno) tb3
where tb2.districtorganizeno(+)=tb3.districtorganizeno 又因为tb1、tb3相交且存在各自不相交的部分，所以tb1 U (tb2 U tb3) 是tb1与(tb2 U tb3)的一个全连接， oracle中没有全连接的操作符，只能用 “左连接 union 右连接”，为什么不用 union all 呢？因为集合存在相交的部分，union all必然使相交的部分重复。 select (select fullname from orgorganize where organizeno=tb1.districtorganizeno) f0,f1,f2,f3,f4, f5, f6 from
(select nvl(districtorganizeno,-1) districtorganizeno,sum(num) f1,sum(buildarea) f2,sum(amount) f3 from issuesalesorder where ruleid=164601 and isvalid=-1 and to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' group by districtorganizeno) tb1,
(select tb3.districtorganizeno,f4||'/'||f7 f4,f5||'/'||f8 f5,f6||'/'||f9 f6 from
(select nvl(districtorganizeno,-1) districtorganizeno,sum(num) f4,sum(buildarea) f5,sum(amount) f6 from issuesalesorder where ruleid=164802 and isvalid=-1 and to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' and issue=-1 group by districtorganizeno) tb2,
(select nvl(districtorganizeno,-1) districtorganizeno,sum(num) f7,sum(buildarea) f8,sum(amount) f9 from issuesalesorder where ruleid=164802 and isvalid=-1 and to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' group by districtorganizeno) tb3
where tb2.districtorganizeno(+)=tb3.districtorganizeno) tb2
where tb1.districtorganizeno=tb2.districtorganizeno(+)
union all
select (select fullname from orgorganize where organizeno=tb2.districtorganizeno) f0,f1,f2,f3,f4, f5, f6 from
(select nvl(districtorganizeno,-1) districtorganizeno,sum(num) f1,sum(buildarea) f2,sum(amount) f3 from issuesalesorder where ruleid=164601 and isvalid=-1 and to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' group by districtorganizeno) tb1,
(select tb3.districtorganizeno,f4||'/'||f7 f4,f5||'/'||f8 f5,f6||'/'||f9 f6 from
(select nvl(districtorganizeno,-1) districtorganizeno,sum(num) f4,sum(buildarea) f5,sum(amount) f6 from issuesalesorder where ruleid=164802 and isvalid=-1 and to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' and issue=-1 group by districtorganizeno) tb2,
(select nvl(districtorganizeno,-1) districtorganizeno,sum(num) f7,sum(buildarea) f8,sum(amount) f9 from issuesalesorder where ruleid=164802 and isvalid=-1 and to_char(commitdatetime,'yyyy-mm-dd') between '@startdatetime' and '@enddatetime' group by districtorganizeno) tb3
where tb2.districtorganizeno(+)=tb3.districtorganizeno) tb2
where tb1.districtorganizeno(+)=tb2.districtorganizeno 4.如何区分“左链接”和 “右链接” 其实真的没必要区分，它们都是外链接而已。数据库原理及sql标准及人们更容易理解为“(+)”在“=”左边叫“左链接”，“(+)”在“=”右边叫“右链接”。不幸的是，oracle对此的理解刚好相反，“(+)”在“=”左边叫“右链接”，“(+)”在“=”右边叫“左链接”。不过没关系，tb2.districtorganizeno(+)=tb3.districtorganizeno 叫“右链接”，哪这样呢tb3.districtorganizeno=tb2.districtorganizeno(+)？这有区别吗？不过换了个前后顺序而已，需要理解的是 tb2.districtorganizeno的集合包含在tb3.districtorganizeno中。
|
