时间戳

首页 > 解决方案 > 从联接查询中选择 SUM 字段和其他列

mysql - 从联接查询中选择 SUM 字段和其他列

问题描述

我有这些桌子。

  1. 级别表级别（id，名称）
  2. 学生表学生（id、name、level_id）
  3. 付款类型表payment_types (id, name) 例如 Tuition Feee, Uniform
  4. 付款表付款（id、student_id、payment_type_id、金额、created_at）
  5. 级别支付表payment_fees (level_id, payment_type_id, amount) 例如特定级别的免学费金额

我需要一个查询，它将获取指定级别（level_id）中所有学生的总付款详细信息，并显示每个学生的付款费用（使用学生 level_id 查找payment_fees表

我有这个查询：

select students.id, students.name name, levels.name class, MAX(payments.created_at) date, SUM(payments.amount) amount 
from `students` 
left join `payments` on `students`.`id` = `payments`.`student_id` and `payments`.`payment_type_id` = '1'
inner join `levels` on `levels`.`id` = `students`.`level_id`
group by `students`.`id`

这很好用 这是 GUI 演示文稿（在页面按钮上检查现实生活中的查询），这是 json 响应，但我还需要一列总计，即学生级别的支付费用（来自 payment_fees金额列）并指定payment_type_id 以便我可以将总和与总数进行比较，并了解学生是否已完成付款。

我已经尝试过了，但是遇到了这个错误：

select students.id, students.name name, levels.name class, MAX(payments.created_at) date, SUM(payments.amount) amount, payment_fees.amount as total 
from `students` 
left join `payments` on `students`.`id` = `payments`.`student_id` and `payments`.`payment_type_id` = '1'
inner join `levels` on `levels`.`id` = `students`.`level_id`
inner join 'payment_fees' on 'payment_fees.level_id' = 'students.level_id'

但它不起作用......它给出了这个错误：这是错误响应

SQLSTATE[42000]: 
Syntax error or access violation: 1055 Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'schooladminer.payment_type_fees.amount' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

标签： mysql

解决方案

您可以使用子查询来分隔事物 

select T3.student_name,T3.Level_name,T3.payment_name,T3.payment as student_payment,created_at,

    T4.payment_name as Level_payment,LevelPayment from 
    (
    select student_name,Level_name,payment_name,sum(amount) as payment,max(created_at) as created_at from
    (
    select S.name as student_name,S.id as student_id,
    L.name as Level_name,L.level_id from students S
    left join levels L
    on S.level_id=L.id
    )T1
    left join 
    (
    select PT.name as payment_name,id, student_id, payment_type_id, amount, created_at  
    from payments P
    left join payment_types PT
    on P.payment_type_id=PT.id
    ) T2
    on T1.student_id=T2.student_id
    group by student_name,Level_name,payment_name,T1.student_id
    ) T3
    left join
    (
    select PT.name as payment_name,L.name as Level_name,l.level_id, payment_type_id, sum(amount) as LevelPayment from payment_fees PF
    left join
    payment_types PT
    on PF.payment_type_id=PT.id
    left join levels L on PF.level_id=L.id
    group by PT.name,L.name,L.level_id,payment_type_id
    ) T4 on T3.Level_name=T4.Level_name

推荐阅读