Нужен запрос для join четырех таблиц с некоторыми условиями?

У меня есть следующие четыре таблицы:

1) mls_user 2) mls_category 3) Bonus_point 4) mls_entry

Значения в таблице mls_user выглядят следующим образом:

*-------------------------*
| id | store_id | name    |
*-------------------------*
| 1  |    101   | sandeep |
| 2  |    101   | gagan   |
| 3  |    102   | santosh |
| 4  |    103   | manu    |
| 5  |    101   | jagveer |
*-------------------------*

Значения в таблице mls_category выглядят следующим образом:

*---------------------------------*
| cat_no |  store_id |  cat_value |
*---------------------------------*
|   20   |   101     |     1      |
|   21   |   101     |     4      |
|   30   |   102     |     1      |
|   31   |   102     |     2      |
|   40   |   103     |     1      |
|   41   |   103     |     1      |
*---------------------------------*

В таблице Bonus_point значения указаны ниже:

*-----------------------------------*
| user_id | store_id |  bonus_point |
|   1     |   101    |     10       |
|   4     |   101    |      5       |
*-----------------------------------*

Значения в таблице mls_entry выглядят следующим образом:

*-------------------------------------------------------*
| user_id | store_id |  category |  distance |  status  |
*-------------------------------------------------------*
|    1    |   101    |    20     |    10     | Approved |
|    1    |   101    |    21     |    40     | Approved |
|    1    |   101    |    20     |    10     | Approved |
|    2    |   101    |    20     |     5     | Approved |
|    3    |   102    |    30     |    10     | Approved |
|    3    |   102    |    31     |    80     | Approved |
|    4    |   101    |    20     |    15     | Approved |
*-------------------------------------------------------*

И я хочу, чтобы результат был ниже:

*--------------------------------------------------*
|  user name  | Points | bonus Point | Total Point |    
*--------------------------------------------------*
|   Sandeep   |   30   |     10      |    40       |
|   Santosh   |   30   |     0       |    30       |
|   Manu      |   15   |     5       |    20       |
|   Gagan     |   5    |     0       |     5       |
|   Jagveer   |   0    |     0       |    0        |
*--------------------------------------------------*

Рассказываю расчет того, как придут баллы для пользователя Sandeep. Очки = ((10 + 10) / 1 + 40/4) = 30 Здесь 1 и 4 - значение cat, полученное из mls_category.

Я использую приведенный ниже код для конкретного пользователя, но когда я

 SELECT sum(t1.totald/c.cat_value) as total_distance 
     FROM mls_category c 
       join (
         select sum(distance) totald, user_id, category 
           FROM mls_entry 
           WHERE user_id=1 AND store_id='101' AND status='approved' 
           group by user_id, category) t1 on c.cat_no = t1.category

Я создал таблицы в сети для проверки ДЕМО

0
0
27
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Для вычисления очков (кроме бонусных) требуется отдельное соединение между таблицами mls_entry и mls_category. Я бы сделал это в отдельном подзапросе, а затем присоединил бы к более крупному запросу.

Вот один из подходов:

SELECT
    u.name,
    COALESCE(t1.points, 0) AS points,
    COALESCE(b.bonus_point, 0) AS bonus_points,
    COALESCE(t1.points, 0) + COALESCE(b.bonus_point, 0) AS total_points
FROM mls_user u
LEFT JOIN
(
    SELECT e.user_id, 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
) t1
    ON u.id = t1.user_id
LEFT JOIN bonus_point b
    ON u.id = b.user_id
ORDER BY
    total_points DESC;

Это результат, который я получаю из приведенного выше запроса в настроенной вами демонстрации:

enter image description here

Результат не совпадает в точности, потому что у вас (возможно) есть опечатка в данных Santosh в вашем вопросе, или в противном случае ожидаемый результат в вашем вопросе содержит опечатку.

Отличный братан, ты мой герой. Только один вопрос, который я хочу упорядочить по убыванию по total_points, означает, что сначала будет Santosh, а затем sandeep. Итак, где я разместил заказ

Santosh Khatri 31.10.2018 13:18

@SantoshKhatri Используйте ORDER BY total_points DESC. LOL, только что понял, что ты Сантош!

Tim Biegeleisen 31.10.2018 13:20

Большое спасибо. ты мой супергерой. ты спасаешь мне жизнь.

Santosh Khatri 31.10.2018 13:25

еще один вопрос предполагает, что я хочу отображать записи в соответствии с store_id означает, что я хочу отображать записи только store_id '101', мои новые записи будут отображаться только для Sandeep, Santosh и jagveer, потому что их store_id равен 101, тогда я помещаю условие.

Santosh Khatri 31.10.2018 14:40

Другие вопросы по теме