group by后必须跟所有select 中没有参与计算的字段 你可以用相关子查询实现你的结果:SELECT a.Date, a.Class, (select SUM(a.amount) from tablename1 c where a.class=c.class group by c.class) as amount, b.note, b.Name FROM tablename1 a LEFT OUTER JOIN tablename2 b ON a.ID = b.ID
SELECT a.Date, a.Class, SUM(a.amount) over(partition by a.class), b.note, b.Name FROM tablename1 a, tablename2 b where a.ID = b.ID(+) GROUP BY a.Class
sorry:SELECT a.Date, a.Class, SUM(a.amount) over(partition by a.class), b.note, b.Name FROM tablename1 a, tablename2 b where a.ID = b.ID(+)
group by后必须跟所有select 中没有参与计算的字段,这是对的。也就是说,其他没有参与sum,max或min等的字段,如果要想查询出来就必须group by。
你可以用相关子查询实现你的结果:SELECT
a.Date,
a.Class,
(select SUM(a.amount) from tablename1 c
where a.class=c.class
group by c.class) as amount,
b.note,
b.Name
FROM
tablename1 a
LEFT OUTER JOIN tablename2 b ON a.ID = b.ID
a.Date,
a.Class,
SUM(a.amount) over(partition by a.class),
b.note,
b.Name
FROM
tablename1 a, tablename2 b where a.ID = b.ID(+)
GROUP BY a.Class
a.Date,
a.Class,
SUM(a.amount) over(partition by a.class),
b.note,
b.Name
FROM
tablename1 a, tablename2 b where a.ID = b.ID(+)