MariaDB多表关联查询：为主表匹配单条随机照片记录

你需要实现的是让tbl_vluchtgegevens的每条记录对应tbl_photos里的随机一张匹配照片，关联逻辑是通过tbl_luchtvaartmaatschappij衔接航空公司ID与IATA代码，同时匹配航班注册号。下面给你两种可行的解决方案，分别适配不同版本的MariaDB：

方法一：使用窗口函数（推荐，效率更优） #

MariaDB 10.2及以上版本支持窗口函数，用ROW_NUMBER()可以很方便地给每个匹配组的照片随机排序，只取第一条：

SELECT 
    vg.gegevenID,
    vg.vertrekdatum2,
    vg.luchtvaartmaatschappij,
    vg.inschrijvingnmr,
    p.file
FROM tbl_vluchtgegevens vg
LEFT JOIN tbl_luchtvaartmaatschappij lvm 
    ON vg.luchtvaartmaatschappij = lvm.luchtvaartmaatschappijID
LEFT JOIN (
    SELECT 
        img_lvm,
        img_nmr,
        file,
        -- 按照片的航空公司IATA和航班号分组，每组内随机排序并分配行号
        ROW_NUMBER() OVER (PARTITION BY img_lvm, img_nmr ORDER BY RAND()) AS rn
    FROM tbl_photos
) p 
    ON lvm.IATACode = p.img_lvm 
    AND vg.inschrijvingnmr = p.img_nmr
    AND p.rn = 1  -- 仅保留每组的第一条随机记录
WHERE vg.vertrekdatum2 <= NOW()
ORDER BY vg.vertrekdatum2 DESC;

方法二：关联子查询（适配旧版本MariaDB） #

如果你的MariaDB版本较低不支持窗口函数，可以用关联子查询随机获取每个匹配组的照片ID，再关联到照片表：

SELECT 
    vg.gegevenID,
    vg.vertrekdatum2,
    vg.luchtvaartmaatschappij,
    vg.inschrijvingnmr,
    p.file
FROM tbl_vluchtgegevens vg
LEFT JOIN tbl_luchtvaartmaatschappij lvm 
    ON vg.luchtvaartmaatschappij = lvm.luchtvaartmaatschappijID
LEFT JOIN tbl_photos p 
    ON lvm.IATACode = p.img_lvm 
    AND vg.inschrijvingnmr = p.img_nmr
    AND p.photoID = (
        -- 针对当前主表记录的匹配条件，随机返回一个照片ID
        SELECT photoID 
        FROM tbl_photos
        WHERE img_lvm = lvm.IATACode 
          AND img_nmr = vg.inschrijvingnmr
        ORDER BY RAND()
        LIMIT 1
    )
WHERE vg.vertrekdatum2 <= NOW()
ORDER BY vg.vertrekdatum2 DESC;

分析你之前尝试的问题 #

• 第一个SQL直接关联所有匹配照片，所以每条主表记录会对应所有符合条件的照片，导致结果重复；
• 第二个SQL存在语法错误（子查询的关联写法不正确），还有拼写错误（luchtvaartnamatschappij应为luchtvaartmaatschappijID），所以无法正确执行。

内容的提问来源于stack exchange，提问作者Daniël Cronk