有两个表,其中project表相关字段定义如下:
id int,主键
amount int,表示项目总金额;另一个表payment 相关字段定义如下:
id int ,主键
project_id int, 即project表的id,但没有外键关联
type byte, 1表示收款,2表示付款
money int,表示收付款金额 一个项目project对应多条payment记录,比如说一个id为12的project在payment里同时有2条收款记录,3条付款记录,只要payment记录里project_id=12即可。问题来了,我想用一条sql语句查出来项目总数,所有项目总金额(sum(amount)),以及这些项目总支付金额和总收款金额,该怎么写?
id int,主键
amount int,表示项目总金额;另一个表payment 相关字段定义如下:
id int ,主键
project_id int, 即project表的id,但没有外键关联
type byte, 1表示收款,2表示付款
money int,表示收付款金额 一个项目project对应多条payment记录,比如说一个id为12的project在payment里同时有2条收款记录,3条付款记录,只要payment记录里project_id=12即可。问题来了,我想用一条sql语句查出来项目总数,所有项目总金额(sum(amount)),以及这些项目总支付金额和总收款金额,该怎么写?
In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'pms.p1.id';
this is incompatible with sql_mode=only_full_group_by该怎么破?
select count(pro.id), sum(pro.amount),
sum(rec.money), sum(pay.money) from project pro
left join
(select project_id, sum(money) money from payment where type=1 group by project_id) rec on rec.project_id=pro.id
left join
(select project_id, sum(money) money from payment where type=2 group by project_id) pay on pay.project_id=pro.id;
SQL如下:
select
count(DISTINCT a.id) 项目总数,
sum(a.amount) 项目总金额,
sum(case when b.type = 1 then b.money else 0 end) 收款总金额,
sum(case when b.type = 2 then b.money else 0 end) 付款总金额
from
project a left join payment b on a.id = b.project.id
SQL如下:
select
count(DISTINCT a.id) 项目总数,
sum(a.amount) 项目总金额,
sum(case when b.type = 1 then b.money else 0 end) 收款总金额,
sum(case when b.type = 2 then b.money else 0 end) 付款总金额
from
project a left join payment b on a.id = b.project.id
6楼这位大哥你好,你的代码我试了下,项目总金额不对,能帮忙看看吗?不过你的语句让我明白了case when 的的用法,谢谢
SQL如下:
select
count(DISTINCT a.id) 项目总数,
sum(a.amount) 项目总金额,
sum(case when b.type = 1 then b.money else 0 end) 收款总金额,
sum(case when b.type = 2 then b.money else 0 end) 付款总金额
from
project a left join payment b on a.id = b.project.id
6楼这位大哥你好,你的代码我试了下,项目总金额不对,能帮忙看看吗?不过你的语句让我明白了case when 的的用法,谢谢sorry,昨天把这个总项目的金额给整错了,原因是两张表join的时候会产生冗余。所以直接sum的总和会比实际的大
现在修改如下(由于没有数据,请自行测试):
select
count(DISTINCT a.id) 项目总数,
c.sum1 项目总金额,
sum(case when type = 1 then b.money else 0 end) 收款总金额,
sum(case when type = 2 then b.money else 0 end) 付款总金额
from
project a left join payment b on a.id = b.project.id
left join (select sum(amount) sum1,id id1 from payment) c on c.id1 = a.id 或者
select
count(DISTINCT a.id) 项目总数,
(select sum(c.amount) from payment c) 项目总金额,
sum(case when type = 1 then b.money else 0 end) 收款总金额,
sum(case when type = 2 then b.money else 0 end) 付款总金额
from
project a left join payment b on a.id = b.project.id
另外大概看了下,项目总金额计算也是错的。因为只有project里有amount,代表项目总金额,但是项目收款是有阶段的,因此理论上是项目完成后payment里type=1(表示收款)并且project_id=project.id的所有记录的sum(money)等于project里此id的amount。表结构不复杂,我在1楼说的很明白了,请老司机费心再帮忙看看,谢谢了。