mysql - 复杂的MySQL查询错误结果

Question

我正在尝试构建复杂的 mysql 查询，但它返回了错误的结果...

SELECT
  b.name  AS batch_name,
  b.id    AS batch_id,

  COUNT(DISTINCT s.id)
    AS total_students,

  COALESCE( SUM(s.open_bal), 0 )
    AS open_balance,

  SUM(  COALESCE(i.reg_fee,   0)
      + COALESCE(i.tut_fee,   0)
      + COALESCE(i.other_fee, 0)
  ) AS gross_fee,

  SUM( COALESCE(i.discount, 0) )
    AS discount,

  COALESCE( SUM(s.open_bal), 0 )
    + SUM(  COALESCE(i.reg_fee,   0)
          + COALESCE(i.tut_fee,   0)
          + COALESCE(i.other_fee, 0)
      )
    - SUM( COALESCE(i.discount, 0) )
    AS net_payable,

  SUM(  COALESCE(r.reg_fee,   0)
      + COALESCE(r.tut_fee,   0)
      + COALESCE(r.other_fee, 0)
  ) AS net_recieved,

  ( COALESCE( SUM(s.open_bal), 0 )
    + SUM(  COALESCE(i.reg_fee,   0)
          + COALESCE(i.tut_fee,   0)
          + COALESCE(i.other_fee, 0)
      )
    - SUM(  COALESCE(i.discount,  0) )
  )
  - ( SUM(  COALESCE(r.reg_fee,   0)
          + COALESCE(r.tut_fee,   0)
          + COALESCE(r.other_fee, 0)
      )
    )
    AS balance_due

  FROM batches b
  LEFT JOIN students s ON s.batch      = b.id
  LEFT JOIN invoices i ON i.student_id = s.id
  LEFT JOIN recipts  r ON r.student_id = s.id

  WHERE s.inactive = 0
  GROUP BY b.name, b.id;

返回以下结果...

| batch_name | total_students  | open_bal | gross_fee | discount | net_payable | net_recieved | due_balance |
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+
|  MS        | 6               | 10000    | 0         | 0        | 10000       | 101000       | -91000      |
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+

批处理表

| id  | name |
+-----+------+
| 9   | Ms   |
+-----+------+

学生表

| id  | open_bal | batch | inactive |
+-----+----------+-------+----------+
| 44  | -16000   | 9     | 0        |
+-----+----------+-------+----------+
| 182 | 9000     | 9     | 0        |
+-----+----------+-------+----------+
| 184 | -36000   | 9     | 0        |
+-----+----------+-------+----------+
| 185 | 19000    | 9     | 0        |
+-----+----------+-------+----------+
| 186 | 9000     | 9     | 0        |
+-----+----------+-------+----------+
| 187 | 4000     | 9     | 0        |
+-----+----------+-------+----------+

发票表

| id   | student_id | reg_fee | tut_fee | other_fee | net_payable | discount |
+------+------------+---------+---------+-----------+-------------+----------+
|      |            |         |         |           |             |          |
+------+------------+---------+---------+-----------+-------------+----------+

以上学号不提供发票

收据表

| id   | student_id | reg_fee | tut_fee | other_fee | status     |
+------+------------+---------+---------+-----------+------------+
|  8   | 44         | 0       | 0       | 1500      |  confirmed |
+------+------------+---------+---------+-----------+------------+
|  277 | 44         | 0       | 50000   | 0         |  confirmed |
+------+------------+---------+---------+-----------+------------+
|  26  | 182        | 0       | 0       | 1500      |  confirmed |
+------+------------+---------+---------+-----------+------------+
|  424 | 182        | 0       | 15000   | 0         |  confirmed |
+------+------------+---------+---------+-----------+------------+
|  468 | 182        | 0       | 15000   | 0         |  confirmed |
+------+------------+---------+---------+-----------+------------+
|  36  | 185        | 0       | 0       | 1500      |  confirmed |
+------+------------+---------+---------+-----------+------------+
|  697 | 185        | 0       | 15000   | 0         |  confirmed |
+------+------------+---------+---------+-----------+------------+
|  66  | 187        | 0       | 0       | 1500      |  confirmed |
+------+------------+---------+---------+-----------+------------+

使用上述 sql 查询和表的预期结果...

| batch_name | total_students  | open_bal | gross_fee | discount | net_payable | net_recieved | due_balance |
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+
|  MS        | 6               | -11000   | 0         | 0        | 10000       | 101000       | -112000     |
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+

Answer 1

您仍然没有提供完整的信息 - 没有批处理表，甚至不存在的 recipts 表。无论如何，我假设我们不关心批处理表中的内容，假设它只是名称和 ID。您的收据表有同一名学生的多行。由于所有 JOIN，这应该会导致其他表也返回多行。因此，您 SUM() 多次值必须只求和一次，即 open_balance。这可能是问题出在哪里的线索，我想说你必须将你需要的信息从“收据表”移到子查询中，但我不确定你是否向我们展示了你的整个数据库。尝试从查询中删除收据表并再次检查结果。如果是这样，您应该看看接下来该怎么做，或者至少向我们提供更多信息。

编辑:查询应该是:

SELECT
  b.name  AS batch_name,
  b.id    AS batch_id,

  COUNT(DISTINCT s.id)
    AS total_students,

  COALESCE( SUM(s.open_bal), 0 )
    AS open_balance,

  SUM(  COALESCE(i.reg_fee,   0)
      + COALESCE(i.tut_fee,   0)
      + COALESCE(i.other_fee, 0)
  ) AS gross_fee,

  SUM( COALESCE(i.discount, 0) )
    AS discount,

  COALESCE( SUM(s.open_bal), 0 )
    + SUM(  COALESCE(i.reg_fee,   0)
          + COALESCE(i.tut_fee,   0)
          + COALESCE(i.other_fee, 0)
      )
    - SUM( COALESCE(i.discount, 0) )
    AS net_payable,
  SUM((SELECT SUM(COALESCE(receipts.reg_fee,   0)
      + COALESCE(receipts.tut_fee,   0)
      + COALESCE(receipts.other_fee, 0)) FROM receipts WHERE receipts.student_id = s.id))
  AS net_recieved,

  ( COALESCE( SUM(s.open_bal), 0 )
    + SUM(  COALESCE(i.reg_fee,   0)
          + COALESCE(i.tut_fee,   0)
          + COALESCE(i.other_fee, 0)
      )
    - SUM(  COALESCE(i.discount,  0) )
  ) 
  - SUM((SELECT SUM(COALESCE(receipts.reg_fee,   0)
          + COALESCE(receipts.tut_fee,   0)
          + COALESCE(receipts.other_fee, 0)) FROM receipts WHERE receipts.student_id = s.id)) 
    AS balance_due

  FROM batches b
  LEFT JOIN students s ON s.batch      = b.id
  LEFT JOIN invoices i ON i.student_id = s.id
  WHERE s.inactive = 0
  GROUP BY b.name, b.id;

这将对收据表中的学生数据求和，即使它在不止一行中，也只返回一行。删除与收据表的连接会删除其他表中的重复行，因此计算现在应该是正确的。

还有一件事 - 您在 WHERE 子句中有 s.inactive = 0，请确保它与此计算无关。

附言为什么你不知道什么是子查询而你最终写了这样的东西？