select a1.id,a1.s1,a2.s2,a3.gradename from (select id,sum(amount) as s1 from usr group by id) a1, (select id,sum(amount) as s1 from usr where birthday between '1980-8-1' and '1980-9-2' group by id) a2, (select id,gradename from level,grade where level.gradecode = grade.gradecode) a3 where a1.id = a2.id and a1.id=a3.id
同意leeyoong(莫西)的写法,不过这种写法在Oralce中没问题,在Access中就不支持了,其它数据库不太清楚。 这样写也可以: select a1.id,a1.s1,a2.s2,a4.gradename from (select id,sum(amount) as s1 from usr group by id) a1, (select id,sum(amount) as s1 from usr where birthday between '1980-8-1' and '1980-9-2' group by id) a2,level a3,grade a4 where a1.id = a2.id and a1.id=a3.id and a3.gradecode =a4.gradecode
我用的是Sql server,这种方法不行
我只能做到一个sum,而不能在一个语句中完成两个sum
第二个sum中,使用case birthday选择,若不在指定日期范围内,取零,否则取amount
分别取两个sum,用union把他们连起来。
zh430说的基本对,就是有点小错误,以下已初步测试正确: select a1.id,a1.s1,a2.s1,a4.gradename from (select id,sum(amount) as s1 from usr group by id) a1, (select id,sum(amount) as s1 from usr where birthday between '1980-8-1' and '1980-9-2' group by id) a2,leve a3,grade a4 where a1.id = a2.id and a1.id=a3.id and a3.gradecode =a4.gradecode
(select id,sum(amount) as s1 from usr group by id) a1,
(select id,sum(amount) as s1 from usr where birthday between '1980-8-1' and '1980-9-2' group by id) a2,
(select id,gradename from level,grade where level.gradecode = grade.gradecode) a3 where a1.id = a2.id and a1.id=a3.id
level中id也不是唯一的,但是Grade中的gradecode是唯一的
我想查询的是该账号在一定时间内的金额汇总和他所有的金额总数,谢谢
这样写也可以:
select a1.id,a1.s1,a2.s2,a4.gradename from
(select id,sum(amount) as s1 from usr group by id) a1,
(select id,sum(amount) as s1 from usr where birthday between '1980-8-1' and '1980-9-2' group by id) a2,level a3,grade a4
where a1.id = a2.id and a1.id=a3.id and a3.gradecode =a4.gradecode
(select id,sum(amount) as s1 from usr group by id) a1,
(select id,sum(amount) as s1 from usr where birthday between '1980-8-1' and '1980-9-2' group by id) a2,leve a3,grade a4
where a1.id = a2.id and a1.id=a3.id and a3.gradecode =a4.gradecode