求助oracle的连接查询sql

Question

在oracle中查询数据
有两个表 t_people, t_income，t_people：
-----------------
id name
-----------------
01 Edison
02 Nicolas
03 Cecilia
04 Gill
-----------------t_income：
-------------------------------------
id name year amount
-------------------------------------
01 Edison 2008 50000
02 Edison 2007 40000
03 Edison 2006 30000
05 Nicolas 2007 55000
06 Nicolas 2006 40000
07 Cecilia 2008 30000
08 Cecilia 2007 25000
-------------------------------------想写查询语句查询每个人近三年的收入情况
每个人都列出来
想得到这样的结果-----------------------------------------------
name income_1 income_2 income_3
-----------------------------------------------
Edison 50000 40000 30000
Nicolas 0.0 55000 55000
Cecilia 25000 30000 0.0
Gill 0.0 0.0 0.0
-----------------------------------------------我用左连接写的sql如下select pt.name,
(case when it.year=(to_char(sysdate,'yyyy')-2) then it.amount end) income_1,
(case when it.year=(to_char(sysdate,'yyyy')-1) then it.amount end) income_2,
(case when it.year=(to_char(sysdate,'yyyy')-0) then it.amount end) income_3
form t_people pt, t_income it
where pt.name = it.name(+);
group by pt.name;可是会报错：
ORA-00979: 不是 GROUP BY 表达式请问各位数据库高手我这个sql语句应该怎么写

Answer 1

select name,year,sum(amount) from t_income order by name,year group by name,year;

Answer 2

select pt.name, 
(case when it.year=(to_char(sysdate,'yyyy')-2) then it.amount end) income_1, 
(case when it.year=(to_char(sysdate,'yyyy')-1) then it.amount end) income_2, 
(case when it.year=(to_char(sysdate,'yyyy')-0) then it.amount end) income_3 
form t_people pt, t_income it 
where pt.name = it.name(+); 
order by pt.name; 

Answer 3

select pt.name, 
       decode(it.year,to_char(sysdate-2,'yyyy'),it.amount,0) income_2008',
       decode(it.year,to_char(sysdate-1,'yyyy'),it.amount,0) income_2007',
       decode(it.year,to_char(sysdate,'yyyy'),it.amount,0) income_2006'
   form t_people pt, t_income it 
   where pt.name = it.name(+); 
   order by pt.name; 

Answer 4

select pt.name, 
      decode(it.year,to_char(sysdate-2,'yyyy'),it.amount,0) income_2008, 
      decode(it.year,to_char(sysdate-1,'yyyy'),it.amount,0) income_2007, 
      decode(it.year,to_char(sysdate,'yyyy'),it.amount,0) income_2006 
  form t_people pt, t_income it 
  where pt.name = it.name(+); 
  order by pt.name; 

Answer 5

可能这样来写SQL的:select pt.name,
sum(decode (it.year,(to_char(sysdate,'yyyy')-2),it.amount,0)) income_1,
sum(decode (it.year,(to_char(sysdate,'yyyy')-1),it.amount,0)) income_2,
sum(decode (it.year,(to_char(sysdate,'yyyy')-0),it.amount,0)) income_3
from t_people pt, t_income it 
where pt.name = it.name(+)
group by pt.name;

Answer 6

谢谢大家回答
我遇到的问题并不是查询收入金额
而是我不想这样的结果
----------------------------------------------- 
name income_1 income_2 income_3 
----------------------------------------------- 
Edison 50000   0.0     0.0
Edison 0.0    40000   0.0
Edison 0.0    0.0     30000  
----------------------------------------------- 而是像这样
----------------------------------------------- 
name income_1 income_2 income_3 
----------------------------------------------- 
Edison 50000 40000 30000 
----------------------------------------------- 我想把结果按姓名group by起来的时候报错了

Answer 7

select pt.name, 
max(case when it.year=(to_char(sysdate,'yyyy')-2) then it.amount end) income_1, 
max(case when it.year=(to_char(sysdate,'yyyy')-1) then it.amount end) income_2, 
max(case when it.year=(to_char(sysdate,'yyyy')-0) then it.amount end) income_3 
form t_people pt, t_income it 
where pt.name = it.name(+); 
group by pt.name; 加个分组的函数!

Answer 8

我就是在java里面组一条sql语句
再组合一些搜索条件

Answer 9

select id,name,(select amount from t_income where t_income.id=t_people.id and year=2008) income_1,(select amount from t_income where t_income.id=t_people.id and year=2007) income_2,(select amount from t_income where t_income.id=t_people.id and year=2006) income_3 from t_people;

Answer 10

问题解决把行转成列,类似下面的例子SELECT id,
       MAX(decode(cn, 'c1', cv, NULL)) AS c1,
       MAX(decode(cn, 'c2', cv, NULL)) AS c2,
       MAX(decode(cn, 'c3', cv, NULL)) AS c3
  FROM 
SELECT id, 'c1' cn, c1 cv
  FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
  FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv 
  FROM t_col_row
GROUP BY id
ORDER BY 1;