英文:
Get a record for each parameter without repeating them
问题
可以创建一个查询，最好不要使用大量的联合操作，以便为我的每个远程条件获取一条记录吗？
例如，假设我有以下表格：
| id | value |
|---|---|
| 1 | 10 |
| 2 | 9 |
| 3 | 11 |
| 4 | 15 |
| 5 | 14 |
| 6 | 20 |
| 7 | 21 |
我想要获取一个随机记录，其值在9和10之间，在同一个查询中，我还想要一个随机值，其值在9和11之间，然后再一个值，其值在10和15之间。
在这种情况下，仅仅执行以下查询是不够的：
SELECT ... WHERE (value >= 9 AND value <= 10) OR (value >= 9 AND value <= 11) OR (value >= 10 AND value <= 15)
ORDER BY RAND()
LIMIT 3
因为收到的值将来自于id 1, 2, 3，但不满足(value >= 10 AND value <= 15)的条件。
可以使用联合操作来实现，但如何确保在每个子查询内不重复出现已经来自其他子查询的值呢？
例如：
SELECT * FROM (
SELECT ... WHERE value >= 9 AND value <= 10 ORDER BY RAND() LIMIT 1
UNION ALL
SELECT ... WHERE value >= 9 AND value <= 11 ORDER BY RAND() LIMIT 1
UNION ALL
SELECT ... WHERE value >= 10 AND value <= 15 ORDER BY RAND() LIMIT 1
) tb
在这种情况下，它会重复id 1两次，并且可能带来任何一个id (1, 3, 5 和 4)。
但我真正想要的是满足每个条件的3条记录，这些记录不能重复，并且在样本之间是随机的。
英文:
Is it possible to create a query, preferably without using hundreds of unions, that brings me a record for each item in my where remotely?
For example, assuming I have the following table
| id | value |
|---|---|
| 1 | 10 |
| 2 | 9 |
| 3 | 11 |
| 4 | 15 |
| 5 | 14 |
| 6 | 20 |
| 7 | 21 |
And I wanted to get a random record where its value is between 9 and 10, in the same query I also want a random value where its value is between 9 and 11 and then one more where its value is between 10 and 15
In this case it is not enough just to launch:
SELECT ... WHERE (value >= 9 AND value <= 10) OR (value >= 9 AND value <= 11) OR (value >= 10 AND value <= 15)
ORDER BY RAND()
LIMIT 3
Because the received values would be from id 1, 2 3, but without satisfying the condition of (value >= 10 AND value <= 15).
It would be possible to do it with a union, but how to make sure that within each subquery it does not repeat the values already coming from the other subqueries?
Ex:
SELECT * FROM (
SELECT ... WHERE value >= 9 AND value <= 10 ORDER BY RAND() LIMIT 1
UNION ALL
SELECT ... WHERE value >= 9 AND value <= 11 ORDER BY RAND() LIMIT 1
UNION ALL
SELECT ... WHERE value value >= 10 AND value <= 15 ORDER BY RAND() LIMIT 1
) tb
In this case it would repeat the id 1 twice and could bring any one of the ids (1, 3, 5 and 4)
But what I really wanted would be 3 records that satisfy each condition but that are not repeatable and that are random between samples
答案1
得分: 1
可以使用通用表达式（CTE）来实现如下：
with cte1 as (
select *
from mytable where value between 9 and 10
order by RAND() LIMIT 1
),
cte2 as (
select t.*
from mytable t
left join cte1 c1 on c1.id = t.id
where c1.id is null and t.value between 9 and 11
order by RAND() LIMIT 1
),
cte3 as (
select t.*
from mytable t
left join cte1 c1 on c1.id = t.id
left join cte2 c2 on c2.id = t.id
where c1.id is null and c2.id is null and t.value between 10 and 15
order by RAND() LIMIT 1
)
select * from cte1 union all
select * from cte2 union all
select * from cte3
第一个 cte1 用于获取第一个随机行。
cte2 用于获取第二个随机行，条件是它未被 cte1 选择，使用 left join 和条件 cte1.id is null。
cte3 用于获取第三个随机行，条件是它未被 cte1 和 cte2 选择。
然后我们使用 union all。
英文:
This can be done using CTEs as follows :
with cte1 as (
select *
from mytable where value between 9 and 10
order by RAND() LIMIT 1
),
cte2 as (
select t.*
from mytable t
left join cte1 c1 on c1.id = t.id
where c1.id is null and t.value between 9 and 11
order by RAND() LIMIT 1
),
cte3 as (
select t.*
from mytable t
left join cte1 c1 on c1.id = t.id
left join cte2 c2 on c2.id = t.id
where c1.id is null and c2.id is null and t.value between 10 and 15
order by RAND() LIMIT 1
)
select * from cte1 union all
select * from cte2 union all
select * from cte3
The first cte1 was used to obtain the first random row.
cte2 was used to get the second random row with the condition that it had not already been picked by cte1 using left join with the condition cte1.id is null.
cte3 is used to get the third random row that has not already been chosen by cte1 and cte2.
Then we use union all.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论