mysql - SUM() 计算行两次并显示双倍结果

Question

为什么要SUM()计算两次行并在此处显示实际结果的两倍？

在这里，我试图从每个学生 ( )的inv_idstatus表中计算具有 0 值的总行数。inv_id.s_id

它必须根据inv_lst4表中的行号显示，但这里显示。8

如果fee.id是，GROUP_BY那么它会显示实际的 SUM，但相同的学生 ID 开始重复。

请参阅小提琴- SQL Fiddle

数据库结构

班级

id  |   ttl
===========
1   |   One
2   |   Two

部分

id  |   ttl
===========
1   |   A
2   |   B

费用

id  |   ttl
===============
1   |   Annual
2   |   Monthly

学生

id  |   ttl |   cls |   sec
===========================
1   |   John|   1   |   1
2   |   Paul|   1   |   1
3   |   Rina|   2   |   1

sec_fee

id  |   c_id|   s_id|   f_id|   fee
===================================
1   |   1   |   1   |   1   |   1000
2   |   2   |   1   |   2   |   560

inv_id

id  |   s_id|   ft_id   |   status
==================================
1   |   1   |   1       |   0
2   |   1   |   2       |   0
3   |   1   |   3       |   0
4   |   1   |   4       |   0

mysql

    SELECT
        student.id, student.ttl AS stdt, 
        cls.ttl AS cls, 
        sec.ttl AS sec,
        GROUP_CONCAT(DISTINCT fee.id, '.', fee.ttl, '-', sec_fee.fee,'<br/>' ORDER BY sec_fee.f_id) AS amnt,
        SUM(inv_id.status=0) AS upad,
        SUM(inv_id.status=1) AS pad
    FROM
        student
    JOIN
        cls ON cls.id=student.cls
    LEFT JOIN
        sec ON sec.id=student.sec
    LEFT JOIN
        inv_id ON inv_id.s_id = student.id
    LEFT JOIN
        sec_fee ON sec_fee.c_id = student.cls
    LEFT JOIN
        fee ON fee.id = sec_fee.f_id
    WHERE
        cls.id = 1

标签： mysql