连接具有多行的表中的一行

发布于 2024-12-21 03:40:49 字数 836 浏览 1 评论 0原文

这是一个关于mysql查询的问题。作为示例,我们使用一个跟踪汽车销售的系统。有一个包含 salespersons 的表、一个包含 sales 的表以及一个链接这两个表的表。大多数时候,每次销售只有一个人与之相关。但有时一次销售涉及多个人。

Table sales
id | carId | date
---+-------+-----------
1  | 2     | 11-01-2011
2  | 8     | 11-02-2011
3  | 5     | 11-05-2011

Table link
personId | saleId
---------+-------
2        | 1 
2        | 2 
1        | 3
2        | 3

Table salesperson
id | name  | age
---+-------+----
1  | Barry | 25
2  | Sara  | 32

为了检索数据,我想在网页中显示每笔销售的信息。但当多人参与销售时,就会出现问题。我尝试在代码中应用下面的查询,之后 PHP 会处理多人与一笔销售关联时的异常,但由于销售表的长度,下面的查询需要花费太多时间来运行

SELECT count(personId) as amount, carId, date
FROM link 
JOIN salesperson ON link.personId = salesperson.id  
JOIN sales ON sales.id = link.saleId
GROUP BY link.saleId

:可以使用更高效的查询吗?顺便说一下,表的索引已经完成。

This is a question regarding queries in mysql. As an example let’s use a system that tracks the selling of cars. There is table with salespersons, a table of sales and a table that links the two. Most of the time each sale has only one person associated with it. But sometimes multiple persons are associated with a sale.

Table sales
id | carId | date
---+-------+-----------
1  | 2     | 11-01-2011
2  | 8     | 11-02-2011
3  | 5     | 11-05-2011

Table link
personId | saleId
---------+-------
2        | 1 
2        | 2 
1        | 3
2        | 3

Table salesperson
id | name  | age
---+-------+----
1  | Barry | 25
2  | Sara  | 32

To retrieve the data I want to show the information of each sale in a webpage. But when multiple persons are associated with a sale it becomes problematic. I have tried applying the query below in my code, after which the PHP handles the exception of when multiple persons are associated with one sale, but due to the length of the sales table the query below takes too much time to run:

SELECT count(personId) as amount, carId, date
FROM link 
JOIN salesperson ON link.personId = salesperson.id  
JOIN sales ON sales.id = link.saleId
GROUP BY link.saleId

Is there a more efficient query that can be used? By the way indexing of the table is already done.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

爱*していゐ 2024-12-28 03:40:49

那么就只是:

SELECT STRAIGHT_JOIN
    amount, carId, date
FROM (
    SELECT saleId, count(*) as amount
    FROM link
    GROUP BY saleId
    ORDER BY NULL) as amounts
JOIN sales ON sales.id = amounts.saleId;

Then it is just:

SELECT STRAIGHT_JOIN
    amount, carId, date
FROM (
    SELECT saleId, count(*) as amount
    FROM link
    GROUP BY saleId
    ORDER BY NULL) as amounts
JOIN sales ON sales.id = amounts.saleId;
烟雨扶苏 2024-12-28 03:40:49

您是否为表设置了正确的索引?我不知道你的桌子有多大,但我不认为这会是一个大问题。

另外,您想从查询中得到什么结果?您似乎正在尝试获取所有销售情况,然后计算涉及多少销售人员?

Have you set the right indices to the tables? I don't know how big your tables are, but I can't imagine it should be much of a problem.

Also, what result are you trying to get from your query? It seems like you are trying to get all sales and then count how many sales people were involved?

笨死的猪 2024-12-28 03:40:49

您的查询看起来不错,但您的分组依据有点奇怪......
尝试按所有非聚合列进行分组的标准方法,如下所示:

SELECT carId, date, name, age, count(personId) as amount
FROM link 
JOIN salesperson ON salesperson.id = link.personId
JOIN sales ON sales.id = link.saleId
GROUP BY 1,2,3,4;

You query looks OK, but your group by is a little odd...
Try the standard approach of grouping by all non-aggregate columns, like this:

SELECT carId, date, name, age, count(personId) as amount
FROM link 
JOIN salesperson ON salesperson.id = link.personId
JOIN sales ON sales.id = link.saleId
GROUP BY 1,2,3,4;
清风疏影 2024-12-28 03:40:49

我认为问题可能是您使用链接表作为基表并且查询的数据库优化不好。
尝试:

SELECT count(sp.personId) as amount, s.carId as carId, s.date as date, sp.name as name, sp.age as age  
FROM sales as s
JOIN link as l ON s.id = l.saleId
JOIN salesperson as sp ON l.personId = sp.id  
ORDER BY l.saleId

I think that the problem may be that you are using the link table as your base table and the database optimisation of the query isn't good.
Try:

SELECT count(sp.personId) as amount, s.carId as carId, s.date as date, sp.name as name, sp.age as age  
FROM sales as s
JOIN link as l ON s.id = l.saleId
JOIN salesperson as sp ON l.personId = sp.id  
ORDER BY l.saleId
殊姿 2024-12-28 03:40:49

如果您关心性能方面,请避免使用连接,尤其是在 mysql 中。
阅读这篇文章,展示高效的编码技术
http://phpadvent.org/2011/a -stitch-in-time-saves-nine-by-paul-jones

对于第一个查询中的问题,请选择销售 ID
在第二个查询中从链接表中选择 personID
在第三个查询中选择从人员表中选择所有人员

正如文章中提到的,在传递查询时使用 IN 子句。它的编码看起来有点冗长,但比在具有 15k+ 行的表上使用联接要高效得多

If you are concerned with the performance aspect avoid using Joins especially in mysql.
read this article demonstrating the efficient coding techniques
http://phpadvent.org/2011/a-stitch-in-time-saves-nine-by-paul-jones

For your problem in first query select sales ID
in second query select personID from links table
in third query select select all persons from person table

As mentioned in the article use IN clause while passing a query. it looks little lengthy in coding but is much efficient than using join on table with 15k+ rows

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文