如何使用许多相同的选择优化sql查询？

e0bqpujr 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(325)

我已经创建了这个查询，但我认为它可以优化很多。。。但我不知道怎么做？
这是一个查询，它选择了一个学生尚未完成的任务，也选择了计划任务的数量，以及他请求帮助的任务数量。
后来我不想数数，而是要他/她请求帮助的答案的整行，但现在我开始用数数开始，因为这似乎更容易。
有没有人可以告诉我如何优化这个，或者我在哪里可以学到这个？

SELECT
    gu.user_id,
    notComplete.count AS notComplete,
    planned.count AS planned,
    helpNeeded.need AS helpNeeded
FROM
    `groups_users` gu
LEFT JOIN(
    SELECT
        a.created_by AS user_id,
        COUNT(1) AS 'count'
    FROM
        `answers` a
    WHERE
        a.deleted_at IS NULL AND a.completed_at IS NULL AND a.checked_at IS NULL
    GROUP BY
        a.created_by
) AS notComplete
ON
    notComplete.user_id = gu.user_id
LEFT JOIN(
    SELECT
        a.created_by AS user_id,
        COUNT(1) AS 'count'
    FROM
        `answers` a
    WHERE
        a.deleted_at IS NULL
    GROUP BY
        a.created_by
) AS planned
ON
    planned.user_id = gu.user_id
LEFT JOIN(
    SELECT
        a.created_by AS user_id,
        COUNT(1) AS need
    FROM
        `answers` a
    WHERE
        a.deleted_at IS NULL AND a.requested_help_at IS NOT NULL
    GROUP BY
        a.created_by
) AS helpNeeded
ON
    helpNeeded.user_id = gu.user_id
INNER JOIN `users` u ON
    u.id = gu.user_id AND u.type = 'student'
WHERE
    gu.group_id = 213

sql mysql performance

来源：https://stackoverflow.com/questions/49812881/how-to-optimise-a-sql-query-with-many-of-the-same-selections

1条答案

按热度按时间

szqfcxe21#

请尝试以下操作

SELECT
  gu.user_id,
  SUM(IF(a.deleted_at IS NULL AND a.completed_at IS NULL AND a.checked_at IS NULL,1,0)) AS notComplete,
  SUM(IF(a.deleted_at IS NULL,1,0)) AS planned,
  SUM(IF(a.deleted_at IS NULL AND a.requested_help_at IS NOT NULL,1,0)) AS helpNeeded
FROM `groups_users` gu
JOIN `users` u ON u.id = gu.user_id
LEFT JOIN `answers` a ON a.created_by = gu.user_id
WHERE gu.group_id = 213
  AND u.type = 'student'
GROUP BY gu.user_id

尝试下面的第二个问题

SELECT
  gu.user_id,
  IF(a.deleted_at IS NULL AND a.completed_at IS NULL AND a.checked_at IS NULL,1,0) AS notComplete,
  IF(a.deleted_at IS NULL,1,0) AS planned,
  IF(a.deleted_at IS NULL AND a.requested_help_at IS NOT NULL,1,0) AS helpNeeded,
  a.* -- full answer row
FROM `groups_users` gu
JOIN `users` u ON u.id = gu.user_id
LEFT JOIN `answers` a ON a.created_by = gu.user_id
LEFT JOIN `answer_plannedday` ap ON ap.answer_id = a.id
LEFT JOIN `plannedday` p ON p.id = ap.planned_day_id
WHERE gu.group_id = 213
  AND u.type = 'student'  
  AND STR_TO_DATE(CONCAT(p.year, '-', p.month, '-', p.day), '%Y-%m-%d') BETWEEN STR_TO_DATE('2016-12-01', '%Y-%m-%d') AND STR_TO_DATE('2018-04-13', '%Y-%m-%d')

如果您只想获得notcomplete的详细信息，您可以将所有必要的条件放入where

SELECT
  gu.user_id,
  a.* -- full answer row
FROM `groups_users` gu
JOIN `users` u ON u.id = gu.user_id
LEFT JOIN `answers` a ON a.created_by = gu.user_id
LEFT JOIN `answer_plannedday` ap ON ap.answer_id = a.id
LEFT JOIN `plannedday` p ON p.id = ap.planned_day_id
WHERE gu.group_id = 213
  AND u.type = 'student'  
  AND STR_TO_DATE(CONCAT(p.year, '-', p.month, '-', p.day), '%Y-%m-%d') BETWEEN STR_TO_DATE('2016-12-01', '%Y-%m-%d') AND STR_TO_DATE('2018-04-13', '%Y-%m-%d')
  -- you say here that you want to get only these rows
  AND a.deleted_at IS NULL AND a.completed_at IS NULL AND a.checked_at IS NULL

赞(0）回复(0）举报 2021-06-21

我来回答

如何使用许多相同的选择优化sql查询？

1条答案

相关问题

热门标签

最新问答