gpt4 book ai didi

MYSQL 查询帮助 JOIN 表

转载 作者:行者123 更新时间:2023-11-29 23:01:10 26 4
gpt4 key购买 nike

我在查询时遇到了一些问题,希望有人可以指导我解决这个问题?我想这应该是可以实现的。我有两张 table 。表 A 存储小时数,表 B 存储驾驶时间。我正在努力让所有每天工作超过 6 小时的人都能够做到这一点。但我还需要获取特定日期的驾驶时间,他们一天的驾驶时间超过 6 小时。我希望我说得有道理。这是我现在正在使用的查询,正在努力让每天工作超过 6 小时的每个人也能获得分钟数。行驶时间存储在名为 ies_mileage 的表 B 中,字段为持续时间。我尝试了几件事但没有成功。

SELECT 
wk_date,
full_name,
SUM(hours) AS HOURS
FROM `ies_weekly`
LEFT JOIN infant_specialist
ON ies_weekly.is_id = infant_specialist.is_id
WHERE wk_date BETWEEN '2015-01-16' AND '2015-01-31'
GROUP BY
DAY(wk_date),
ies_weekly.is_id
HAVING SUM(hours) > 6
ORDER BY full_name ASC

这是有效的,当我尝试添加持续时间时,总数变得困惑并且不再准确。这是我尝试过的

SELECT 
wk_date,
full_name,
SUM(hours) AS HOURS,
SUM(duration) AS minutes
FROM
`ies_weekly`
LEFT JOIN infant_specialist
ON ies_weekly.is_id = infant_specialist.is_id
LEFT JOIN ies_mileage
ON ies_weekly.wk_date = ies_mileage.mil_date
AND ies_weekly.is_id = ies_mileage.ies_id
WHERE wk_date BETWEEN '2015-01-16' AND '2015-01-31'
GROUP BY
DAY(wk_date),
ies_weekly.is_id
HAVING SUM(hours) > 6
ORDER BY full_name ASC

最佳答案

SELECT full_name, wk_date, SUM(hours) HOURS,
(Select Sum(duration)
From ies_mileage
Where mil_date = w.wk_date
and ies_id = w.is_id) DrivingTime
FROM ies_weekly w
LEFT JOIN infant_specialist i
ON i.is_id = w.is_id
WHERE wk_date BETWEEN '2015-01-16' AND '2015-01-31'
GROUP BY DAY(wk_date), w.is_id
HAVING SUM(hours) > 6
ORDER BY full_name ASC

关于MYSQL 查询帮助 JOIN 表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28485813/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com