需求,有四个表关联-- 产品表(备用12345,删除标志,产品主键、产品编码、产品名称、停用标志,增删改时间戳)
create table FW_PRODUCT
(
def1 VARCHAR2(50),
def2 VARCHAR2(50),
def3 VARCHAR2(50),
def4 VARCHAR2(50),
def5 VARCHAR2(50),
dr NUMBER(10) default 0,
pk_fwproduct CHAR(20) not null,
productcode VARCHAR2(50),
productname VARCHAR2(50),
stopflag CHAR(1),
ts CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
);-- 市场表(分析标志,备用12345,删除标志,市场编码,市场名称,产品主键、市场主键,停用标志,增删改时间戳)
create table FW_MARKET
(
analyseflag CHAR(1),
def1 VARCHAR2(100),
def2 VARCHAR2(100),
def3 VARCHAR2(100),
def4 VARCHAR2(100),
def5 VARCHAR2(100),
dr NUMBER(10) default 0,
etcode VARCHAR2(50) not null,
etname VARCHAR2(50),
pk_fwproduct CHAR(20),
pk_et CHAR(20) not null,
stopflag CHAR(1),
ts CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
);
-- 报价主表(备用12345,删除标志,报价主键,报价日期,产品名称,增删改时间戳)
create table FW_PRICE
(
def1 VARCHAR2(100),
def2 VARCHAR2(100),
def3 VARCHAR2(100),
def4 VARCHAR2(100),
def5 VARCHAR2(100),
dr NUMBER(10) default 0,
pk_fwprice CHAR(20) not null,
pricedate CHAR(10) not null,
productname CHAR(20),
ts CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
);-- 报价子表(备用12345,删除标志,报价主表PK,市场PK,报价子表PK,价格,增删改时间戳)
create table FW_PRICE_B
(
def1 VARCHAR2(50),
def2 VARCHAR2(50),
def3 VARCHAR2(50),
def4 VARCHAR2(50),
def5 VARCHAR2(50),
dr NUMBER(10) default 0,
infob CHAR(20),
et CHAR(20),
pk_fwpriceb CHAR(20) not null,
price NUMBER(28,8),
ts CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
);-- 测试数据
insert into FW_PRODUCT (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRODUCT, PRODUCTCODE, PRODUCTNAME, STOPFLAG, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BPAQ', '01', '铝锭', 'N', '2013-05-24 11:59:19');
insert into FW_PRODUCT (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRODUCT, PRODUCTCODE, PRODUCTNAME, STOPFLAG, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BPAR', '02', '铝箔', 'N', '2013-05-24 11:59:25');
insert into FW_PRODUCT (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRODUCT, PRODUCTCODE, PRODUCTNAME, STOPFLAG, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BPAS', '03', '煤炭', 'N', '2013-05-24 11:59:34');
insert into FW_MARKET (ANALYSEFLAG, DEF1, DEF2, DEF3, DEF4, DEF5, DR, MARKETCODE, MARKETNAME, PK_FWPRODUCT, PK_MARKET, STOPFLAG, TS)
values ('N', null, null, null, null, null, 0, '001', '南方_煤', '1003ZZ1000000000BPAS', '1003ZZ1000000000BPAT', 'N', '2013-05-24 12:00:15');
insert into FW_MARKET (ANALYSEFLAG, DEF1, DEF2, DEF3, DEF4, DEF5, DR, MARKETCODE, MARKETNAME, PK_FWPRODUCT, PK_MARKET, STOPFLAG, TS)
values ('N', null, null, null, null, null, 0, '002', '北方_煤', '1003ZZ1000000000BPAS', '1003ZZ1000000000BPAV', 'N', '2013-05-24 12:12:04');
insert into FW_MARKET (ANALYSEFLAG, DEF1, DEF2, DEF3, DEF4, DEF5, DR, MARKETCODE, MARKETNAME, PK_FWPRODUCT, PK_MARKET, STOPFLAG, TS)
values ('N', null, null, null, null, null, 0, '003', '山西_煤炭', '1003ZZ1000000000BPAS', '1003ZZ1000000000BPAW', 'N', '2013-05-24 12:12:23');
insert into FW_MARKET (ANALYSEFLAG, DEF1, DEF2, DEF3, DEF4, DEF5, DR, MARKETCODE, MARKETNAME, PK_FWPRODUCT, PK_MARKET, STOPFLAG, TS)
values ('N', null, null, null, null, null, 0, '004', '江西_铝', '1003ZZ1000000000BPAQ', '1003ZZ1000000000BPAX', 'N', '2013-05-24 12:13:34');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57I', '2013-05-24', '1003ZZ1000000000BPAS', '2013-05-24 19:30:41');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57M', '2013-05-24', '1003ZZ1000000000BPAQ', '2013-05-24 19:30:28');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSA', '2013-05-27', '1003ZZ1000000000BPAS', '2013-05-27 10:35:19');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSE', '2013-05-26', '1003ZZ1000000000BPAS', '2013-05-27 10:36:08');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5L2', '2013-05-07', '1003ZZ1000000000BPAR', '2013-05-25 01:41:00');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5L8', '2013-05-25', '1003ZZ1000000000BPAQ', '2013-05-25 01:48:03');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5LD', '2013-05-25', '1003ZZ1000000000BPAS', '2013-05-25 01:49:27');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57I', '1003ZZ1000000000BPAT', '0001ZZ1000000001H57J', 2341.00000000, '2013-05-24 19:30:07');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57I', '1003ZZ1000000000BPAV', '0001ZZ1000000001H57K', 2150.00000000, '2013-05-24 19:30:07');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57I', '1003ZZ1000000000BPAW', '0001ZZ1000000001H57L', 2000.00000000, '2013-05-24 19:30:07');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57M', '1003ZZ1000000000BPAX', '0001ZZ1000000001H57N', 12.00000000, '2013-05-24 19:30:28');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSA', '1003ZZ1000000000BPAT', '1003ZZ1000000000BXSB', 2805.00000000, '2013-05-27 10:35:19');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSA', '1003ZZ1000000000BPAV', '1003ZZ1000000000BXSC', 2680.00000000, '2013-05-27 10:35:19');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSA', '1003ZZ1000000000BPAW', '1003ZZ1000000000BXSD', 2600.00000000, '2013-05-27 10:35:19');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSE', '1003ZZ1000000000BPAT', '1003ZZ1000000000BXSF', 2785.00000000, '2013-05-27 10:36:08');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSE', '1003ZZ1000000000BPAV', '1003ZZ1000000000BXSG', 2710.00000000, '2013-05-27 10:36:08');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSE', '1003ZZ1000000000BPAW', '1003ZZ1000000000BXSH', 2655.00000000, '2013-05-27 10:36:08');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5L2', '1003ZZ1000000000BPAX', '0001ZZ1000000001H5L3', 12.00000000, '2013-05-25 01:41:00');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5L8', '1003ZZ1000000000BPAX', '0001ZZ1000000001H5L9', 12.00000000, '2013-05-25 01:48:03');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5LD', '1003ZZ1000000000BPAX', '0001ZZ1000000001H5LE', 12.00000000, '2013-05-25 01:49:27');
-- 查询煤炭的各市场报价
select fw_price.pricedate,
fw_et.etcode,
fw_et.etname,
fw_product.productcode,
fw_product.productname,
fw_price_b.price
from fw_price_b, fw_price, fw_et, fw_product
where fw_price_b.infob = fw_price.pk_fwprice
and fw_price_b.et = fw_et.pk_et
and fw_et.pk_fwproduct = fw_product.pk_fwproduct
and fw_product.productcode='03'
order by pricedate原始输出如下:PRICEDATE MARKETCODE MARKETNAME PRODUCTCODE PRODUCTNAME PRICE
2013/5/24 3 山西_煤炭 3 煤炭 2000
2013/5/24 1 南方_煤 3 煤炭 2341
2013/5/24 2 北方_煤 3 煤炭 2150
2013/5/26 2 北方_煤 3 煤炭 2710
2013/5/26 1 南方_煤 3 煤炭 2785
2013/5/26 3 山西_煤炭 3 煤炭 2655
2013/5/27 3 山西_煤炭 3 煤炭 2600
2013/5/27 1 南方_煤 3 煤炭 2805
2013/5/27 2 北方_煤 3 煤炭 2680希望能够输出:日期 山西_煤炭 南方_煤 北方_煤
2013/5/24 2000 2341 2150
2013/5/26 2655 2785 2710
请问该如何写这个SQL?
市场的数据会是动态增加的
create table FW_PRODUCT
(
def1 VARCHAR2(50),
def2 VARCHAR2(50),
def3 VARCHAR2(50),
def4 VARCHAR2(50),
def5 VARCHAR2(50),
dr NUMBER(10) default 0,
pk_fwproduct CHAR(20) not null,
productcode VARCHAR2(50),
productname VARCHAR2(50),
stopflag CHAR(1),
ts CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
);-- 市场表(分析标志,备用12345,删除标志,市场编码,市场名称,产品主键、市场主键,停用标志,增删改时间戳)
create table FW_MARKET
(
analyseflag CHAR(1),
def1 VARCHAR2(100),
def2 VARCHAR2(100),
def3 VARCHAR2(100),
def4 VARCHAR2(100),
def5 VARCHAR2(100),
dr NUMBER(10) default 0,
etcode VARCHAR2(50) not null,
etname VARCHAR2(50),
pk_fwproduct CHAR(20),
pk_et CHAR(20) not null,
stopflag CHAR(1),
ts CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
);
-- 报价主表(备用12345,删除标志,报价主键,报价日期,产品名称,增删改时间戳)
create table FW_PRICE
(
def1 VARCHAR2(100),
def2 VARCHAR2(100),
def3 VARCHAR2(100),
def4 VARCHAR2(100),
def5 VARCHAR2(100),
dr NUMBER(10) default 0,
pk_fwprice CHAR(20) not null,
pricedate CHAR(10) not null,
productname CHAR(20),
ts CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
);-- 报价子表(备用12345,删除标志,报价主表PK,市场PK,报价子表PK,价格,增删改时间戳)
create table FW_PRICE_B
(
def1 VARCHAR2(50),
def2 VARCHAR2(50),
def3 VARCHAR2(50),
def4 VARCHAR2(50),
def5 VARCHAR2(50),
dr NUMBER(10) default 0,
infob CHAR(20),
et CHAR(20),
pk_fwpriceb CHAR(20) not null,
price NUMBER(28,8),
ts CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
);-- 测试数据
insert into FW_PRODUCT (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRODUCT, PRODUCTCODE, PRODUCTNAME, STOPFLAG, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BPAQ', '01', '铝锭', 'N', '2013-05-24 11:59:19');
insert into FW_PRODUCT (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRODUCT, PRODUCTCODE, PRODUCTNAME, STOPFLAG, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BPAR', '02', '铝箔', 'N', '2013-05-24 11:59:25');
insert into FW_PRODUCT (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRODUCT, PRODUCTCODE, PRODUCTNAME, STOPFLAG, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BPAS', '03', '煤炭', 'N', '2013-05-24 11:59:34');
insert into FW_MARKET (ANALYSEFLAG, DEF1, DEF2, DEF3, DEF4, DEF5, DR, MARKETCODE, MARKETNAME, PK_FWPRODUCT, PK_MARKET, STOPFLAG, TS)
values ('N', null, null, null, null, null, 0, '001', '南方_煤', '1003ZZ1000000000BPAS', '1003ZZ1000000000BPAT', 'N', '2013-05-24 12:00:15');
insert into FW_MARKET (ANALYSEFLAG, DEF1, DEF2, DEF3, DEF4, DEF5, DR, MARKETCODE, MARKETNAME, PK_FWPRODUCT, PK_MARKET, STOPFLAG, TS)
values ('N', null, null, null, null, null, 0, '002', '北方_煤', '1003ZZ1000000000BPAS', '1003ZZ1000000000BPAV', 'N', '2013-05-24 12:12:04');
insert into FW_MARKET (ANALYSEFLAG, DEF1, DEF2, DEF3, DEF4, DEF5, DR, MARKETCODE, MARKETNAME, PK_FWPRODUCT, PK_MARKET, STOPFLAG, TS)
values ('N', null, null, null, null, null, 0, '003', '山西_煤炭', '1003ZZ1000000000BPAS', '1003ZZ1000000000BPAW', 'N', '2013-05-24 12:12:23');
insert into FW_MARKET (ANALYSEFLAG, DEF1, DEF2, DEF3, DEF4, DEF5, DR, MARKETCODE, MARKETNAME, PK_FWPRODUCT, PK_MARKET, STOPFLAG, TS)
values ('N', null, null, null, null, null, 0, '004', '江西_铝', '1003ZZ1000000000BPAQ', '1003ZZ1000000000BPAX', 'N', '2013-05-24 12:13:34');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57I', '2013-05-24', '1003ZZ1000000000BPAS', '2013-05-24 19:30:41');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57M', '2013-05-24', '1003ZZ1000000000BPAQ', '2013-05-24 19:30:28');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSA', '2013-05-27', '1003ZZ1000000000BPAS', '2013-05-27 10:35:19');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSE', '2013-05-26', '1003ZZ1000000000BPAS', '2013-05-27 10:36:08');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5L2', '2013-05-07', '1003ZZ1000000000BPAR', '2013-05-25 01:41:00');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5L8', '2013-05-25', '1003ZZ1000000000BPAQ', '2013-05-25 01:48:03');
insert into FW_PRICE (DEF1, DEF2, DEF3, DEF4, DEF5, DR, PK_FWPRICE, PRICEDATE, PRODUCTNAME, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5LD', '2013-05-25', '1003ZZ1000000000BPAS', '2013-05-25 01:49:27');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57I', '1003ZZ1000000000BPAT', '0001ZZ1000000001H57J', 2341.00000000, '2013-05-24 19:30:07');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57I', '1003ZZ1000000000BPAV', '0001ZZ1000000001H57K', 2150.00000000, '2013-05-24 19:30:07');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57I', '1003ZZ1000000000BPAW', '0001ZZ1000000001H57L', 2000.00000000, '2013-05-24 19:30:07');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H57M', '1003ZZ1000000000BPAX', '0001ZZ1000000001H57N', 12.00000000, '2013-05-24 19:30:28');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSA', '1003ZZ1000000000BPAT', '1003ZZ1000000000BXSB', 2805.00000000, '2013-05-27 10:35:19');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSA', '1003ZZ1000000000BPAV', '1003ZZ1000000000BXSC', 2680.00000000, '2013-05-27 10:35:19');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSA', '1003ZZ1000000000BPAW', '1003ZZ1000000000BXSD', 2600.00000000, '2013-05-27 10:35:19');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSE', '1003ZZ1000000000BPAT', '1003ZZ1000000000BXSF', 2785.00000000, '2013-05-27 10:36:08');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSE', '1003ZZ1000000000BPAV', '1003ZZ1000000000BXSG', 2710.00000000, '2013-05-27 10:36:08');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '1003ZZ1000000000BXSE', '1003ZZ1000000000BPAW', '1003ZZ1000000000BXSH', 2655.00000000, '2013-05-27 10:36:08');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5L2', '1003ZZ1000000000BPAX', '0001ZZ1000000001H5L3', 12.00000000, '2013-05-25 01:41:00');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5L8', '1003ZZ1000000000BPAX', '0001ZZ1000000001H5L9', 12.00000000, '2013-05-25 01:48:03');
insert into FW_PRICE_B (DEF1, DEF2, DEF3, DEF4, DEF5, DR, INFOB, MARKET, PK_FWPRICEB, PRICE, TS)
values (null, null, null, null, null, 0, '0001ZZ1000000001H5LD', '1003ZZ1000000000BPAX', '0001ZZ1000000001H5LE', 12.00000000, '2013-05-25 01:49:27');
-- 查询煤炭的各市场报价
select fw_price.pricedate,
fw_et.etcode,
fw_et.etname,
fw_product.productcode,
fw_product.productname,
fw_price_b.price
from fw_price_b, fw_price, fw_et, fw_product
where fw_price_b.infob = fw_price.pk_fwprice
and fw_price_b.et = fw_et.pk_et
and fw_et.pk_fwproduct = fw_product.pk_fwproduct
and fw_product.productcode='03'
order by pricedate原始输出如下:PRICEDATE MARKETCODE MARKETNAME PRODUCTCODE PRODUCTNAME PRICE
2013/5/24 3 山西_煤炭 3 煤炭 2000
2013/5/24 1 南方_煤 3 煤炭 2341
2013/5/24 2 北方_煤 3 煤炭 2150
2013/5/26 2 北方_煤 3 煤炭 2710
2013/5/26 1 南方_煤 3 煤炭 2785
2013/5/26 3 山西_煤炭 3 煤炭 2655
2013/5/27 3 山西_煤炭 3 煤炭 2600
2013/5/27 1 南方_煤 3 煤炭 2805
2013/5/27 2 北方_煤 3 煤炭 2680希望能够输出:日期 山西_煤炭 南方_煤 北方_煤
2013/5/24 2000 2341 2150
2013/5/26 2655 2785 2710
请问该如何写这个SQL?
市场的数据会是动态增加的
http://bbs.csdn.net/topics/330039676Oracle 11g 行列互换 pivot 和 unpivot
(select fw_price.pricedate, fw_et.etname, fw_price_b.price
from fw_price_b, fw_price, fw_et, fw_product
where fw_price_b.infob = fw_price.pk_fwprice
and fw_price_b.et = fw_et.pk_et
and fw_et.pk_fwproduct = fw_product.pk_fwproduct
and fw_product.productcode = '03'
order by pricedate)
select *
from t pivot(sum(price) for etname in('山西_煤炭',
'南方_煤',
'北方_煤'));
一个sql搞不定动态增加,虽然pivot能搞定,但输出的格式是xml的,一般都不实用。。
可以参考下面的帖子,稍显复杂。
http://www.itpub.net/thread-1609939-1-1.html