复杂的MySQL查询错误结果 [英] complex MySQL query wrong results

问题描述

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

I am trying to build complex mysql query but its returning wrong results...

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查询和表的预期结果...

Expected results using above sql query and tables...

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

推荐答案

我已经找到了解决方案，我将很多查询合并在一起，结果有些翻了一番.谢谢.

I have got the solution, i was joining lots of queries together and that's by some results are doubling. thanks.

这篇关于复杂的MySQL查询错误结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！