@easy_katka

Как оптимизировать данный sql запрос?

SELECT COUNT(*) as `all_ct`, (SELECT COUNT(*) FROM `requests` WHERE `statut` = 'new') as `ct_new`, 
(SELECT COUNT(*) FROM `requests` t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'analyze' and t2.id_client IS NOT NULL) as `ct_analyze`, 
(SELECT COUNT(*) FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'analyzed' and t2.id_client IS NOT NULL) as`ct_analyzed`, 
(SELECT COUNT(*) FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'sale' and t2.id_client IS NOT NULL) as `ct_sale`, 
(SELECT COUNT(*) FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'meeting' and t2.id_client IS NOT NULL) as `ct_meeting`, 
(SELECT COUNT(*) FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'portfolio' and t2.id_client IS NOT NULL) as`ct_portfolio`,
(SELECT COUNT(*) FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t1.`statut` = 'canceled' and t2.id_client IS NOT NULL) as `ct_canceled`  
FROM requests t1 LEFT JOIN clients t2 ON t2.id_client = t1.id_client WHERE t2.id_client IS NOT NULL

долговато работает, проверятся если клиент есть то считаем запросы, может быть только запрос без клиента вот почему нужно условие проверки
  • Вопрос задан
  • 61 просмотр
Пригласить эксперта
Ответы на вопрос 1
LaRN
@LaRN
Senior Developer
Можно попробовать кучу однотипных запросов заменить на один с использованием группировки, примерно так:

SELECT SUM(CASE WHEN t1.`statut` = 'analyze'   
THEN 1 ELSE 0 END) as `ct_analyze`,
       SUM(CASE WHEN t1.`statut` = 'analyzed'   
THEN 1 ELSE 0 END) as `ct_analyzed`,
... ДРУГИЕ ПОЛЯ... 
   FROM `requests` t1 
   LEFT JOIN clients t2 
          ON t2.id_client = t1.id_client  
WHERE tnd t2.id_client IS NOT NULL
GROUP BY t1.id_client
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы