英文:
COMPLEX SQL join 3 table with count and filter using selected date and area
问题
我想制作一个列表指南,其中包含基于当前月份每个地区的trips指南计数,以便更容易首先分配工作给计数较少的指南,以便在选择的日期/月份中每个地区的工作数量平等。即使没有完成任何trips指南,它也会显示该地区的所有指南。
#guide
:id(编号),
name(姓名),
area(地区)
#trips:
id(编号),
tripdate(旅行日期),
area(地区),
#tripguide:
id(编号),
trip_id(旅行编号),
guide_id(指南编号),
status(状态),
期望的结果
name(姓名) work(工作)
guideA 2
guideB 1
guideC 0
我的查询是
SELECT g.id, g.name, g.area, COUNT(tg.guide_id) AS work
FROM tripguide AS tg
LEFT JOIN trips AS t ON tg.trip_id = t.id
LEFT JOIN guide AS g ON tg.guide_id = g.id
WHERE t.area = '1' AND tg.status = 1 AND t.tripdate >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) AND t.tripdate <= LAST_DAY(CURRENT_DATE)
GROUP BY g.id;
问题是没有trips指南记录的指南不会显示。
在这里是SQL fiddle
感谢建议和答案。
英文:
i want to make list guide with count of their tripguide based on area per current month
so it will easier to assign work to guide with less count first, so theres equality on number of jobs per area in selected date/month.
it will show all guide in the area even with zero tripguide done.
#guide
:id,
name,
area
#trips:
id,
tripdate,
area,
#tripguide:
id,
trip_id,
guide_id,
status,
desired result
name work
guideA 2
guideB 1
guideC 0
my query is
SELECT g.id, g.name,g.area,COUNT(tg.guide_id) AS work
FROM tripguide AS tg
LEFT JOIN trips AS t ON tg.trip_id = t.id
LEFT JOIN guide AS g ON tg.guide_id = g.id
WHERE t.area = '1' AND tg.status = 1 AND t.tripdate >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) AND t.tripdate <= LAST_DAY(CURRENT_DATE)
GROUP BY g.id;
the problem is guide with no record on tripguide not show.
here the SQL fiddle
Thank you for the suggestion & answer
答案1
得分: 1
你正在选择 tripguide 行,并与其他表进行外连接。这意味着你甚至会显示没有关联导游或行程的 tripguide 行。这样的行甚至不存在。你想选择 导游,即使它们没有 tripguide/行程。
以下是已更正的查询:
SELECT g.id, g.name, g.area, COUNT(t.id) AS work
FROM guide AS g
LEFT JOIN tripguide AS tg ON tg.guide_id = g.id
AND tg.status = 1
LEFT JOIN trips AS t ON t.id = tg.trip_id
AND t.area = 1
AND t.tripdate >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY)
AND t.tripdate <= LAST_DAY(CURRENT_DATE)
GROUP BY g.id
ORDER BY work DESC, g.id;
这将显示所有导游以及他们在区域 1 的状态-1 行程。即使导游的区域不是区域 1。如果你想将其限制为区域 1 的导游,你当然需要添加 WHERE g.area = 1
。
英文:
You are selecting tripguide rows, outer joining the other tables. This means you would even show tripguide rows that have no associated guide or trip. Such rows don't even exist. You want to select guides, even when they have no tripguides/trips.
Here is the corrected query:
SELECT g.id, g.name, g.area, COUNT(t.id) AS work
FROM guide AS g
LEFT JOIN tripguide AS tg ON tg.guide_id = g.id
AND tg.status = 1
LEFT JOIN trips AS t ON t.id = tg.trip_id
AND t.area = 1
AND t.tripdate >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY)
AND t.tripdate <= LAST_DAY(CURRENT_DATE)
GROUP BY g.id
ORDER BY work DESC, g.id;
This shows all guides along with their status-1 trips in area 1. Even if the guide's area is not area 1. If you want to limit this to area-1 guides, you'll have to add WHERE g.area = 1
of course.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论