SQL连接三个表格,使用选定的日期和区域进行计数和筛选。

huangapple go评论65阅读模式
英文:

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 = &#39;1&#39; AND tg.status = 1 AND t.tripdate &gt;= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) AND t.tripdate &lt;= 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 &gt;= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY)
                    AND t.tripdate &lt;= 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.

huangapple
  • 本文由 发表于 2023年6月5日 12:07:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/76403451.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定