如何实现SQL语句来创建/或功能

SQL statement question. Let's say I have the following in my mini-table. Let's say the table is "images."

SQL语句问题。假设我的迷你表中有以下内容。假设该表是“图像”。

ID  |  file_name   |fruit_type | 
================================
1   |     ex.jpg   |  apple    | 
2   |     am.png   |  apple    | 
3   |     pl.jpeg  |  orange   |  
4   |      e.png   |  orange   | 
5   |     yep.png  |  apple    |

OK. So when I call this, I want it to pull two pictures, and each should be a random selection from within a fruit_type. So apples should only be picked with apples. Oranges should only be picked with oranges. There should be no combination of both types.

好。因此，当我调用它时，我希望它能够拉出两张图片，每张图片都应该是一个fruit_type中的随机选择。所以苹果应该只用苹果挑选。橘子应该只用橙子挑选。两种类型都不应该有任何组合。

Here's where it breaks down. Let's say the code I run is this:

这是它崩溃的地方。假设我运行的代码是这样的：

$query="SELECT * FROM images WHERE fruit_type='apple' 
OR fruit_type='orange' ORDER BY RAND() LIMIT 0,2";

This would return two random selections, like I want. However, this captures BOTH apple and orange types from that column.

这将返回两个随机选择，就像我想要的那样。但是，这会从该列中捕获苹果和橙色两种类型。

I have been trying to identify an SQL statement that would allow it to either choose apples or oranges, but never both. I've come up short, so I'm turning to you all.

我一直在尝试识别一个允许它选择苹果或橙子的SQL语句，但从不同时选择苹果或橙子。我说得很短，所以我转向你们。

3 个解决方案

#1

You can create two queries, first for apples, second for oranges and select random apple and random orange, then join them using UNION ALL clause.

您可以创建两个查询，首先是苹果，第二个是橙子，然后选择随机苹果和随机橙色，然后使用UNION ALL子句加入它们。

#2

The following will work, but it won't be a great option from a performance point of view if your table gets huge (millions of records). The below query takes all combinations of images that have the same fruit_type excluding matching images (you don't want the same image twice, do you?).

以下内容可行，但从性能的角度来看，如果您的表变得庞大（数百万条记录），它将不是一个很好的选择。以下查询获取具有相同fruit_type但不包括匹配图像的所有图像组合（您不希望同一图像两次，是吗？）。

SELECT images1.*, images2.*
FROM images images1, images images2
WHERE images1.id != images2.id
AND images1.fruit_type = images2.fruit_type
ORDER BY RAND()
LIMIT 0,2

#3

Since you don't have windowing functions in MySQL, you have to improvise. This post is adapted from this blog post by Quassnoi:

由于您在MySQL中没有窗口函数，因此您必须即兴发挥。这篇文章改编自Quassnoi的博客文章：

  SELECT  
    distinct
    @fi AS file_name,
    fruit_type

    FROM    (
    SELECT  m.*
    FROM    (

    SELECT  @_f = NULL

    ) vars,

    images m
    ORDER BY
    fruit_type, rand()
    ) mo

    WHERE   (CASE WHEN @_f IS NULL OR @_f <> fruit_type THEN @fi := file_name ELSE file_name END IS NOT NULL)

    AND (@_f := fruit_type) IS NOT NULL

#1