使用具有不同随机子查询结果的 GroupBy 在 MySQL 中随机选择子查询 [英] Randomizing Select SubQuery in MySQL using GroupBy with different random SubQuery results
本文介绍了使用具有不同随机子查询结果的 GroupBy 在 MySQL 中随机选择子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何为同一国家/地区显示不同的members.name
,目前如果为同一国家/地区显示相同的members.name
,但在每次查询执行时不同.
How to show different members.name
for the same country, currently if shows the same members.name
for the same country, but different one at each query execution.
基于上一个问题:使用GroupBy时在MySQL中随机选择子查询
查询:
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
推荐答案
如上一个问题所示,country_streets
和 street_members
之间没有直接链接,所以我只看到以下解决方案:
As seen in a previous question, you don't have a direct link between country_streets
and street_members
, so I only see the following solution :
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
country_cities ON country_cities.country_id = country.id
JOIN
country_streets ON country_streets.city_id = country_cities.id
JOIN
(SELECT
RAND() as seed, country_id, city_id, cs.id as streets_id, guid, name
FROM
street_members sm
JOIN country_cities cc ON sm.country_id = cc.country_id
JOIN country_streets cs ON cs.city_id = cc.country_id
GROUP BY seed, name, guid,country_id,city_id,streets_id ORDER BY seed
) memebers ON memebers.streets_id=country_streets.id
GROUP BY country.guid , country_cities.guid , country_streets.guid
ORDER BY RAND()
LIMIT 0 , 100
同样,它没有在您的特定数据库上进行测试(当然可以在这里和那里改进),但您明白了.
Again it's not tested on your specific database (and could surely be improved here and there) but you get the idea.
这篇关于使用具有不同随机子查询结果的 GroupBy 在 MySQL 中随机选择子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文