使用 GroupBy 时在 MySQL 中随机选择子查询 [英] Randomizing Select subquery in MySQL when using GroupBy

问题描述

我有一个生成 100 个随机平面行的查询.结果必须是不同的，这意味着您不能拥有完全相同的国家、城市和街道 - 这就是 Group By 方便的地方.

I have the query that generates 100 random flat rows. Result needs to be distinct, which means, you cannot have the exact same country, city and street - that is where the Group By is handy.

它有效，除了以下几点:

it works, besides the following:

它连接(连接)国家、城市和街道，并连接子查询成员(这是问题所在).

It connects (joins), country, cities and streets and joins subquery members (this is the issue).

加入是 1 对 1 对 1，但是，成员可以在每个国家/地区拥有多个成员.

Join is 1 to 1 to 1, however, members can have a multiple members per country.

问题是当前查询结果总是那个国家的第一个成员.

请注意，重复的成员在不同的行中是可以的，但至少它们会被随机化并且并不总是返回第一个成员.

Note that duplicate members is fine in different rows, but at least they will be randomized and not always return first member.

如果出现重复成员，我不想添加空值，如果可能的话会很好，但不是必须的.

I do not want to get into adding nulls if a duplicate member appears, if possible it would be good, but not must.

SELECT 
    country.guid,
    country.Name AS 'country name',
    country.Area_id,
    country_cities.guid,
    country_cities.name AS 'city name',
    country_streets.guid,
    country_streets.name AS 'country streets',
    memebers.name.guid,
    memebers.name AS 'street members'
FROM
    country
        LEFT JOIN
    (SELECT 
        street_members.country_id, guid, name
    FROM
        street_members) memebers ON memebers.country_id = country.id
        JOIN
    country_cities ON country_cities.country_id = country.id
        JOIN
    country_streets ON country_streets.city_id = country_cities.id

GROUP BY country.guid , country_cities.guid , country_streets.guid
ORDER BY RAND()
LIMIT 0 , 100

推荐答案

有趣的一个，我终于通过在 group by 的子查询中添加我们想要的列来使它工作，如下所示:

Funny one, I finally made it work by adding the columns we wanted in the subquery in a group by, like this :

SELECT 
    country.guid,
    country.Name AS 'country name',
    country.Area_id,
    country_cities.guid,
    country_cities.name AS 'city name',
    country_streets.guid,
    country_streets.name AS 'country streets',
    memebers.name.guid,
    memebers.name AS 'street members'
FROM
    country
        JOIN
    (SELECT 
        RAND() as seed, country_id, guid, name
    FROM
        street_members GROUP BY seed, name, guid,country_id ORDER BY seed) memebers ON memebers.country_id = country.id
        JOIN
    country_cities ON country_cities.country_id = country.id
        JOIN
    country_streets ON country_streets.city_id = country_cities.id
GROUP BY country.guid , country_cities.guid , country_streets.guid
ORDER BY RAND()
LIMIT 0 , 100

没有测试整个请求，但是在我的一个数据库上测试了这个技巧并且它有效.

Did not test the whole request, but tested the trick on one of my DB and it work.

这篇关于使用 GroupBy 时在 MySQL 中随机选择子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！