带有 RAND() 子查询条件的 MySQL 查询 [英] MySQL query with RAND() subquery condition

问题描述

我有一个嵌套的 子查询，它选择一个随机 AlbumID 所选视频所在的(视频可以在多个专辑中)，然后外部查询根据该AlbumID返回视频和专辑信息.

I have a nested subquery that selects a random AlbumID that the selected video is in (videos can be in multiple albums), and the outer query then returns the videos and album information based on that AlbumID.

问题是查询返回混合结果；有时它会给我一个专辑中的一些视频，有时它会给出多个专辑中的视频，有时它什么都不返回.

The problem is that the query is returning mixed results; sometimes it gives me some of the videos from one album, sometimes it gives videos from multiple albums, sometimes it returns nothing.

如果我指定特定的 AlbumID 而不是子查询，则外部查询有效，并且子查询本身正确返回 1 个随机 AlbumID.但放在一起，它给了我不同的结果.我错过了什么?为什么它返回不同数量的行和多个相册?

The outer query works if I specify a specific AlbumID instead of the subquery, and the subquery by itself correctly returns 1 random AlbumID. But put together, it's giving me mixed results. What am I missing? Why is it returning varying amounts of rows, and multiple albums?

我已经用测试数据复制了这个问题，你可以在这里找到 CREATE 查询:http://pastebin.com/raw.php?i=e6HaaSGK

I've replicated the issue with test data, you can find the CREATE queries here: http://pastebin.com/raw.php?i=e6HaaSGK

SELECT SQL:

The SELECT SQL:

SELECT  
    Videos_Demo.VideoID,
    VideosInAlbums_Demo.AlbumID
FROM 
    VideosInAlbums_Demo
    LEFT JOIN
        Videos_Demo
        ON Videos_Demo.VideoID = VideosInAlbums_Demo.VideoID
WHERE
    VideosInAlbums_Demo.AlbumID = (
                                    SELECT
                                        AlbumID
                                    FROM
                                        VideosInAlbums_Demo
                                    WHERE
                                        VideoID = '1'
                                    ORDER BY
                                        RAND()
                                    LIMIT 1
                                    )

推荐答案

试试这个.将子查询移动到 JOIN 似乎解决了这个问题.我认为问题与在 WHERE 子句中包含子查询有关.我认为在 WHERE 子句中，正在为每条记录执行子查询和 RAND 函数.这可能就是结果不同的原因.

Try this. Moving the subquery to the JOIN seems to fix the problem. I think the problem has to do with having the subquery in the WHERE clause. I think that in the WHERE clause, the subquery and RAND function is being getting executed for each record. This is probably why the results are varying.

SELECT  a.AlbumID,
        Videos_Demo.VideoID,
        VideosInAlbums_Demo.AlbumID

FROM    VideosInAlbums_Demo

        LEFT JOIN Videos_Demo
        ON Videos_Demo.VideoID = VideosInAlbums_Demo.VideoID

        JOIN 
        (
            SELECT  AlbumID
            FROM    VideosInAlbums_Demo
            WHERE   VideoID = '1'
            ORDER BY RAND()
            LIMIT 1
        ) AS a ON VideosInAlbums_Demo.AlbumID = a.AlbumID

这篇关于带有 RAND() 子查询条件的 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！