mysql - SELECT 语句子查询

Question

编辑:我需要其中减去直接和间接分钟数的总数。

我正在尝试将 M.Minutes 求和为别名“dminutes”。然后，再次求 M.mines 的总和，并减去具有“间接”列值的 M.mines(并为其指定“inminutes”别名)。但是，它显示为空，因此语法错误。有建议吗？

table = tasks
column = task_type


Example:
M.minutes total = 60 minutes
M. minutes (with "direct" task_type column value) = 50 minutes (AS dminutes)
M. minutes (with "indirect" task_type column value) = 10 minutes (AS inminutes)

SQL语句:

SELECT 
U.user_name,
SUM(M.minutes) as dminutes,
ROUND(SUM(M.minutes))-(SELECT (SUM(M.minutes)) from summary s WHERE ta.task_type='indirect') as inminutes
FROM summary S
JOIN users U ON U.user_id = S.user_id
JOIN tasks TA ON TA.task_id = S.task_id
JOIN minutes M ON M.minutes_id = S.minutes_id
WHERE DATE(submit_date) = curdate()
AND TIME(submit_date) BETWEEN '00:00:01' and '23:59:59'
GROUP BY U.user_name 
LIMIT 0 , 30

Answer 1

我认为这样的事情应该有效。

您可能需要稍微调整一下。

SELECT direct.duser_id, indirect.iminutes, direct.dminutes, 
    direct.dminutes - indirect.iminutes FROM
    (SELECT U.user_id AS iuser_id, SUM(M.minutes) AS iminutes
    FROM summary S
    JOIN users U 
    ON U.user_id = S.user_id
    JOIN minutes M 
    ON M.minutes_id = S.minutes_id
    JOIN tasks TA 
    ON TA.task_id = S.task_id
    WHERE TA.task_type='indirect'
    AND DATE(submit_date) = curdate()
    AND TIME(submit_date) BETWEEN '00:00:01' and '23:59:59'
    GROUP BY U.user_id) AS indirect
JOIN
    (SELECT U.user_id AS duser_id, SUM(M.minutes) AS dminutes
    FROM summary S
    JOIN users U 
    ON U.user_id = S.user_id
    JOIN minutes M 
    ON M.minutes_id = S.minutes_id
    JOIN tasks TA 
    ON TA.task_id = S.task_id
    WHERE TA.task_type='direct'
    AND DATE(submit_date) = curdate()
    AND TIME(submit_date) BETWEEN '00:00:01' and '23:59:59'
    GROUP BY U.user_id) AS direct
WHERE indirect.iuser_id = direct.duser_id