时间戳

首页 > 解决方案 > 来自具有不同关系的两个连接

mysql - 来自具有不同关系的两个连接

问题描述

在我的 php 页面上，我想显示一个表格：航次名称、开始日期、总行驶距离、船名、船长、船员

+----------------------+
| Tables_in_boatinglog |
+----------------------+
| boat                 |
| crew                 |
| vday                 |
| voyage               |
| voyage_crew          |
+----------------------+

我已经做到了这一点：

SELECT
    v.*,
    MIN(DATE_FORMAT(d.vdaydate, '%M %e, %Y')) AS vdaydate,
    SUM(d.vdaydistance) AS vdaydistance,
    boat.boatname
FROM
    voyage v
        JOIN
    vday d ON d.idvoyage = v.idvoyage
    INNER JOIN boat ON v.idboat = boat.idboat
GROUP BY v.idvoyage , v.voyagename

...会给我一个航程列表，其中包含 vday 表中的开始日期

SELECT v.*, c.crewname, vc.captain
FROM voyage v
     INNER JOIN
    voyage_crew vc ON vc.idvoyage = v.idvoyage
        INNER JOIN
    crew c ON vc.idcrew = c.idcrew

...会给我一份船员名单，船长用“y”表示。

但我不知道如何将这两者结合起来。我应该尝试在 MySQL 语句中执行此操作吗？还是 PHP 中的某种循环？

VOYAGE
idvoyage | PRI 
voyagename
voyagearea
idboat 

VDAY
idvday | PRI
vdaydate
vdaynotes
vdaydistance
vdaystartlocation
vdayfinishlocation
idvoyage

BOAT
idboat | PRI
boatname
boattype
boatlength
boatmodel
boatyear
boatcharterco

CREW
idcrew | PRI
crewname
crewcert

VOYAGE_CREW
idvoyage | PRI 
idcrew | PRI
captain | varchar(2)

标签： mysql

解决方案

您的 SQL 有点令人困惑，但这可能有效，或者类似的东西应该有效。基本上，您需要将两个查询与idvoyage

SELECT * FROM (
    SELECT
        v.*,
        MIN(DATE_FORMAT(d.vdaydate, '%M %e, %Y')) AS vdaydate,
        SUM(d.vdaydistance) AS vdaydistance,
        boat.boatname
    FROM
        voyage v
            JOIN
        vday d ON d.idvoyage = v.idvoyage
        INNER JOIN boat ON v.idboat = boat.idboat
    GROUP BY v.idvoyage , v.voyagename
)
AS a # this table name can be anything
LEFT JOIN (
    SELECT v.*, c.crewname, vc.captain
    FROM voyage v
         INNER JOIN
        voyage_crew vc ON vc.idvoyage = v.idvoyage
            INNER JOIN
        crew c ON vc.idcrew = c.idcrew
)
AS b # this table name can be anything
USING (idvoyage)
# alternately, instead of the above line with "USING", you could try:
# ON (a.idvoyage = b.idvoyage)

这里的主要思想是，您将两个查询保持原样，并在每个查询周围添加一个大的 JOIN。

推荐阅读