Соедините две таблицы и подсчитайте сумму
У меня три стола
Таблица 1
c_id c_name status p_id created_at
1 john 1 79 2016-04-13 10:36:44
2 smith 1 79 2016-04-13 14:25:57
3 phil 1 76 2016-04-18 18:06:21
4 leo 1 76 2016-04-18 15:51:41
5 craig 1 79 2016-04-20 10:44:17
...
Таблица 2
p_id p_name
75 test1
76 test2
77 test3
78 test4
79 test5
...
таблица 3
id c_id
1 1
2 1
3 3
4 4
5 4
Мне нужен результат:
period p_id p_name total_count active_count cance_count no_of_occur
2016-04-13 79 test5 2 2 0 2
2016-04-18 76 test2 2 2 0 2
2016-04-20 79 test5 1 1 0 1
...
Для кода, который я написал quert, но он дает мне неправильный счет
SELECT
DATE(DATE_ADD(created_at, INTERVAL 19800 SECOND)) AS period, table1.p_id,
MIN(mdcpev.value) AS product_name,
COUNT(table1.c_id) AS total_count,
SUM(status = 1) AS active_count,
SUM(status = 2) AS cancel_count,
COUNT(table3.id) AS no_of_occur,
FROM table1
INNER JOIN table2 AS mdcpev ON mdcpev.p_id = p_id
LEFT JOIN table3 AS mdspo ON mdspo.c_id = tabe1.c_id
GROUP BY DATE(DATE_ADD(created_at, INTERVAL 19800 SECOND)),p_id
РЕДАКТИРОВАТЬ
мой текущий результат запроса
period p_id p_name total_count active_count cance_count no_of_occur
2016-04-13 79 test5 3 3 0 3
2016-04-18 76 test2 3 3 0 2
2016-04-20 79 test5 1 1 0 1
Здесь я получил active_count 3 что не так должно быть 2 и no_of_occurдолжно быть 2 и в настоящее время отображается 3
Кто-нибудь может подсказать мне, что не так в этом запросе?
Спасибо
Решение
Попробуйте этот запрос, он будет работать.
SELECT
DATE_FORMAT(table1.created_at,'%Y-%m-%d'),
table1.p_id,
table2.p_name,
COUNT(table1.c_id) AS total_count,
COUNT(CASE WHEN status = 1 THEN table1.c_id ELSE NULL end) AS active_count,
COUNT(CASE WHEN status = 2 THEN table1.c_id ELSE NULL end) AS cancel_count,
COUNT(table3.id) AS no_of_occur
FROM table1
JOIN table2
on table2.p_id=table1.p_id
JOIN table3
ON table3.c_id=table1.c_id
Group by DATE_FORMAT(table1.created_at,'%Y-%m-%d'),table1.p_id
Выход:
Я взял фиктивные данные как это от вопроса и попробовал в моей системе ..
Другие решения
SUM(status = 1) AS active_count, это всегда возвращает true (1) для всех условий. Таким образом, вы получаете неправильный вывод.
Я надеюсь, что этот запрос будет работать. Пожалуйста, проверьте.
SELECT
DATE_FORMAT(table1.created_at,'%Y-%m-%d'),
table1.p_id,
table2.p_name,
COUNT(table1.c_id) AS total_count,
SUM(CASE WHEN status= 1 THEN 1 ELSE 0 END) AS active_count,
SUM(CASE WHEN status= 2 THEN 1 ELSE 0 END) AS cancel_count,
COUNT(table3.id) AS no_of_occur
FROM table1
JOIN table2
on table2.p_id=table1.p_id
JOIN table3
ON table3.c_id=table1.c_id
Group by DATE_FORMAT(table1.created_at,'%Y-%m-%d'),table1.p_id
Причиной различия является соединение с таблицей 3, так как у вас есть 1 и 4 дважды в столбце c_id, и они будут дублировать записи.
Я бы изменил счет следующим образом:
COUNT(distinct table1.c_id) AS total_count,
Count(distinct case when status = 1 then table1.c_id else null end) AS active_count,
Count(distinct case when status = 2 then table1.c_id else null end) AS cancel_count,
COUNT(distinct table3.id) AS no_of_occur,
Различия в подсчете гарантируют, что учитываются только разные значения. Тем не менее, у вас все еще будет проблема с no_of_occur. Если это основано на table3.id, то вы все равно получите 3 для p_id 79 в 1-й записи, потому что 1-я запись в table1 относится к 2 записям в table3, а вторая запись в table1 относится к 3-ей записи в table3. Итак, я считаю, что ваши ожидания либо неверны, либо ваш расчет no_of_occur не должен быть вообще привязан к таблице 3. Но в этом случае вам даже не нужно table3 в запросе. Это только вы можете ответить.