MYSQL + Select 2 columns - 1 being Unique

I have a table like the following:

我有一个如下表：

 domainname    english
 www.a.com     apples
 www.a.com     peaches
 www.b.com     oranges
 www.b.com     banana
 www.c.com     watermelon

I want to select a random list of domainname and english but where the domainname is unique. for example:

我想选择一个域名和英语的随机列表，但域名是唯一的。例如：

 www.a.com     apples
 www.b.com     banana
 www.c.com     watermelon

The results need to be random each time.

结果每次都需要随机。

I've read other examples on this site and tried the following but it doesn't give a unique list of domainnames - eg: they show up twice or more:

我已经阅读了本网站上的其他示例并尝试了以下内容，但它没有给出一个唯一的域名列表 - 例如：它们出现两次或更多次：

 SELECT DISTINCT(domainname),english FROM table WHERE domainname ORDER BY RAND();

Hopeing someone can give some advice.

希望有人能给出一些建议。

cheers

干杯

3 个解决方案

#1

Try this query:

试试这个查询：

SELECT * FROM (
  SELECT * FROM table ORDER BY RAND()) t
GROUP BY
  domainname

#2

Try this query distinct is not working with multiple column name

尝试此查询distinct不使用多列名称

SELECT DISTINCT(domainname) FROM table WHERE domainname ORDER BY RAND();

#3

If the english column is the PRIMARY KEY of the table (or has a UNIQUE constraint), you could also use this:

如果英文列是表的PRIMARY KEY（或具有UNIQUE约束），您还可以使用：

SELECT t.*
FROM 
      ( SELECT DISTINCT domainname
        FROM tableX
      ) AS dd
  JOIN
      tableX AS t
    ON t.english =
       ( SELECT tt.english  
         FROM tableX AS tt
         WHERE tt.domainname = dd.domainname
         ORDER BY RAND()
         LIMIT 1
       )

#1