mysql - 如何将表记录与SQL表中的某些条件结合在一起？

我在数据库中有六个表，所有表都是相对的，希望在一个表中显示记录。
以下是我的桌子：
1）mls U商店

*----------------------------*
|   store_id |  store_title  |
*----------------------------*
|     1001   |  ajmar-jaipur |
|     1002   |  dwarka-delhi |
*----------------------------*

2）mls U类

*-------------------------------------------*
 | cat_no | store_id |  cat_value | cat_type |
 *-------------------------------------------*
 |  20    |   1001   |     1      | running  |
 |  21    |   1001   |     4      | cycling  |
 |  22    |   1002   |     1      | running  |
 |  23    |   1002   |     2      | swmining |
 *-------------------------------------------*

3）mls_点矩阵

*----------------------------------------*
| store_id | value_per_point |  maxpoint |
*----------------------------------------*
|   1001   |       1         |    10     |
|   1001   |       2         |    20     |
|   1002   |       1         |    20     |
|   1002   |       4         |    30     |
*----------------------------------------*

4）mls U用户

*--------------------------*
| id |  store_id |  name   |
*--------------------------*
|  1 |  1001     | sandeep |
|  2 |  1001     | jagveer |
|  3 |  1002     | gagan   |
*--------------------------*

5）加分

   *---------------------------------------------------*
   | user_id |  store_id |  bonus_points |  bonus_type |
   *---------------------------------------------------*
   |    1    |    1001   |     10        |   fixed     |
   |    3    |    1002   |      2        |     %       |
   *---------------------------------------------------*

6）mls U入口

*-------------------------------------------------------*
| user_id | store_id |  category |  distance |  status  |
*-------------------------------------------------------*
|   1     |   1001   |    20     |    10     | approved |
|   1     |   1001   |    21     |    40     | approved |
|   1     |   1001   |    20     |     5     | reject   |
|   2     |   1001   |    21     |    40     | approved |
|   3     |   1002   |    22     |    10     | approved |
|   3     |   1002   |    23     |    20     | approved |
*-------------------------------------------------------*

现在我要输出如下：

*-----------------------------------------------------------------------------------*
| Name    |  Entries   | Points Earned | Bonus Points | Total Points | Total Amount |
*-----------------------------------------------------------------------------------*
| Sandeep | running(1) |       20      |      10      |     30       |     60       |
|         | cycling(1) |               |              |              |              |
*-----------------------------------------------------------------------------------*
| Jagveer | cycling(1) |       10      |      0       |     10       |     10       |
*-----------------------------------------------------------------------------------*

我使用以下代码：

SELECT
    u.name,
    ROUND(COALESCE(t1.points, 0)) AS points,
    ROUND(COALESCE(b.bonus_points, 0)) AS bonus_points,
    ROUND(COALESCE(t1.points, 0) + COALESCE(b.bonus_points, 0)) AS total_points
FROM mls_user u
LEFT JOIN
(
    SELECT e.user_id, e.status, SUM(e.distance / c.cat_value) AS points
    FROM mls_entry e
    INNER JOIN mls_category c
        ON e.store_id = c.store_id AND e.category = c.cat_no
    GROUP BY e.user_id
    HAVING e.status='approved'
) t1
    ON u.id = t1.user_id
LEFT JOIN bonus_points b
    ON u.id = b.user_id
WHERE u.store_id = '1001'
ORDER BY
    total_points DESC

这个SQL查询给我赢得的点数、加分和总分，但是我找不到条目和总金额，它给我的Sandeep计算错误的点数，根据数据，一个条目被拒绝。所以应该是20，而不是25。
我的总数是Sandep30x2（它来自点矩阵）=60
与jagveer相同，jagveer 10X1=10的总量。
我在DEMO中创建了表

最佳答案

请尝试以下操作：

SELECT
    u.name,
    ROUND(COALESCE(t1.points, 0)) AS points,
    ROUND(COALESCE(b.bonus_points, 0)) AS bonus_points,
    ROUND(COALESCE(t1.points, 0) + COALESCE(b.bonus_points, 0)) AS total_points,
    ROUND(COALESCE(t1.points, 0) + COALESCE(b.bonus_points, 0)) * t1.countId as total_amount,
    group_concat(t1.EntriesConcat) as Entries

FROM mls_user u
LEFT JOIN
(
    SELECT e.user_id, e.status, SUM(e.distance / c.cat_value) AS points,
    concat(c.cat_type, '(',count(e.user_id), ')' ) as EntriesConcat,
    count(e.user_id) as countId -- it returns count of records according to group by part
    FROM mls_entry e
        INNER JOIN mls_category c
            ON e.store_id = c.store_id AND e.category = c.cat_no
    -- remove HAVING and use WHERE clause
    WHERE e.status='approved'
    GROUP BY e.user_id
) t1 ON u.id = t1.user_id
LEFT JOIN bonus_points b ON u.id = b.user_id
WHERE u.store_id = '1001'
ORDER BY total_points DESC

mysql的group_concat有助于在Group By

关于mysql - 如何将表记录与SQL表中的某些条件结合在一起？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53118812/