Как я могу получить строку с количеством всех связанных строк?

У меня есть структура таблицы, как это:

// question_and_answer
+----+-----------+------------+
| id |   title   | related_id |
+----+-----------+------------+
| 1  | q1        | NULL       |
| 2  |           | 1          |
| 3  |           | 1          |
| 4  | q2        | NULL       |
| 5  |           | 1          |
| 6  |           | 4          |
| 7  |           | 1          |
| 8  | q3        | NULL       |
| 9  |           | 8          |
| 10 |           | 4          |
+----+-----------+------------+

// votes
+----+---------+
| id | post_id |
+----+---------+
| 1  | 2       |
| 2  | 2       |
| 3  | 1       |
| 4  | 6       |
| 5  | 2       |
| 6  | 1       |
| 7  | 8       |
+----+---------+

Мне нужно получить три вещи:

заголовок одной строки (для данного идентификатора)
количество связанных строк
количество его голосов

Я могу получить их тремя отдельными запросами:

// Assuming id = 1 

SELECT title FROM question_and_answer WHERE id = 1;

SELECT count(a.*) FROM question_and_answer q 
LEFT JOIN question_and_answer a ON q.id = a.related_id WHERE q.id = 1; 

SELECT count(v.*) FROM question_and_answer qa
LEFT JOIN votes v on qa.id = v.post_id
WHERE qa.id = 1;

Ожидаемый результат таков:

// Assuming id = 1 
+-------+-------------+-----------+
| title | answers_num | votes_num |
+-------+-------------|-----------+
| q1    | 4           | 2         |
+-------+-------------+-----------+

// Assuming id = 2
+-------+-------------+-----------+
| title | answers_num | votes_num |
+-------+-------------|-----------+
|       | 0           | 3         |
+-------+-------------+-----------+

Любая идея, как я могу получить это с помощью одного запроса?

mysql sql

07.04.2019 09:16

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков

В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...

Как построить CRUD-приложение в Laravel

Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...

Освоение PHP и управление базами данных: Создание собственной СУБД - часть II

В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...

Документирование API с помощью Swagger на Springboot

В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .

Роли и разрешения пользователей без пакета Laravel 9

Этот пост изначально был опубликован на techsolutionstuff.com .

Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM

В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.

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

Ответы 1

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

Для получения количества строк с ненулевыми значениями вы можете использовать count и левое соединение Для одного идентификатора, например: 1

SELECT qa.title, t1.answers_num,  t2.votes_num
FROM question_and_answer qa 
LEFT  JOIN (
  SELECT qa.title, count(c.related_id) answers_num
  FROM question_and_answer qa 
  LEFT  JOIN question_and_answer c ON qa.id = c.related_id
  GROUP BY qa.title
) t1  on t1.title = qa.title 
LEFT JOIN (
    SELECT qa.title, count(v.post_id) votes_num
    FROM question_and_answer qa 
    LEFT  JOIN votes v on qa.id = v.post_id
    GROUP BY qa.title
) t2 ON t2.title = qa.title 
WHERE qa.id = 1;

Для всех идентификаторов

SELECT qa.title, t1.answers_num,  t2.votes_num
FROM question_and_answer qa 
LEFT  JOIN (
  SELECT qa.title, count(c.related_id) answers_num
  FROM question_and_answer qa 
  LEFT  JOIN question_and_answer c ON qa.id = c.related_id
) t1  on t1.title = qa.title 
LEFT JOIN (
    SELECT qa.title, count(v.post_id) votes_num
    FROM question_and_answer qa 
    LEFT  JOIN votes v on qa.id = v.post_id
) t2 ON t2.title = qa.title

для obatin количество различных не нулевых значений, которые вы могли бы использовать

Для одного идентификатора, например: 1

SELECT qa.title, c.count(distinct related_id) answers_num
,  v.count(distinct post_id) votes_num
FROM question_and_answer qa 
LEFT  JOIN question_and_answer c ON qa.id = c.related_id
LEFT  JOIN votes v on qa.id = v.post_id
WHERE qa.id = 1;

Для всех идентификаторов

SELECT qa.title, c.count(distinct related_id) answers_num
,  v.count(distinct post_id) votes_num
FROM question_and_answer qa 
LEFT  JOIN question_and_answer c ON qa.id = c.related_id
LEFT  JOIN votes v on qa.id = v.post_id
WHERE related_id is null  
group by qa.title;

Он выдает FUNCTION c.count does not exist., я думаю, c.count(related_id) недействителен в синтаксисе MySQL.

— 07.04.2019 09:51

Сейчас работает (нет синтаксической ошибки). Но результат answers_num и votes_num не совпадает с ожидаемым числом. В реальном наборе данных ожидаемое число для votes_num равно 2, но возвращается 4. Отметил, что count(distinct v.post_id) votes_num возвращает 1.

— 07.04.2019 10:05

ответ обновлен .. надеюсь, это то, что вы ищете

— 07.04.2019 10:31

Спасибо.. но ваша первая версия была почти правильной, просто должно быть так: SELECT qa.subject, count(distinct c.id) answers_num, count(distinct v.id) votes_num ... Дело в том, что distinct c.id и distinct v.id внутри count().

— 07.04.2019 10:34

добавляется как с отличным, так и без зависимости от типа ненулевого значения, которое вы ищете

— 07.04.2019 10:43

07.04.2019 09:26