一个字段分为两列显示，只用一句SQL实现可以吗？

Question

select 
    a.date,
    a.tag,
    decode(a.tag,'收入',a,AMT,'') 收入,
    decode(a.tag,'付出',a.AMT,'') 付出
from 
    tablename a

Answer 1

你要的是不是,每一天的收入付出.
select a.date,sum(income) 收入总额,sum(payamt) 付出总额
from 
(
select a.date,decode(a.tag,'收入',a.amt,0) income ,0 payamt 
from tablename a
union all
select a.date,0 income ,decode(a.tag,'付出',a.amt,0) payamt 
from tablename a
)
group by a.date

Answer 2

SQL> --更改日期格式
SQL> alter session set nls_date_format='YYYY-MM-DD';会话已更改。SQL> create table a
  2  (opdate date,
  3   tag    varchar2(4),
  4   amt    number(12,2)
  5  );表已创建。SQL> insert into a values ('2004-11-1','收入','134.00');已创建 1 行。SQL> insert into a values ('2004-11-2','收入','3790.01');已创建 1 行。SQL> insert into a values ('2004-11-2','支出','1000.00');已创建 1 行。SQL> insert into a values ('2004-11-1','支出','1000.00');已创建 1 行。SQL> insert into a values ('2004-11-3','收入','6000.50');已创建 1 行。SQL> insert into a values ('2004-11-5','收入','4201.00');已创建 1 行。SQL> commit;提交完成。SQL> select * from a;OPDATE     TAG         AMT                                                      
---------- ---- ----------                                                      
2004-11-01 收入        134                                                      
2004-11-02 收入    3790.01                                                      
2004-11-02 支出       1000                                                      
2004-11-01 支出       1000                                                      
2004-11-03 收入     6000.5                                                      
2004-11-05 收入       4201                                                      已选择6行。SQL> --查询所有数据
SQL> select opdate,
  2         tag,
  3         decode(tag,'收入',a.amt,'') 收入,
  4         decode(tag,'支出',a.amt,'') 支出
  5*   from a;OPDATE     TAG        收入       支出                                           
---------- ---- ---------- ----------                                           
2004-11-01 收入        134                                                      
2004-11-02 收入    3790.01                                                      
2004-11-02 支出                  1000                                           
2004-11-01 支出                  1000                                           
2004-11-03 收入     6000.5                                                      
2004-11-05 收入       4201                                                      已选择6行。SQL> --查询每日收入和支出合计，以及总合计（rollup函数）  1  select opdate 日期,
  2         nvl(sum(decode(tag,'收入',a.amt,'')),0) 收入,
  3         nvl(sum(decode(tag,'支出',a.amt,'')),0) 支出
  4    from a
  5* group by rollup(opdate)日期             收入       支出                                                
---------- ---------- ----------                                                
2004-11-01        134       1000                                                
2004-11-02    3790.01       1000                                                
2004-11-03     6000.5          0                                                
2004-11-05       4201          0                                                
             14125.51       2000                                                SQL> --查询每日收入和支出合计，以及总合计（cube函数）  1  select opdate 日期,
  2         nvl(sum(decode(tag,'收入',a.amt,'')),0) 收入,
  3         nvl(sum(decode(tag,'支出',a.amt,'')),0) 支出
  4    from a
  5* group by cube(opdate)日期             收入       支出                                                
---------- ---------- ----------                                                
2004-11-01        134       1000                                                
2004-11-02    3790.01       1000                                                
2004-11-03     6000.5          0                                                
2004-11-05       4201          0                                                
             14125.51       2000                                                

Answer 3

上面贴子的显示格式和我编辑的不一样，如果你要看仔细的，请到下面地址http://sanoul.mblogger.cn/posts/20327.aspx

Answer 4

http://sanoul.mblogger.cn/posts/20327.aspx--------------=====================
SQL> create table a
  2  (opdate date,
  3   tag    varchar2(4),
  4   amt    number(12,2)
  5  );表已创建。SQL> insert into a values ('2004-11-1','收入','134.00');已创建 1 行。SQL> insert into a values ('2004-11-2','收入','3790.01');已创建 1 行。SQL> insert into a values ('2004-11-2','支出','1000.00');已创建 1 行。SQL> insert into a values ('2004-11-1','支出','1000.00');已创建 1 行。SQL> insert into a values ('2004-11-3','收入','6000.50');已创建 1 行。SQL> insert into a values ('2004-11-5','收入','4201.00');已创建 1 行。SQL> commit;提交完成。SQL> select * from a;OPDATE     TAG         AMT 
---------- ---- ---------- 
2004-11-01 收入     134.00 
2004-11-02 收入    3790.01   
2004-11-02 支出       1000 
2004-11-01 支出       1000
2004-11-03 收入     6000.5
2004-11-05 收入       4201已选择6行。SQL> --查询所有数据
SQL> select opdate,
  2         tag,
  3         decode(tag,'收入',a.amt,'') 收入,
  4         decode(tag,'支出',a.amt,'') 支出
  5*   from a;OPDATE     TAG        收入       支出 
---------- ---- ---------- ---------- 
2004-11-01 收入        134             
2004-11-02 收入    3790.01            
2004-11-02 支出                  1000  
2004-11-01 支出                  1000  
2004-11-03 收入     6000.5           
2004-11-05 收入       4201          已选择6行。SQL> --查询每日收入和支出合计，以及总合计（rollup函数）  1  select opdate 日期,
  2         nvl(sum(decode(tag,'收入',a.amt,'')),0) 收入,
  3         nvl(sum(decode(tag,'支出',a.amt,'')),0) 支出
  4    from a
  5* group by rollup(opdate)日期             收入       支出    
---------- ---------- ----------    
2004-11-01        134       1000 
2004-11-02    3790.01       1000 
2004-11-03     6000.5          0 
2004-11-05       4201          0  
             14125.51       2000 SQL> --查询每日收入和支出合计，以及总合计（cube函数）  1  select opdate 日期,
  2         nvl(sum(decode(tag,'收入',a.amt,'')),0) 收入,
  3         nvl(sum(decode(tag,'支出',a.amt,'')),0) 支出
  4    from a
  5* group by cube(opdate)日期             收入       支出  
---------- ---------- ---------- 
2004-11-01        134       1000 
2004-11-02    3790.01       1000 
2004-11-03     6000.5          0  
2004-11-05       4201          0  
             14125.51       2000  

Answer 5

select date,tag,decode(tag,'收入',AMT,'') 收入,decode(tag,'付出',AMT,'') 付出 from a;即可得到你想要的显示效果!