如何从mySQL中的组合表中获取列的总和？

I've been trying to write a mySQL-statement for the scenario below, but I just can't get it to work as intended. I would be very grateful if you guys could help me get it right!

我一直在尝试为下面的场景编写一个mySQL语句,但我无法让它按预期工作。如果你们能帮助我做对,我将非常感激!

I have two tables in a mySQL-database, event and route:

我在mySQL数据库,事件和路由中有两个表:

event:

id | date | destination | drivers | passengers | description | executed

id |日期|目的地|司机|乘客|描述|执行

route:

name | distance

名字|距离

drivers contains a string with the usernames of the registered drivers in an event on the form "jack:jill:john".

drivers包含一个字符串,其中包含已注册驱动程序的用户名,其形式为“jack:jill:john”。

destination contains the event destination (oh, really?) and its value is always the same as one of the values in the field name in the table route (i.e. the destination must already exist in route).

destination包含事件目标(哦,真的吗?),它的值始终与表路径中字段名称中的一个值相同(即目标必须已经存在于路由中)。

executed tells if the event is upcoming (0) or already executed (1).

执行告知事件是即将发生(0)还是已执行(1)。

distance is the distance to the destination in km from the home location.

距离是距离本地位置的距离目的地的距离。

What I want is to get the total distance covered for one specific user, only counting already executed events.

我想要的是获得一个特定用户的总距离,只计算已执行的事件。

E.g., if Jill has been registered as a driver in two executed events where the distances to the destinations are 50km and 100km respectively, I would like the query to return the value 150.

例如,如果Jill已经在两个已执行事件中注册为驱动程序,其中到达目的地的距离分别为50km和100km,我希望查询返回值150。

I know I can use something like ...WHERE drivers LIKE '%jill%' AND executed = 1 to get the executed events where Jill was driving, and SUM() to get the total distance, but how do I combine the two tables and get it all to work?

我知道我可以使用类似的东西... WHERE驱动程序LIKE'%jill%'并执行= 1来获取Jill正在驱动的执行事件,并使用SUM()来获取总距离,但是如何组合这两个表让它全部运作?

Your help is very much appreciated!

非常感激您的帮忙!

/Linus

2 个解决方案

#1

I haven't use MySQL for years, so sorry if I've got the syntax wrong, but something like this should do it:

我已经多年没有使用MySQL了,很抱歉如果我的语法错误,但是这样的事情应该这样做:

In generic SQL:

在通用SQL中:

select sum(distance) from route
 join event on route.name = event.destination 
 where drivers like '%jill%' AND executed = 1

Or not using JOIN:

或者不使用JOIN:

select sum(distance) from route, event
 where drivers like '%jill%' AND executed = 1
 and route.name = event.destination

#2

Stuart's answer shows you how to get the sum of the column, but I just want to note that:

Stuart的回答向您展示了如何获得列的总和,但我只想注意:

...WHERE drivers LIKE '%jill%'...

will return any event with a driver whose name contains the letters 'jill'.

将使用名称中包含字母'jill'的驱动程序返回任何事件。

Secondly, this database design doesn't seem to be normalized. You have driver names and route names repeated. If you normalize the database and have something like:

其次,这个数据库设计似乎没有规范化。您重复了驱动程序名称和路由名称。如果您规范化数据库并具有以下内容:

participant

id | name | role

event

id | date | route_id | description | executed

route

id | name | distance

participant_event

id | participant_id | event_id

then it would be a lot easier to work with the data.

那么处理数据会容易得多。

Then if you wanted to implement a user search, you could make the query:

然后,如果要实现用户搜索,可以进行查询:

SELECT id FROM participant WHERE
    name LIKE '%jill%' AND
    role='driver';

Then if the query returns more than one result, let the user/application choose the correct driver and then run a SELECT SUM like Stuart's query:

然后,如果查询返回多个结果,请让用户/应用程序选择正确的驱动程序,然后像Stuart的查询一样运行SELECT SUM:

SELECT SUM(r.distance) FROM route r
    JOIN event e ON e.route_id=r.id
    JOIN participant_event pe ON e.id=pe.event_id
    JOIN participant p ON pe.participant_id=p.id
    WHERE p.id=?;

Otherwise, the only way to ensure that you're only getting the total distance driven by one driver is to do something like this (assuming drivers is comma-delimited):

否则,确保您只获得一个驱动程序驱动的总距离的唯一方法是执行以下操作(假设驱动程序以逗号分隔):

...WHERE LCASE(drivers)='jill' OR
    drivers LIKE 'jill, %' OR
    drivers LIKE '%, jill' OR
    drivers LIKE '%, jill,%';

#1