我在编写SQL查询方面相当不错,但我的大脑一直在旋转,试图找出编写查询的最佳方法。设置如下:
表动物有列:animal_id、farmer_id类型和born_on
这张表上有很多行,记录着每个农民的所有动物出生情况。
我需要做的是获取农民的所有农场ID,在他们最近出生的10只动物中,至少有3只是"绵羊"类型。
感谢您的帮助!
这是我的尝试:
SELECT a.farmer_id
FROM Animals a
WHERE
(select count(game_id)
from Animals b
where b.farmer_id = a.farmer_id
ORDER BY born_on DESC LIMIT 10) >= 3
这有点困难,因为MySQL没有Row_Number(),所以你需要模拟它。一种方法是使用自联接和计数。你也可以使用这里描述的@rownumber
技术
在我的示例中,我使用animal_id查找最新的,但您可能希望将JOIN条件更改为AND t1.born_on < t2.born_on
SELECT farmer_id
FROM
(
SELECT t1.farmer_id,
t1.animal_id,
Count(t2.animal_id) rn,
t1.type
FROM table1 t1
LEFT JOIN table1 t2
ON t1.farmer_id = t2.farmer_id
AND t1.animal_id < t2.animal_id
GROUP BY t1.farmer_id,
t1.animal_id,
t1.type
) as t
WHERE RN < 10
and type = 'Sheep'
HAVING COUNT(animal_id) >= 3
演示
使用的SAMPLE数据
| ANIMAL_ID | FARMER_ID | TYPE |
---------------------------------
| 1 | 1 | dog |
| 2 | 1 | dog |
| 3 | 1 | dog |
| 4 | 1 | dog |
| 5 | 1 | dog |
| 6 | 1 | dog |
| 7 | 1 | dog |
| 8 | 1 | dog |
| 9 | 1 | dog |
| 10 | 1 | dog |
| 11 | 2 | dog |
| 12 | 2 | dog |
| 13 | 2 | dog |
| 14 | 2 | dog |
| 15 | 2 | dog |
| 16 | 2 | dog |
| 17 | 2 | sheep |
| 18 | 2 | sheep |
| 19 | 2 | sheep |
| 20 | 3 | sheep |
| 21 | 3 | sheep |
| 22 | 3 | sheep |
| 23 | 3 | cat |
| 24 | 3 | cat |
| 25 | 3 | cat |
| 26 | 3 | cat |
| 27 | 3 | cat |
| 28 | 3 | cat |
| 29 | 3 | cat |
| 30 | 3 | cat |
结果只有2只,因为即使3只羊有三只,它们也不在最后10只中
假设您使用的是PHP(此处标记),而不是进行一个查询,进行多个查询并进行选择(通用或绵羊)会简单得多
或者,选择三只羊,得到指数,如果指数不是前三只羊的指数,再选择七只。