在oralce中求一sql,有关行列转换问题

你们都是坏人 2017-01-12 10:51:56
已有表 contractitem (主表)


insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 4, 'MD01800', '9001100090');

insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 5, 'MD02000', '3926909090');

insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 7, 'MD02210', '8443999090');

insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 10, 'MD03400', '8532229000');

insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 11, 'MD03500', '8533219000');

insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 13, 'MD03700', '7108130000');

insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 14, 'MD03800', '8542390000');

insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 15, 'MD03900', '8443999090');

insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 18, 'MD04300', '8443999090');

insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 19, 'MD04400', '8544110000');

insert into contractitem (CONTRACTID, SEQUENCENO, ITEMNO, CUSTOMCODE)

values ('D1', 41, 'ZS00300', '4819100000');

明细表 contractitemsubitem


insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'OCC0100', 'MD01800', 2.000000, 2.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'OCC0100', 'MD02000', 2.000000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0100', 'MD04300', 1.000000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0100', 'ZS00300', 0.002000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0200', 'MD03500', 1.000000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0200', 'MD03800', 5.000000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0300', 'MD03400', 2.000000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0300', 'MD03900', 3.000000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0400', 'MD02210', 1.000000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0400', 'MD03400', 1.000000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0400', 'MD03700', 0.540000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0500', 'MD03400', 2.000000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0500', 'MD04400', 3.000000, 0.000000);

insert into contractitemsubitem (CONTRACTID, ITEMNO, SUBITEMNO, CONSUMPTIONRATE, WASTERATE)

values ('D1', 'TPH0500', 'ZS00300', 0.002000, 0.000000);

主表A，明细表B， 通过 A.CONTRACTID=B.CONTRACTID and A.ITEMNO=B.SUBITEMNO 关联。
现要求得到以下数据，该如何写sql，(以表B 中的 ITEMNO 进行分组，每5个一组，数据横向排列 )
通过主表A，明细表B 求一个sql 得到以下数据！


insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 4, 'MD01800', 'OCC0100', 2.000000, 2.000000, 'TPH0100', 0.000000, 0.000000, 'TPH0200', 0.000000, 0.000000, 'TPH0300', 0.000000, 0.000000, 'TPH0400', 0.000000, 0.000000, 1);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 5, 'MD02000', 'OCC0100', 2.000000, 0.000000, 'TPH0100', 0.000000, 0.000000, 'TPH0200', 0.000000, 0.000000, 'TPH0300', 0.000000, 0.000000, 'TPH0400', 0.000000, 0.000000, 1);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 7, 'MD02210', 'OCC0100', 0.000000, 0.000000, 'TPH0100', 0.000000, 0.000000, 'TPH0200', 0.000000, 0.000000, 'TPH0300', 0.000000, 0.000000, 'TPH0400', 1.000000, 0.000000, 1);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 10, 'MD03400', 'OCC0100', 0.000000, 0.000000, 'TPH0100', 0.000000, 0.000000, 'TPH0200', 0.000000, 0.000000, 'TPH0300', 2.000000, 0.000000, 'TPH0400', 1.000000, 0.000000, 1);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 11, 'MD03500', 'OCC0100', 0.000000, 0.000000, 'TPH0100', 0.000000, 0.000000, 'TPH0200', 1.000000, 0.000000, 'TPH0300', 0.000000, 0.000000, 'TPH0400', 0.000000, 0.000000, 1);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 13, 'MD03700', 'OCC0100', 0.000000, 0.000000, 'TPH0100', 0.000000, 0.000000, 'TPH0200', 0.000000, 0.000000, 'TPH0300', 0.000000, 0.000000, 'TPH0400', 0.540000, 0.000000, 1);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 14, 'MD03800', 'OCC0100', 0.000000, 0.000000, 'TPH0100', 0.000000, 0.000000, 'TPH0200', 5.000000, 0.000000, 'TPH0300', 0.000000, 0.000000, 'TPH0400', 0.000000, 0.000000, 1);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 15, 'MD03900', 'OCC0100', 0.000000, 0.000000, 'TPH0100', 0.000000, 0.000000, 'TPH0200', 0.000000, 0.000000, 'TPH0300', 3.000000, 0.000000, 'TPH0400', 0.000000, 0.000000, 1);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 18, 'MD04300', 'OCC0100', 0.000000, 0.000000, 'TPH0100', 1.000000, 0.000000, 'TPH0200', 0.000000, 0.000000, 'TPH0300', 0.000000, 0.000000, 'TPH0400', 0.000000, 0.000000, 1);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 41, 'ZS00300', 'OCC0100', 0.000000, 0.000000, 'TPH0100', 0.002000, 0.000000, 'TPH0200', 0.000000, 0.000000, 'TPH0300', 0.000000, 0.000000, 'TPH0400', 0.000000, 0.000000, 1);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 10, 'MD03400', 'TPH0500', 2.000000, 0.000000, '', 0.000000, 0.000000, '', 0.000000, 0.000000, '', 0.000000, 0.000000, '', 0.000000, 0.000000, 2);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 19, 'MD04400', 'TPH0500', 3.000000, 0.000000, '', 0.000000, 0.000000, '', 0.000000, 0.000000, '', 0.000000, 0.000000, '', 0.000000, 0.000000, 2);

insert into viewconsumptapp (CONTRACTID, SEQUENCENO, SUBITEMNO, ITEMNO1, CONSUMPTIONRATE1, WASTERATE1, ITEMNO2, CONSUMPTIONRATE2, WASTERATE2, ITEMNO3, CONSUMPTIONRATE3, WASTERATE3, ITEMNO4, CONSUMPTIONRATE4, WASTERATE4, ITEMNO5, CONSUMPTIONRATE5, WASTERATE5, GROUPNO)

values ('D1', 41, 'ZS00300', 'TPH0500', 0.002000, 0.000000, '', 0.000000, 0.000000, '', 0.000000, 0.000000, '', 0.000000, 0.000000, '', 0.000000, 0.000000, 2);

...全文
687 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
qq_21880229 2017-09-27
  • 打赏
  • 举报
 回复
学到了，每天都来学习一下
你们都是坏人 2017-01-13
  • 打赏
  • 举报
 回复
感谢 u012557814 的回复，好像有重复的数据！！
落落叶叶无声 2017-01-12
  • 打赏
  • 举报
 回复
有点长，未考虑怎么优化 

WITH OCC0100_TPH0400 AS (SELECT C.CONTRACTID,
       C.SEQUENCENO,
       C.SUBITEMNO,
       'OCC0100' ITEMNO1,
       SUM(DECODE(C.ITEMNO, 'OCC0100', CONSUMPTIONRATE, 0)) CONSUMPTIONRATE1,
       SUM(DECODE(C.ITEMNO, 'OCC0100', WASTERATE, 0)) WASTERATE1,
       'TPH0100' ITEMNO2,
       SUM(DECODE(C.ITEMNO, 'TPH0100', CONSUMPTIONRATE, 0)) CONSUMPTIONRATE2,
       SUM(DECODE(C.ITEMNO, 'TPH0100', WASTERATE, 0)) WASTERATE2,
       'TPH0200' ITEMNO3,
       SUM(DECODE(C.ITEMNO, 'TPH0200', CONSUMPTIONRATE, 0)) CONSUMPTIONRATE3,
       SUM(DECODE(C.ITEMNO, 'TPH0200', WASTERATE, 0)) WASTERATE3,
       'TPH0300' ITEMNO4,
       SUM(DECODE(C.ITEMNO, 'TPH0300', CONSUMPTIONRATE, 0)) CONSUMPTIONRATE4,
       SUM(DECODE(C.ITEMNO, 'TPH0300', WASTERATE, 0)) WASTERATE4,
       'TPH0400' ITEMNO5,
       SUM(DECODE(C.ITEMNO, 'TPH0400', CONSUMPTIONRATE, 0)) CONSUMPTIONRATE5,
       SUM(DECODE(C.ITEMNO, 'TPH0400', WASTERATE, 0)) WASTERATE5,
       1 GROUPNO
  FROM (SELECT A.SEQUENCENO,
               A.CUSTOMCODE,
               B.CONTRACTID,
               B.ITEMNO,
               B.SUBITEMNO,
               B.CONSUMPTIONRATE,
               B.WASTERATE
          FROM CONTRACTITEM A, CONTRACTITEMSUBITEM B
         WHERE A.CONTRACTID = B.CONTRACTID
           AND A.ITEMNO = B.SUBITEMNO
           AND B.ITEMNO != 'TPH0500') C
 GROUP BY C.ITEMNO, C.SEQUENCENO, C.CONTRACTID, C.SUBITEMNO
 ORDER BY C.SEQUENCENO),
 TPH0500 AS (
 SELECT C.CONTRACTID,
       C.SEQUENCENO,
       C.SUBITEMNO,
       'TPH0500' ITEMNO1,
       SUM(DECODE(C.ITEMNO, 'TPH0500', CONSUMPTIONRATE, NULL)) CONSUMPTIONRATE1,
       SUM(DECODE(C.ITEMNO, 'TPH0500', WASTERATE, NULL)) WASTERATE1,
       'TPH0100' ITEMNO2,
       SUM(DECODE(C.ITEMNO, 'TPH0100', CONSUMPTIONRATE, NULL)) CONSUMPTIONRATE2,
       SUM(DECODE(C.ITEMNO, 'TPH0100', WASTERATE, NULL)) WASTERATE2,
       'TPH0200' ITEMNO3,
       SUM(DECODE(C.ITEMNO, 'TPH0200', CONSUMPTIONRATE, NULL)) CONSUMPTIONRATE3,
       SUM(DECODE(C.ITEMNO, 'TPH0200', WASTERATE, NULL)) WASTERATE3,
       'TPH0300' ITEMNO4,
       SUM(DECODE(C.ITEMNO, 'TPH0300', CONSUMPTIONRATE, NULL)) CONSUMPTIONRATE4,
       SUM(DECODE(C.ITEMNO, 'TPH0300', WASTERATE, NULL)) WASTERATE4,
       'TPH0400' ITEMNO5,
       SUM(DECODE(C.ITEMNO, 'TPH0400', CONSUMPTIONRATE, NULL)) CONSUMPTIONRATE5,
       SUM(DECODE(C.ITEMNO, 'TPH0400', WASTERATE, NULL)) WASTERATE5,
       2 GROUPNO
  FROM (SELECT A.SEQUENCENO,
               A.CUSTOMCODE,
               B.CONTRACTID,
               B.ITEMNO,
               B.SUBITEMNO,
               B.CONSUMPTIONRATE,
               B.WASTERATE
          FROM CONTRACTITEM A, CONTRACTITEMSUBITEM B
         WHERE A.CONTRACTID = B.CONTRACTID
           AND A.ITEMNO = B.SUBITEMNO
           AND B.ITEMNO = 'TPH0500') C
 GROUP BY C.ITEMNO, C.SEQUENCENO, C.CONTRACTID, C.SUBITEMNO
 ORDER BY C.SEQUENCENO)
 SELECT * FROM OCC0100_TPH0400
 UNION ALL
 SELECT * FROM TPH0500;
你们都是坏人 2017-01-12
  • 打赏
  • 举报
 回复
引用 7 楼 xinhao_ 的回复:
[quote=引用 6 楼 js14982 的回复:] 通过a，b得到c表的逻辑是什么，我看了半天没看懂你怎么得来的c表
c表 是 要得到的数据，是是手动建的数据。 根据 B 中的 ITEMNO 进行分组，每5个一组 例如 上述 表B中 ITEMNO 不同的值有6个， 所以前5个 进行分组 'OCC0100','TPH0100','TPH0200','TPH0300','TPH0400' 
  select  A.contractid,A.sequenceno,b.subitemno,b.subitemname  from contractitem A ,
           (select distinct  contractid,subitemno, subitemname from contractitemsubitem  where contractid='D1'
              AND  itemno in ('OCC0100','TPH0100','TPH0200','TPH0300','TPH0400') ) B  where  
            A.contractid=B.contractid AND A.itemno=B.subitemno order by A.sequenceno
sql查出对应的是 VIEWCONSUMPTAPP 中的 10笔资料，groupno=1 剩下的一个 'TPH0500' 另外分组 
 select  A.contractid,A.sequenceno,b.subitemno,b.subitemname  from contractitem A ,
           (select distinct  contractid,subitemno, subitemname from contractitemsubitem  where contractid='D1'
              AND  itemno in ('TPH0500') ) B  where  
            A.contractid=B.contractid AND A.itemno=B.subitemno order by A.sequenceno
sql查出对应的是 VIEWCONSUMPTAPP 中的 后面3笔笔资料，groupno=2 [/quote] 以上sql多加了栏位 subitemname sql 应该为 
select  A.contractid,A.sequenceno,b.subitemno  from contractitem A ,
           (select distinct  contractid,subitemno from contractitemsubitem  where contractid='D1'
              AND  itemno in ('OCC0100','TPH0100','TPH0200','TPH0300','TPH0400') ) B  where  
            A.contractid=B.contractid AND A.itemno=B.subitemno order by A.sequenceno
与 
select  A.contractid,A.sequenceno,b.subitemno  from contractitem A ,
           (select distinct  contractid,subitemno  from contractitemsubitem  where contractid='D1'
              AND  itemno in ('TPH0500') ) B  where  
            A.contractid=B.contractid AND A.itemno=B.subitemno order by A.sequenceno
你们都是坏人 2017-01-12
  • 打赏
  • 举报
 回复
引用 6 楼 js14982 的回复:
通过a，b得到c表的逻辑是什么，我看了半天没看懂你怎么得来的c表
c表 是 要得到的数据，是是手动建的数据。 根据 B 中的 ITEMNO 进行分组，每5个一组 例如 上述 表B中 ITEMNO 不同的值有6个， 所以前5个 进行分组 'OCC0100','TPH0100','TPH0200','TPH0300','TPH0400' 
  select  A.contractid,A.sequenceno,b.subitemno,b.subitemname  from contractitem A ,
           (select distinct  contractid,subitemno, subitemname from contractitemsubitem  where contractid='D1'
              AND  itemno in ('OCC0100','TPH0100','TPH0200','TPH0300','TPH0400') ) B  where  
            A.contractid=B.contractid AND A.itemno=B.subitemno order by A.sequenceno
sql查出对应的是 VIEWCONSUMPTAPP 中的 10笔资料，groupno=1 剩下的一个 'TPH0500' 另外分组 
 select  A.contractid,A.sequenceno,b.subitemno,b.subitemname  from contractitem A ,
           (select distinct  contractid,subitemno, subitemname from contractitemsubitem  where contractid='D1'
              AND  itemno in ('TPH0500') ) B  where  
            A.contractid=B.contractid AND A.itemno=B.subitemno order by A.sequenceno
sql查出对应的是 VIEWCONSUMPTAPP 中的 后面3笔笔资料，groupno=2
js14982 2017-01-12
  • 打赏
  • 举报
 回复
通过a，b得到c表的逻辑是什么，我看了半天没看懂你怎么得来的c表
你们都是坏人 2017-01-12
  • 打赏
  • 举报
 回复
要求 求一个sql 通过 CONTRACTITEM，CONTRACTITEMSUBITEM 得到的数据 和表VIEWCONSUMPTAPP 中的数据一样。 《表VIEWCONSUMPTAPP 中的数据 是根据要求，手动插入的资料 》 
create table VIEWCONSUMPTAPP
(
  CONTRACTID       VARCHAR2(30),
  SEQUENCENO       NUMBER(5),
  SUBITEMNO        VARCHAR2(7),

  ITEMNO1          VARCHAR2(7),
  CONSUMPTIONRATE1 NUMBER(10,6),
  WASTERATE1       NUMBER(10,6),

  ITEMNO2          VARCHAR2(7),
  CONSUMPTIONRATE2 NUMBER(10,6),
  WASTERATE2       NUMBER(10,6),

  ITEMNO3          VARCHAR2(7),
  CONSUMPTIONRATE3 NUMBER(10,6),
  WASTERATE3       NUMBER(10,6),

 
  ITEMNO4          VARCHAR2(7),
  CONSUMPTIONRATE4 NUMBER(10,6),
  WASTERATE4       NUMBER(10,6),


  ITEMNO5          VARCHAR2(7),
  CONSUMPTIONRATE5 NUMBER(10,6),
  WASTERATE5       NUMBER(10,6),

  GROUPNO          NUMBER(5)
)
你们都是坏人 2017-01-12
  • 打赏
  • 举报
 回复
引用 3 楼 sxq129601 的回复:
能否给个简单的点的单表案例，你这谁看的清
建表语句如下 

create table CONTRACTITEM
(
  CONTRACTID         VARCHAR2(30) not null,
  ITEMNO             VARCHAR2(7) not null,
  SEQUENCENO         NUMBER(4),
  CUSTOMCODE         VARCHAR2(12) default ''
)

create table CONTRACTITEMSUBITEM
(
  CONTRACTID      VARCHAR2(30) not null,
  ITEMNO          VARCHAR2(7) not null,
  SUBITEMNO       VARCHAR2(7) not null,
  CONSUMPTIONRATE NUMBER(10,6) default 0 not null,
  WASTERATE       NUMBER(10,6) default 0 not null
)
sxq129601 2017-01-12
  • 打赏
  • 举报
 回复
能否给个简单的点的单表案例，你这谁看的清
你们都是坏人 2017-01-12
  • 打赏
  • 举报
 回复
@卖水果的net @ghx287524027 @js14982 大神，求帮忙！！
你们都是坏人 2017-01-12
  • 打赏
  • 举报
 回复
木有人啊！！！！
Oracle中SQL语句行列之间相互转换
本人在工作中遇到了涉及到数据库行列之间相互转换的问题，在网上搜索了很久也没有一个比较完整的解释，通过本人自己的摸索测试，整理出来了Oracle中SQL语句行列之间相互转换的资料，大家可以下载学习。
oracle sql 行列转换
oracle sql行列转换
Oracle数据库SQL语言
本课程全面介绍oracle的SQL语言。介绍查询语句DML语句DDL语句数据库对象等等
Oracle SQLServer行列转换
分别讲述了SQLServer和Oracle行列转换的两种方式，可以通过SQL实现，也可通过关键字搞定； 还可以看到Oracle与SQLServer关键字用法的差异。
sql行列转换、一个字段包含另一个字段.sql
sql行列转换、一个字段包含另一个字段.sql
Oracle 高级技术

3,491

社区成员

18,714

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章