表a
Code Re
01 aa
02 ccc
03 ddd
表b
Code date status
01 2009/07/20 S
01 2009/07/18 T
03 2009/07/15 Y需要得到的報表為 2009/07/15 - 2009/07/20 :
Code re 09/07/15 09/07/16 09/07/17 09/07/18 09/07/19 09/07/20
01 aa T S
02 ccc
03 ddd Y
Code Re
01 aa
02 ccc
03 ddd
表b
Code date status
01 2009/07/20 S
01 2009/07/18 T
03 2009/07/15 Y需要得到的報表為 2009/07/15 - 2009/07/20 :
Code re 09/07/15 09/07/16 09/07/17 09/07/18 09/07/19 09/07/20
01 aa T S
02 ccc
03 ddd Y
解决方案 »
- win7 oracle 11g 卸载的步骤
- Oracle10g创表查表问题
- 怎么写一条翻页的SQL语句?
- 100分求助,如何用JAVA程序实现Oracle两张表对拷(有Blob字段)
- 不装ORACLE客户端,能够访问ORACLE服务器么?
- oracle10g删除表为什么在管理器里还是能看到,用什么方法删除它(最好是命令)
- 向各位求救!連接各種(每種各版本)數據庫的連接字符串。
- 看看下面的SQL语句错在哪儿?
- oracle 9i for redhat linux 8.0 安装不上
- oracle 加了个regexp_replace查询很慢
- 有没有这样的查询条件
- 求求求!! 一个关于数据库查询的问题
如下(这个sql要成功运行的话,必须b表保证code,date是唯一的)select a.code, a.re, (select status from b where b.code=a.code and b.date='2009/07/15') "2009/07/15" ,
(select status from b where b.code=a.code and b.date='2009/07/16') "2009/07/16",
(select status from b where b.code=a.code and b.date='2009/07/17') "2009/07/17",
(select status from b where b.code=a.code and b.date='2009/07/18') "2009/07/18",
(select status from b where b.code=a.code and b.date='2009/07/19') "2009/07/19",
(select status from b where b.code=a.code and b.date='2009/07/20') "2009/07/20"
from a;如果日期不固定的话,需要用procedure来操作
------------建表
CREATE TABLE TEST
(
WL VARCHAR2(10),
XYSL INTEGER,
XYCK VARCHAR2(10),
XCLCK VARCHAR2(10),
XCLCKSL INTEGER,
PC INTEGER
);
------------ 第一部分测试数据
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' , 40, 321);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 50, 222);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 60, 333);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' , 70, 223);
COMMIT;
-------------------- 动态生成结果表
DECLARE
V_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS
SELECT DISTINCT T.XCLCK
FROM TEST T
ORDER BY XCLCK;
BEGIN
V_SQL := 'SELECT WL,XYSL,XYCK'; FOR V_XCLCK IN CURSOR_1
LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK ||
''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK;
END LOOP;
V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';
--DBMS_OUTPUT.PUT_LINE(V_SQL);
V_SQL := 'CREATE TABLE RESULT AS '|| V_SQL;
--DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;
--------------- 结果
SELECT * FROM RESULT T;
WL XYSL XYCK C1 C2 C3 C4 C5
---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
A1 2 C1 20 50 10 0 0
A2 3 C4 40 0 0 110 70
( select distinct AA,
max(case when cdate=to_date('2009-07-15','yyyy-mm-dd') then status end) BB,
max(case when cdate=to_date('2009-07-16','yyyy-mm-dd') then status end) CC,
max(case when cdate=to_date('2009-07-17','yyyy-mm-dd') then status end) DD,
max(case when cdate=to_date('2009-07-18','yyyy-mm-dd') then status end) EE,
max(case when cdate=to_date('2009-07-19','yyyy-mm-dd') then status end) FF,
max(case when cdate=to_date('2009-07-20','yyyy-mm-dd') then status end) GG
from
( select a.code aa,b.code bb,cdate,status
from a ,b where a.code=b.code(+)
) A,
(
SELECT (to_date(2009||'-'||7,'yyyy-mm')+rownum-1) b
FROM DUAL
CONNECT BY rownum<=30
)C
where A.cdate<=C.b
group by AA) D
where a.code=D.AA(+)RESULT:
01 aa T S
02 ccc
03 ddd Y
---接分了!
DECODE(to_char(MM_DD,'yyyy-mm-dd'),'2009-07-19',STATUS,null) "2009-07-19" ,
DECODE(to_char(MM_DD,'yyyy-mm-dd'),'2009-07-18',STATUS,null) "2009-07-18" ,
DECODE(to_char(MM_DD,'yyyy-mm-dd'),'2009-07-17',STATUS,null) "2009-07-17" ,
DECODE(to_char(MM_DD,'yyyy-mm-dd'),'2009-07-16',STATUS,null) "2009-07-16" ,
DECODE(to_char(MM_DD,'yyyy-mm-dd'),'2009-07-15',STATUS,null) "2009-07-15"
from b) B
WHERE A.CODE=B.CODE
(select status from b where b.code=a.code and b.date='2009/07/16') "2009/07/16",
(select status from b where b.code=a.code and b.date='2009/07/17') "2009/07/17",
(select status from b where b.code=a.code and b.date='2009/07/18') "2009/07/18",
(select status from b where b.code=a.code and b.date='2009/07/19') "2009/07/19",
(select status from b where b.code=a.code and b.date='2009/07/20') "2009/07/20"
from a;
CASE WHEN 就可以
上面兄弟答对的
suncrafted兄弟的,这种方式,如果是做统计是可以的,比如是max,sum,min,avg才行,不过这里是显示本身的值,好像就有些不行了。除非是加上over来做。
select a.code,a.re,
"09/07/15"=case
when b.date='09/07/15' then b.status,
"09/07/16"=case
when b.date='09/07/16' then b.status,
"09/07/17"=case
when b.date='09/07/17' then b.status,
"09/07/18"=case
when b.date='09/07/18' then b.status,
"09/07/19"=case
when b.date='09/07/19' then b.status,
"09/07/20"=case
when b.date='09/07/20' then b.status
from A a,B b
where a.code = b.code(+);